By: Ben Snaidero | Updated: 2016-12-13 | Comments | Related: > Performance Tuning
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.
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.
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.
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
- Read more on Explain Plans
- Read more information on parameter sniffing
About the author
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-12-13
