SQL Server Parameter Sniffing - Slow Running Queries

Parameter sniffing is a problem that can occasionally creep into code, just when everything seems fine. A stored procedure that yesterday ran nice and fast, today is taking much longer. The next day it is fast again. Nothing has changed on the server / db. It can be difficult to track down why.

The answer could be Parameter Sniffing.

When a stored procedure is compiled, it compiles using ( or "sniffing") the parameter values set ​​at the time of the invocation. It uses those parameter values ​​to determine the optimal execution plan for the proc. Simply put, if you later call the proc with a different parameter value, it will still use the execution plan determined by the first set of values. It will do this regardless of whether a better execution plan would have been more appropriate for the new value.

For example, if I have a table containing all the values ​​from 1 to 100, and then 100 rows with a value 999:

/ * Create table * /

CREATE TABLE DummyData ( col1 INT ​​)

DECLARE @Counter INT

/ * Insert data 1-100 * /

SET @Counter = 0

WHILE @Counter < 100

BEGIN

INSERT INTO DummyData ( col1 ) VALUES ( @Counter )

SET @Counter = @Counter + 1

END

/ * Insert 100 data rows with value 999 * /

SET @Counter = 0

WHILE @Counter < 100

BEGIN

INSERT INTO DummyData ( col1 ) VALUES ( 999 )

SET @Counter = @Counter + 1

END

Next create a procedure to select values from this table:

CREATE PROC SelectDummyData ( @DummyValue INT )

AS

BEGIN

SELECT * FROM DummyData WHERE col1 = @DummyValue

END

Now generate the Execution Plan for this procedure, passing a parameter of 999, and look at the "estimated number of rows":

EXEC SelectDummyData @DummyValue = 999

The row count is 100, which is what we would expect. But re-run the execute statement, this time with @DummyValue = 5, and the Estimated Number of Rows is still 100, when we would expect it to be 1.

The reason it is still expecting 100 rows is because the execution plan was determined when the proc was first compiled, when we used a value of 999.  It is still using the same execution plan, and therefore the same number of rows is expected, regardless of the input value supplied.

The impact of this oddity in this example is negligible, but for a complex query it can result in significantly slower performance.

If your query is complex it might be easier to generate the execution plan in XML ( SET SHOWPLAN_XML ON ) and then search the XML for something like "ParameterCompiledValue =".

To avoid parameter sniffing issues from occurring there are a number of options that can be considered:

1. Recompile the stored proc every time it is executed:

EXEC SelectDummyData @DummyValue =  1
WITH RECOMPILE 

This will ensure that the proc uses the supplied  parameters to build the best execution plan every time, but the overhead is the constant recompilation. This may be acceptable if there is sufficient gain in query performance.

Generating the execution plan on the above script, using various parameters, shows the correct number of rows being estimated each time.

If the parameter sniffing  relates to a set of queries in particular, then  recompilation can be specified individually for queries:

SELECT * FROM DummyData WHERE col1 = @DummyValue

OPTION ( RECOMPILE )

2. Optimize the query/ies for a specific parameter value:

ALTER PROC SelectDummyData ( @DummyValue INT )

AS

BEGIN

SELECT * FROM DummyData WHERE col1 = @DummyValue

OPTION ( optimize FOR ( @DummyValue = 1 ))

END

With this option we can force the query plan to be based on the same known value every time it is compiled (eg During maintenance tasks). This option may work if we know the optimal value for our system. The downside, however, is that it may still perform poorly for other values, but that may well be tolerable.

3. Avoid sniffing altogether, by using a local variable inside the proc:

ALTER PROC SelectDummyData ( @DummyValue INT )
AS BEGIN DECLARE @LocalDummyValue INT SET @LocalDummyValue = @DummyValue
SELECT * FROM DummyData WHERE col1 = @LocalDummyValue
END 

Here there is no possibility of parameter sniffing, and the execution plan will be based on the statistics. Note, however, that the plan will be built using statistic densities instead of statistic histograms, which are less accurate. Therefore it is not guaranteed to be the best plan for all possible values.

Conclusion
Parameter sniffing has the potential to cause queries to run with widely varying performance. In order to mitigate this problem consider modifying the code based on the options presented above. Hopefully this will help keep the queries and stored procedures performing well wherever they are used.

More useful info on this topic is available here:

Degremont Michel

http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx

Turgay Sahtiyan
http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx