Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using a SQL Server Explain Plan to View Cached Query Plan Parameters in Management Studio


By:   |   Read Comments   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


Problem

There is a stored procedure in my database that runs in milliseconds 99% of the time, but every once in a while we start to see calls to this stored procedure that take over 5 seconds. We suspect that this is a parameter sniffing issue because running DBCC FREEPROCCACHE always fixes the issue. Is there some way we can confirm that this in indeed the issue?

Solution

We will create an example to show how to troubleshoot this problem.

Setup

In order to demonstrate a method we can use to confirm this we'll create a simple stored procedure in the AdventureWorks database that performs a query of the SalesOrderDetail table based on a date range that is passed in as parameters. We will also create an index on the date column so the query can use this for table access if needed.

Here is the T-SQL code to create these two objects.

CREATE PROCEDURE [dbo].[uspGetSalesOrderDetailByModifiedDate]
   @StartModifiedDate [datetime] NULL, @EndModifiedDate [datetime] NULL
AS
BEGIN

   SET NOCOUNT ON;

   SELECT 
      sod.[SalesOrderID],
      sod.[SalesOrderDetailID],
      sod.[CarrierTrackingNumber],
      sod.[OrderQty],
      sod.[ProductID],
      sod.[SpecialOfferID],
      sod.[UnitPrice],
      sod.[UnitPriceDiscount],
      sod.[LineTotal],
      sod.[rowguid],
      sod.[ModifiedDate]
   FROM 
      Sales.SalesOrderDetail sod
   WHERE
      sod.ModifiedDate BETWEEN @StartModifiedDate and @EndModifiedDate
END;
GO

CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ModifiedDate] ON [Sales].[SalesOrderDetail]
( [ModifiedDate] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
      DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Next we'll need to create a couple of calls to the stored procedure, one with a large date range and one with a small date range, so we can demonstrate how the parameter sniffing is causing the performance issue.

Here is the T-SQL code for these two calls.

EXEC dbo.uspGetSalesOrderDetailByModifiedDate '2014-01-01','2014-02-02'
GO
EXEC dbo.uspGetSalesOrderDetailByModifiedDate '2014-02-01','2014-02-02'
GO

Checking the Cached Parameters

Once the above queries have all been run we can take a look at the plan that is being used. There are two different ways you can get the explain plan for this stored procedure. First you can turn on "Include Actual Explain Plan" in SSMS and then run your stored procedure again. You can also just pull the query plan for the stored procedure directly from the query cache using a query like the one shown below.

SELECT databases.name,
 dm_exec_sql_text.text AS TSQL_Text,
 dm_exec_query_stats.creation_time, 
 dm_exec_query_stats.execution_count,
 dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE '%spGetSalesOrderDetailByModifiedDate%'

Now that we have a way to get the plan, let's take a look at the plan used to execute our query.

Bad Query Plan in SQL Server Management Studio

Looking at this plan we can see that it's doing an index scan of the primary key. We'd expect this query to be using the index we created since it's querying such a small date range. Since this looks like a parameter sniffing issue let's look at what parameters we have cached for this explain plan. We can do this by right clicking on the SELECT object in the explain plan diagram and selecting properties. In the properties window we can scroll down and expand the "Parameter List" item. Here we can see what parameters are cached with our explain plan. Below is a screenshot of what is cached for our query.

Bad Query Plan With Parameters in SQL Server Management Studio

Looking at this screenshot we can see that the query was called with the small date range parameters, but it was executed using the large date range parameters because that is what was in the cache. Now we can confirm that this is indeed a parameter sniffing issue. Let's run "DBCC FREEPROCCACHE" and then take a look again at our query, explain plan and cached parameters.

Good Query Plan With Parameters in SQL Server Management Studio

After clearing the cache we can see that we are using the explain plan that we expected to see originally and the query stored in the cache now was generated using the small date range parameters. All that's left now is to decide how fix this parameter sniffing issue.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools