Parameter sniffing and its impact on query performance
SQL Server generates a query plan for a stored procedure using (sniffing) the parameters sent by the first time.
By ‘first time,’ it is meant that SQL Server is forced to compile a stored procedure because it is not present in the procedure cache.
All subsequent executions of that same query will go to the cache to reuse that same initial query plan. This saves SQL Server time from having to regenerate a new query plan (unless there is a reason for recompilation).
A potential issue occurs when the initial execution of a stored procedure produces a plan that’s suitable for that specific set of parameters but inefficient for others more commonly used.
An optimal execution plan for one set of parameters might be an index scan operation, whereas another set of parameters might be better resolved using an index seek operation.
Parameter sniffing can lead to inefficient execution plans, especially when a stored procedure is called with parameter values that have different cardinality.
When is it bad? When is it good?
Most of the time it’s good. It means SQL Server is caching and reusing query plans to make your queries run faster, saving the CPU and memory that would be used to compile the execution plan again.
Parameter sniffing is only bad when your data values are unevenly distributed and cached query plans are not optimal for all values.
When a stored procedure executes efficiently sometimes, but inefficiently at other, and you swear nothing else in the environment is changing, that’s often a case of parameter sniffing.
How to deal with it when it is bad?
There are many ways you can deal with bad parameter sniffing.
Here are just a few of them:
- Dynamic SQL;
- Dynamic SQL with parameters;
- Dynamic SQL with OPTION (RECOMPILE)
- Hint OPTIMIZE FOR UNKNOWN
- Hint OPTIMIZE FOR VALUE
- Hint OPTION (RECOMPILE)
- Trace flag 4136
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF
There often is not an obvious fix, and as a result, we implement hacks or poor choices to resolve the latency and allow us to move on with life as quickly as possible.
An immense percentage of the content available online encourages the administrator to take shortcuts that do not truly fix a problem.
Bad parameter sniffing case
Let’s kick off with a short demonstration of how parameter sniffing can influence your performance.
Here, we are creating the table Customers_Red9 with two non-clustered indexes (IX_Name and IX_Birthdate). The table has 1 million rows, that were filled with some random data using Redgate SQL Data Generator.
Now, we create a multi-purpose stored procedure that returns the top 100 rows for the given @Name
and @Birthdate
. Also, it returns all rows if @Name
or @Birthdate
is not supplied (top 100).
I will call this stored procedure with @Name = ‘Bobbie Dalton‘
parameter supplied (there is nothing in the procedure cache at this point), so the execution plan will be created with @Name = ‘Bobbie Dalton‘
and @Birthdate = NULL
.
The execution time for the stored procedure call above is 121 ms.
The second procedure call will be supplying @Name = NULL
and @Birthdate = ‘20060107’
.
The execution time for this procedure call is 1757 ms.
This query spent 14,5x more time to execute. I suspect that the second procedure call is suffering from a bad parameter sniffing problem. Let’s try to prove that.
If we look at the execution plan of the second query, we will see that the index on the Name column is being used and not the index on Birthdate. The plan retrieved from the plan cache and that compiled value for @Birthdate
is NULL
and run-time value is ‘2006-01-07’.
We have confirmed that this is a bad parameter sniffing problem.
Now, how to deal with it?
I have already enumerated various methods of how to deal with bad parameter sniffing problems and now for this sample, I will use the hint OPTION (RECOMPILE).
The new stored procedure looks like this:
Running again the first query, using @pName
parameter:
The second query, using @pBirthdate
:
What the RECOMPILE hint does is force the SQL Server to generate a new execution plan every time these queries run.
This way for the two above procedure calls we will get two different execution plans as you can see below:
This approach eliminates our parameter sniffing issue by prompting the SQL Server to regenerate the query plan each time the query is executed.
The disadvantage here is that we lose all benefits from SQL Server caching execution plans to save CPU cycles.
This can be devastating for SQL Server resources in heavy transactional systems that execute the same procedure very often.
- If your parameter-sniffed query is complex or is getting run frequently, RECOMPILE is probably a bad idea.
- If your parameter-sniffed query doesn’t get run often, or if the query doesn’t run often enough to stay in the query plan cache anyway, then RECOMPILE sounds like a good solution.
Conclusion
It is important to be reminded that parameter sniffing is a feature of SQL Server, not a bug or something that is wrong.
Most of the time parameter sniffing is helping SQL Server to use less CPU and memory and when it goes bad on us you know how to deal with it.
We should not automatically seek workarounds, hacks, or cheats to make the problem disappear. Many quick fixes exist that will resolve a problem for now and allow us to move on to other priorities.
Before adding query hints, SQL Server trace flags, or otherwise hobbling the query optimizer, consider every alternate way to improve performance.
Local variables, dynamic SQL, RECOMPILE, and OPTIMIZE are too often cited as the best solutions, when in fact they are typically misused.
When a performance problem due to parameter sniffing occurs frequently, it is more likely the result of design and architecture decisions than a query optimizer quirk.
Instead of creating hacks to solve problems quickly, we can make code more durable at little additional cost to developers. The result will be saved time and resources in the long run and an application that performs more consistently.
Agree? Disagree? Let’s talk in the comments.