SQL Server generates a query plan for a stored procedure using (sniffing) the parameters sent by the first time. By “first time” means that SQL Server is forced to compile a stored procedure because it is not 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).
The potential problem arises when the first time the stored procedure is executed, the set of parameters generate an acceptable plan for that set of parameters but very bad for other more common sets of parameters.
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 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 or clear 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 index (IX_Name and IX_Birthdate). The table has 1 million rows, that was filled with some random data using RedGate SQL Data Generator.
Now, we create a multi-purpose stored procedure that returns top 100 rows for 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,5 x more time to execute. I am suspecting that the second procedure call is suffering from 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 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 problem 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 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:
It eliminates our parameter sniffing problem because SQL Server will regenerate the query plan every single time, we execute the query.
The disadvantage here is that we lose all benefits from having SQL Server save CPU cycles by caching execution plans, which can be devastating for SQL Server resources on heavy transactional systems that execute the same procedure very often. If your parameter sniffed query is complex or is getting ran frequently, RECOMPILE is probably a bad idea.
If your parameter sniffed query doesn’t get ran 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.
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 be automatically seeking workarounds, hacks, or cheats to make the problem go away. 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.