Different Approaches to Correct SQL Server Parameter Sniffing

Problem

You have a SQL Server stored procedure that has been performing fairly well, but suddenly its execution times skyrocket. After looking at the cached execution plan you see that the query is using all indexes as should be. What’s going on? Keep reading and you will find an answer.

Solution

How many times when a process takes longer than expected you have listened to the same complaint: It was working yesterday? This is a tough complaint to face, especially when you need to explain to someone with little knowledge about how a RDBMS works. Of course there are a lot of things to consider when answering this questioning and one that you should not overlook is Parameter Sniffing.

What is Parameter Sniffing in SQL Server?

Every batch you execute, either ad-hoc or stored procedure, generates a query plan that is kept in the plan cache for future usage. SQL Server attempts to create the best query plan to retrieve the data, but what may seem obvious is not always the case with the plan cache.

The way SQL Server choses the best plan is by cost estimation. For example, if I ask you which is best, an index seek followed by a key lookup or a table scan you may answer the first, but it depends on the number of lookups. In other words it depends on the amount of data being retrieved. So the best query plan takes into consideration the cardinality estimation based on input parameters and with the help of statistics.

When the optimizer creates an execution plan it sniffs the parameter values. This is not an issue; in fact it is needed to build the best plan. The problem arises when a query uses a previously generated plan optimized for a different data distribution.

In most cases the database workload is homogeneous so parameter sniffing won’t be a problem; but on a small number of cases this becomes a problem and the outcome can be dramatic.

SQL Server Parameter Sniffing In Action

Now I will demonstrate a parameter sniffing issue with an example.

1. CREATE DATABASE script.

