Analyzing SQL Server Performance Impact of Parameter Sniffing

By: Ben Snaidero | Comments | Related: > Performance Tuning

Problem

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?

Solution

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.

Test Setup

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()

Performance Test

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	CPU	Reads	Duration
Parameter 54322 First	0	32	4
Parameter 23425 Second	390	300194	925
Parameter 23425 First	109	2304	844
Parameter 54322 Second	63	2298	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	CPU	Reads	Duration
Parameter 54322 First	0	32	6
Parameter 23425 Second	140	2304	1146
Parameter 23425 First	156	2304	1014
Parameter 54322 Second	0	32	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.

Next Steps

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.

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