I have a SQL Server stored procedure that sometimes completes in under a second and other times takes over 15 seconds. I've looked at the EXPLAIN PLAN for the stored procedure and all the columns in the statements are indexed, so I suspect parameter sniffing might be the issue. How can I confirm that this is indeed the problem?
If you aren't familiar with parameter sniffing, below are a few tips that explain the problem and provide a few different ways to correct the issue. This tip is going use a simple example to illustrate how performance is impacted by this issue and how after correcting it the stored procedure performs as expected.
- Different Approaches to Correct SQL Server Parameter Sniffing
- Optimize Parameter Driver Queries with SQL Server OPTIMIZE FOR hint
In order to illustrate the performance impacts of parameter sniffing we will need to create a simple table with some skewed data in it. The T-SQL below creates this table as well as a stored procedure with a simple SELECT statement based on the parameter passed to it. Note that the first loop creates some random data and the second loop creates the skewed data. I understand in the real world the stored procedure would have multi-table joins which would end up generating a much more complicated EXPLAIN PLAN, but for our purposes this simple query will be enough to illustrate the point.
--Table Setup CREATE TABLE Main (col1 int primary key, col2 int, coldata varchar(20)); DECLARE @val INT SELECT @val=1 WHILE @val < 500000 BEGIN INSERT INTO Main VALUES (@val,round(rand()*100000,0),'test' + cast(@val as varchar)); SELECT @val=@val+1; END; WHILE @val < 600000 BEGIN INSERT INTO Main VALUES (@val,23425,'test' + cast(@val as varchar)); SELECT @val=@val+1; END; CREATE INDEX ix_Main on Main (col2); CREATE PROCEDURE sp_search_Main (@searchval int) AS BEGIN select col1,col2,coldata from Main where col2=@searchval END GO
You can see from the T-SQL code above in the second WHILE loop that one of the parameters we will pass to the stored procedure will be 23425. For the other more selective value you can run the following T-SQL to find a value for col2 that would return 5 records. I use the NEWID() function just so I get a random value in the somewhere in the middle of the dataset and not the first record. In my case the query returned 54322.
SELECT TOP 1 col2,count(1) FROM Main GROUP BY col2 HAVING count(1)=5 ORDER BY NEWID()
Now that we are ready to run our test let's call the stored procedure with each parameter twice. The first time through we will call it with 54322 first and the next time through we will call it with 23425 first. Between these calls we will flush the cache to ensure a new plan is generated. Below is the TSQL for this step.
dbcc freeproccache go sp_search_Main 54322 go sp_search_Main 23425 go dbcc freeproccache go sp_search_Main 23425 go sp_search_Main 54322 go
Next let's run the above T-SQL code and capture the statistics using SQL Profiler and also include the actual execution in SQL Server Management Studio to see what our performance looks like. We can see from the execution plans below that it's definitely a parameter sniffing issue as the plan changes after we flush the cache and run the stored procedures a second time with the other parameter first.
Looking at the SQL Profiler results we can see the impact this has had on performance even for this simple example. It both cases the second procedure performs much worse using the plan that was cached from the previous call, specifically with respect to the amount of CPU used and the number of reads required to execute the query.
|Parameter 54322 First||0||32||0||4|
|Parameter 23425 Second||390||300194||0||925|
|Parameter 23425 First||109||2304||0||844|
|Parameter 54322 Second||63||2298||0||51|
Now that we've demonstrated the issue let's use one of the solutions suggested in this tip and see how the queries perform. Below is the T-SQL you can use to update the stored procedure we created during our initial setup with the "WITH RECOMPILE" option.
ALTER PROCEDURE sp_search_Main (@searchval int) WITH RECOMPILE AS BEGIN select col1,col2,coldata from Main where col2=@searchval END GO
Next we can run the same T-SQL we did above during our initial test and take a look at the execution plans and SQL Profiler results below.
|Parameter 54322 First||0||32||0||6|
|Parameter 23425 Second||140||2304||0||1146|
|Parameter 23425 First||156||2304||0||1014|
|Parameter 54322 Second||0||32||0||6|
Looking at these results we can see that both stored procedures now perform as expected. We do use a small amount more CPU and the query takes a little bit longer to execute due to the extra overhead of having to compile with each execution, but all in all it's using much fewer resources than it was previously, even with this simple example.
- Test the performance of other alternatives for resolving a parameter sniffing issue.
- Check out the hints section and read more on other options for optimizing queries.
Last Update: 2016-02-24
About the author
View all my tips