Parameter Sniffing

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.

There are other plan operators like Sorts and Hash Joins that may run into Memory Grant issues in parameter sniffing situations.

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
WITH RECOMPILE
AS
/* do something */

-- single statement version
SELECT * FROM Sales.SalesOrderHeader
OPTION (RECOMPILE);

-- stored procedure version

CREATE PROCEDURE dbo.sample @ProductID INT

WITH RECOMPILE

/* do something */

-- single statement version

SELECT * FROM Sales.SalesOrderHeader

OPTION (RECOMPILE);

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
@ProductID INT
AS

SELECT SalesOrderDetailID, OrderQty
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
OPTION (OPTIMIZE FOR (@ProductID=945));

ALTER PROCEDURE Get_OrderID_OrderQty

@ProductID INT

SELECT SalesOrderDetailID, OrderQty

FROM Sales.SalesOrderDetail

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
@StateProvinceID int
AS
SELECT DISTINCT City
FROM Person.Address
WHERE StateProvinceID=@StateProvinceID
OPTION (OPTIMIZE FOR UNKNOWN)
GO

CREATE PROCEDURE dbo.GetCities

@StateProvinceID int

SELECT DISTINCT City

FROM Person.Address

WHERE StateProvinceID=@StateProvinceID

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
@UserID INT
AS
IF @UserID IN (SELECT UserID FROM dbo.ProlificUsers)
BEGIN
EXEC dbo.GetProlificPosts @UserID
END
ELSE
BEGIN
EXEC dbo.GetNormalPosts @UserID
END

CREATE PROCEDURE dbo.GetStackOverflowPosts

@UserID INT

IF @UserID IN (SELECT UserID FROM dbo.ProlificUsers)

BEGIN

EXEC dbo.GetProlificPosts @UserID

END

ELSE

BEGIN

EXEC dbo.GetNormalPosts @UserID

END

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.