sp_BlitzCache™ Result: Parameter Sniffing
You’re here because sp_BlitzCache detected queries that ran at least a few times, and they have a wide, wide variance in CPU time and/or returned number of rows. It isn’t a guarantee that the queries involved are experiencing parameter sniffing, but we’re just looking for symptoms. (We can’t even guarantee that the query has parameters – doing that kind of analysis would take longer than you’d prefer to wait for sp_BlitzCache to run.)
SQL Server uses a process called parameter sniffing when executing queries or stored procedures that use parameters. During compilation, the value passed into the parameter is evaluated and used to create an execution plan. That value is also stored with the execution plan in the plan cache. Future executions of the plan will re-use the plan that was compiled with that reference value.
This is how we want SQL Server to work – compiling execution plans is expensive and time consuming. Good execution plan re-use is key to SQL Server performance.
Problems arise when the values queried in a table are not evenly distributed. If one parameter value returns 10 rows and another parameter value returns 10,000,000 rows, it would be best if SQL Server used different execution plans for the different values of the parameter.
The first time the parameterized query is run, SQL server will compile the query with whichever value is passed in. SQL Server will keep using that value until the query is recompiled (or until SQL Server is restarted).
To help you figure out which parameters are in the cached version of a plan, we’ve added a new column to sp_BlitzCache and sp_BlitzQueryStore:
To Fix Parameter Sniffing
Option 1: Indexes (Good Idea)
Often, inadequate covering indexes can be the root cause of parameter sniffing. SQL Server may choose a Key Lookup plan for a small number of values, and a clustered index seek or scan for a large number of values. With a covering index, the optimizer won’t make those choices, and often you’ll end up with a more stable execution plan.
Option 2: Recompiling (Not Always Great Idea)
You can force SQL Server to recompile the offending statement for every execution by using a recompile hint. Recompile hints can be applied at the stored procedure or statement level.
-- stored procedure version
CREATE PROCEDURE dbo.sample @ProductID INT
/* do something */
-- single statement version
SELECT * FROM Sales.SalesOrderHeader
This solution is good when:
- Queries are run infrequently.
- The best query performance matters for every execution.
This solution is bad when:
- Queries run frequently.
- CPU resources are limited.
- Some variance in query performance is acceptable.
Option 3: Optimize For Value (Rarely A Good Idea)
Instead of forcing SQL Server to recompile for every potential value of data, developers can find a statistically average (or common) value and provide an
OPTIMIZE FOR hint.
In this example, a
ProductID of 945 is “good enough” for all executions of the query.
ALTER PROCEDURE Get_OrderID_OrderQty
SELECT SalesOrderDetailID, OrderQty
WHERE ProductID = @ProductID
OPTION (OPTIMIZE FOR (@ProductID=945));
This solution is good when:
- The distribution of data seldom changes.
- You have development resources dedicated to tuning queries.
- You have resources available to review data distribution.
This solution is not good when:
- Data distribution can change rapidly.
- Data distribution change is not predictable.
- There are limited or no resources for tuning.
Option 4: Optimize For Unknown (Basically Never A Good Idea)
Instead of optimizing for a specific value, it is possible to tell the SQL Server optimizer to optimize for an unknown value. That is – SQL Server ignores values passed in when optimizing the execution plan. Instead, SQL Server will produce a generic plan based on a statistical average distribution of data.
CREATE PROCEDURE dbo.GetCities
SELECT DISTINCT City
OPTION (OPTIMIZE FOR UNKNOWN)
The issue with
OPTIMIZE FOR UNKNOWN hints is that they can produce unpredictable performance, just like parameter sniffing.
Option 5: Exceptions (Second Best Idea)
Instead of attempting to get SQL Server to deliver a good enough execution plan every time, it’s possible to provide branching logic for queries where parameter sniffing can produce bad results. In these scenarios, you know about exceptional values ahead of time and can write code to locate the values that cause problems.
CREATE PROCEDURE dbo.GetStackOverflowPosts
IF @UserID IN (SELECT UserID FROM dbo.ProlificUsers)
EXEC dbo.GetProlificPosts @UserID
EXEC dbo.GetNormalPosts @UserID
This is a problem that the team at StackOverflow have encountered. The most prolific users answer more questions and receive more votes than other users. Queries compiled for regular users run slowly for the prolific users. StackOveflow have added checks for users with the highest profile scores – users with high scores will use one code path, regular users get another.