USE [master]
GO
CREATE DATABASE [TestDB]
CONTAINMENT = NONE
ON PRIMARY 
( NAME = N'TestDB', FILENAME = N'E:\MSSQL\TestDB.mdf' , 
SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'TestDB_log', FILENAME = N'E:\MSSQL\TestDB.ldf' , 
SIZE = 5MB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO
ALTER DATABASE [TestDB] SET RECOVERY SIMPLE

2. Create two simple tables.

USE TestDB
GO
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
  DROP TABLE dbo.Customers
GO
CREATE TABLE Customers
  (
   CustomerID INT NOT NULL IDENTITY(1,1) ,
   CustomerName VARCHAR(50) NOT NULL ,
   CustomerAddress VARCHAR(50) NOT NULL ,
   [State] CHAR(2) NOT NULL ,
   CustomerCategoryID CHAR(1) NOT NULL ,
   LastBuyDate DATETIME ,
   PRIMARY KEY CLUSTERED ( CustomerID )
  )
IF OBJECT_ID('dbo.CustomerCategory', 'U') IS NOT NULL
  DROP TABLE dbo.CustomerCategory
GO
CREATE TABLE CustomerCategory
  (
   CustomerCategoryID CHAR(1) NOT NULL ,
   CategoryDescription VARCHAR(50) NOT NULL ,
   PRIMARY KEY CLUSTERED ( CustomerCategoryID )
  )
CREATE INDEX IX_Customers_CustomerCategoryID 
ON Customers(CustomerCategoryID)

3. The idea with the sample data is to create an odd distribution.

USE TestDB
GO
INSERT INTO [dbo].[Customers] ( 
                [CustomerName],
                [CustomerAddress],
                [State],
                [CustomerCategoryID],
                [LastBuyDate])
SELECT 
    'Desiree Lambert',
    '271 Fabien Parkway',
    'NY',
    'B',
    '2013-01-13 21:44:21'

INSERT INTO [dbo].[Customers] ( 
                [CustomerName],
                [CustomerAddress],
                [State],
                [CustomerCategoryID],
                [LastBuyDate])
SELECT  
    'Pablo Terry',
    '29 West Milton St.',
    'DE',
    'A',
    GETDATE()
go 15000

4. Execute the following query and take a look at the query plan.

USE TestDB
GO
SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = 'A'
SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = 'B'

As you can see, the first query performs a clustered index seek on the CustomersCategory table and a clustered index scan on tghe Customers table whereas the second one uses the non-clustered index (IX_Customers_CustomerCategoryID). That is because the query optimizer is smart enough to anticipate our query results from the given parameters and scans the clustered index instead of performing an index seek followed by a key lookup to our non-clustered index, that would be more expensive due to the fact that our first query returns almost the entire table.

5. Now we create a stored procedure to execute our query.

USE TestDB
GO
CREATE PROCEDURE Test_Sniffing 
@CustomerCategoryID   CHAR(1)
AS
SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID
GO

6. Execute the stored procedure.

USE TestDB
GO
DBCC FREEPROCCACHE()
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO
DBCC FREEPROCCACHE()
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO

On the script we first execute the DBCC FREEPROCCACHE to clean the plan cache and then run the stored procedure. Look at the image below and you will see how the second call to the stored procedure uses the same plan without taking into consideration the parameters given.

After this we perform the same steps, but with the parameters in reverse order and you will see the same behavior, but a different query plan.

Workarounds for SQL Server Parameter Sniffing

Now being exposed to the problem here are a few methods to address it:

Create SQL Server Stored Procedures using the WITH RECOMPILE Option
Use the SQL Server Hint OPTION (RECOMPILE)
Use the SQL Server Hint OPTION (OPTIMIZE FOR)
Use Dummy Variables on SQL Server Stored Procedures
Disable SQL Server Parameter Sniffing at the Instance Level
Disable Parameter Sniffing for a Specific SQL Server Query

Create SQL Server Stored Procedures using the WITH RECOMPILE Option

If the problem is that the optimizer uses a plan compiled with parameters that are no longer suitable then a recompilation will create a new plan with the new parameters right? This is the simplest solution, but not one of the best. If the problem is a single query inside the stored procedure code then performing a recompilation of the entire procedure is not the best approach. We should correct the problematic query.

Furthermore, recompilation will increase CPU load and in heavy concurrent systems it could be as problematic as the issue we are trying to solve.

As an example, below is the sample code to create our previous stored procedure with the recompile option.

USE TestDB
GO
CREATE PROCEDURE Test_Sniffing_Recompile 
@CustomerCategoryID   CHAR(1)
WITH RECOMPILE
AS
SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID
GO

Use the SQL Server Hint OPTION (RECOMPILE)

As I said in the previous paragraph, recompiling the whole stored procedure is not the best choice. We can take advantage of the hint RECOMPILE to recompile the awkward query alone. Look at the sample code below.

USE TestDB
GO
CREATE PROCEDURE Test_Sniffing_Query_Hint_Option_Recompile 
@CustomerCategoryID   CHAR(1)
AS
SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID
OPTION(RECOMPILE)
GO

Now execute the following code and look at the query plan.

USE TestDB
GO
DBCC FREEPROCCACHE()
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Query_Hint_Option_Recompile @CustomerCategoryID 
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Query_Hint_Option_Recompile @CustomerCategoryID 
GO

Stored Procedure Execution with OPTION(RECOMPILE) hint

As you can see on previous image, we have the right plan according to our parameters on both queries.

Use the SQL Server Hint OPTION (OPTIMIZE FOR)

This hint will allow us to set a parameter value to use as a reference for optimization; you can read about this hint from a tip by Greg Robidoux Optimize Parameter Driven Queries with SQL Server OPTIMIZE FOR Hint. Maybe in our scenario we can’t use this hint because we don’t know for sure which parameter values the procedure will use to execute. But if you are using SQL Server 2008 or above then OPTIMIZE FOR UNKNOWN will bring some light. I must warn you that it won’t produce the best plan, speaking in layman terms, the resulting plan will be something in between. So to use this hint you must consider how often the stored procedure is being executed with a wrong plan and how much it impacts your environment having a long running query.

Here is the sample code to use this hint.

USE TestDB
GO
CREATE PROCEDURE Test_Sniffing_Query_Hint_Optimize_Unknown 
@CustomerCategoryID   CHAR(1)
AS
SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID
OPTION(OPTIMIZE FOR UNKNOWN )
GO

The next script is to execute our new Stored Procedure.

USE TestDB
GO
DBCC FREEPROCCACHE()
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Query_Hint_Optimize_Unknown @CustomerCategoryID 
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Query_Hint_Optimize_Unknown @CustomerCategoryID 
GO

And here is the screen capture of its execution plan.

Stored Procedure Execution with OPTION(OPTIMIZE FOR UNKNOWN ) hint

Use Dummy Variables on SQL Server Stored Procedures

This is an old method used on SQL Server versions previous to 2005. It consists of assigning the input parameter to a local variable and uses this variable instead of the parameter.

Look at the sample code below.

USE TestDB
GO
CREATE PROCEDURE Test_Sniffing_Dummy_Var 
@CustomerCategoryID   CHAR(1)
AS
DECLARE @Dummy    CHAR(1)
SELECT @Dummy = @CustomerCategoryID
SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @Dummy
GO

To execute this Stored Procedure you can use this code.

USE TestDB
GO
DBCC FREEPROCCACHE()
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Dummy_Var @CustomerCategoryID 
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Dummy_Var @CustomerCategoryID 
GO

Look at the resulting plan.

Stored Procedure Execution Using Dummy Variables

Disable SQL Server Parameter Sniffing at the Instance Level

Maybe this is the worst choice you can make. As I said before, parameter sniffing is not a bad thing per se and is very useful in most cases to get the best plan. But if you want, starting the instance with trace flag 4136 set will disable parameter sniffing. You can read more about this and the patch level required to use this flag on this KB article: SQL Server 2008 R2 Cumulative Update 2, SQL Server 2008 SP1 Cumulative Update 7 and SQL Server 2005 SP3 Cumulative Update 9 introduce trace flag 4136 that can be used to disable the “parameter sniffing” process.

Before using this method take a look at the next one, it’s far less drastic.

Disable Parameter Sniffing for a Specific SQL Server Query

This may be unknown to you, but a query can use a trace flag as a hint to change the behavior of the query optimizer. The way to do this is by adding the QUERYTRACEON hint to the OPTION clause.

Here is the sample stored procedure followed by its execution.

USE TestDB
GO
CREATE PROCEDURE Test_Sniffing_Query_Hint_QUERYTRACEON 
@CustomerCategoryID   CHAR(1)
AS
SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID
OPTION(QUERYTRACEON 4136)
GO

USE TestDB
GO
DBCC FREEPROCCACHE()
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Query_Hint_QUERYTRACEON @CustomerCategoryID 
GO
DECLARE @CustomerCategoryID CHAR(1)
SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Query_Hint_QUERYTRACEON @CustomerCategoryID 
GO

This is a screen capture of the execution plan.

Next Steps

In this tip you will get more information about parameterizing queries: Using Parameters for SQL Server Queries and Stored Procedures.
Read Greg Robidoux’s tip if you want a deeper explanation about the OPTIMIZE FOR hint: Optimize Parameter Driven Queries with SQL Server OPTIMIZE FOR Hint.
This will help to understand query plans: Graphical Query Plan Tutorial.
Also, if you want to dig further into query plans then this one will be of help: How to read SQL Server graphical query execution plans.
Read this tip if you don’t know too much about SQL Server Queries With Hints.
This tip of Jeremy Kadlec’s could be a day saver: Script to Recompile All SQL Server Database Objects.

Resources

Community

Subscribe