Parameter Sniffing in SQL Server is SQL Server’s effort to reduce the CPU overhead by caching the query execution plan and using it for all similar queries instead of recompiling the query each time at execution.

The way SQL Server chooses the best plan is by cost estimation. The query plan takes into consideration the cardinality estimation based on the input parameters and the help of statistics.

The optimizer creates an execution plan that sniffs the parameter values. The problem arises when the query uses a previously generated plan optimized for a different data distribution.

I have listed a few methods to correct parameter sniffing below with examples. First lets create a test database with some test data.

/*******************************************
 * Create Database
 *******************************************/
USE MASTER
GO

CREATE DATABASE ProductDB
ON 
( NAME = 'Product_dat',
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Productdata.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
LOG ON
( NAME = 'Product_log',
   FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Productlog.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )
GO

ALTER DATABASE [ProductDB] SET RECOVERY SIMPLE
GO

/*******************************************
 * Create tables
 *******************************************/
USE ProductDB
GO

IF OBJECT_ID('dbo.Product', 'U') IS NOT NULL
  DROP TABLE dbo.Product
GO

CREATE TABLE Product
  (
   ProductID INT IDENTITY(1,1) NOT NULL,
   ProductName VARCHAR(50) NOT NULL,
   ProductColour VARCHAR(50) NOT NULL,
   Cost MONEY NOT NULL,
   ProductCategoryID INT NOT NULL,
   PRIMARY KEY CLUSTERED (ProductID)
  )

IF OBJECT_ID('dbo.ProductCategory', 'U') IS NOT NULL
  DROP TABLE dbo.ProductCategory
GO

CREATE TABLE ProductCategory
  (
   ProductCategoryID INT IDENTITY(1,1) NOT NULL,
   CategoryDescription VARCHAR(50) NOT NULL,
   PRIMARY KEY CLUSTERED (ProductCategoryID)
  )

CREATE INDEX IX_Product_ProductCategoryID 
ON Product(ProductCategoryID)

/*******************************************
 * Insert test data
 *******************************************/
INSERT INTO [dbo].[ProductCategory]
(
	CategoryDescription
)
SELECT 'Bicycle'
GO

INSERT INTO [dbo].[ProductCategory]
(
	CategoryDescription
)
SELECT 'Bicycle Accessories'
GO

INSERT INTO [dbo].[Product] 
( 
     [ProductName]
    ,[ProductColour]
    ,[Cost]
    ,[ProductCategoryID]
)

SELECT	 'Mountain Bike'
		,'Red'
		,899
		,1
GO

INSERT INTO [dbo].[Product] 
( 
     [ProductName]
    ,[ProductColour]
    ,[Cost]
    ,[ProductCategoryID]
)
SELECT	 'Helmet'
		,'Orange'
		,79
		,2
GO

Disable parameter sniffing for a specific query
You can use a trace flag as a hint in the query to change the behaviour of the query optimizer. You do this by adding the QUERYTRACEON hint to the OPTION clause.

USE ProductDB
GO

CREATE PROCEDURE dbo.sp_QUERYTRACEON_Hint 
@ProductCategoryID   INT
AS

SELECT	 P.ProductName
		,P.ProductColour
FROM  dbo.Product			P
JOIN dbo.ProductCategory	C ON P.ProductCategoryID = C.ProductCategoryID
WHERE  C.ProductCategoryID = @ProductCategoryID
OPTION(QUERYTRACEON 4136)
GO

Create stored procedures using WITH RECOMPILE
A recompile will create a new execution plan with new parameters. This is not the best solution as the recompilation will increase CPU load and this could be problematic in heavy concurrent systems. If the problem is a single query within the stored procedure then recompiling the entire stored procedure is not the best approach, rather correct the problematic query.

USE ProductDB
GO

CREATE PROCEDURE dbo.sp_With_Recompile 
@ProductCategoryID   INT
WITH RECOMPILE
AS
SELECT	 P.ProductName
		,P.ProductColour
FROM  dbo.Product			P
JOIN dbo.ProductCategory	C ON P.ProductCategoryID = C.ProductCategoryID
WHERE  C.ProductCategoryID = @ProductCategoryID
GO

Use SQL Server hints such as OPTION (RECOMPILE) or OPTION (OPTIMIZE FOR)
As mentioned above previously you should rather correct the problematic query than recompile the entire stored procedure. You can do this by using the hint RECOMPILE.

USE ProductDB
GO

CREATE PROCEDURE dbo.sp_Recompile_Hint
@ProductCategoryID   INT
AS
SELECT	 P.ProductName
		,P.ProductColour
FROM  dbo.Product			P
JOIN dbo.ProductCategory	C ON P.ProductCategoryID = C.ProductCategoryID
WHERE  C.ProductCategoryID = @ProductCategoryID
OPTION(RECOMPILE)
GO

Using the OPTIMIZE FOR hint will allow you to set a parameter value to use as a reference for optimization. If you are using SQL Server 2008 or above then you can use OPTIMIZE FOR UNKNOWN if you are not sure which parameter values the stored procedure will use to execute.

USE ProductDB
GO

CREATE PROCEDURE dbo.sp_Optimize_Unknown_Hint
@ProductCategoryID   INT
AS
SELECT	 P.ProductName
		,P.ProductColour
FROM  dbo.Product			P
JOIN dbo.ProductCategory	C ON P.ProductCategoryID = C.ProductCategoryID
WHERE  C.ProductCategoryID = @ProductCategoryID
OPTION(OPTIMIZE FOR UNKNOWN )
GO

Use dummy variables in stored procedures
You will assign the input parameter to a local variable and the query will use this variable instead of the parameter.

USE ProductDB
GO

CREATE PROCEDURE dbo.sp_Dummy_Variable
@ProductCategoryID   INT
AS
DECLARE @Dummy    INT

SELECT @Dummy = @ProductCategoryID

SELECT	 P.ProductName
		,P.ProductColour
FROM  dbo.Product			P
JOIN dbo.ProductCategory	C ON P.ProductCategoryID = C.ProductCategoryID
WHERE  C.ProductCategoryID = @Dummy
GO

Leave a Reply