Parameter Sniffing in SQL Server

Introduction

Query parameterization supports the reuse of cached execution plans, so one can avoid unnecessary compilations and reduce the number of ad hoc queries in the cache plan. This is a good thing to use a parameterized query that use the same cached execution plan for various parameter values. Parameter sniffing is enabled by default in SQL Server. SQL Server chooses an execution plan based on the specific parameter values that exist at the time of compilation.

SQL Server attempts to optimize the execution of a parameterized query or Stored Procedure or function by creating a compiled execution plan the first time the parameterized query, Stored Procedured or function is executed. But with a different parameter, SQL Server will use the same execution plan that it created earlier. This is known as parameter sniffing. The old execution plan may be de-optimized for the new parameter and the second call may be very slow.

Problem with parameter sniffing

The execution plan that is created using one set of parameters may not be the most efficient when using a different set of parameters. This is the main disadvantage of parameter sniffing. Let us check this with an example.

Example

Suppose I have two tables called EmployeeMaster and EmployeeDetails. The relation between these two tables are as below.



The following is a script to create the EmployeeMaster and EmployeeDetails tables:

CREATE TABLE [dbo].[EmployeeDetails](
               [EmployeeDetailID] [int] IDENTITY(1000,1) NOT NULL,
               [FirstName] [nvarchar](50) NULL,
               [LastName] [nvarchar](50) NULL,
               [BirthDate] [date] NULL,
               [JoiningDate] [date] NULL,
 CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY CLUSTERED
(
               [EmployeeDetailID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[EmployeeMaster](
               [EmployeeID] [int] IDENTITY(1000,1) NOT NULL,
               [DetailID] [int] NOT NULL,
               [CompanyId] [int] NOT NULL,
               [EmployeeCode] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_EmployeeMaster] PRIMARY KEY CLUSTERED
(
               [CompanyId] ASC,
               [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] 
GO 
ALTER TABLE [dbo].[EmployeeMaster]  WITH NOCHECK ADD  CONSTRAINT [FK_EmployeeMaster_EmployeeDetails] FOREIGN KEY([DetailID])
REFERENCES [dbo].[EmployeeDetails] ([EmployeeDetailID])
GO 
ALTER TABLE [dbo].[EmployeeMaster] NOCHECK CONSTRAINT [FK_EmployeeMaster_EmployeeDetails]
GO

These two tables are heavy loaded, in other words the tables contain more than 10 lakh records and tables contain four company data.

The following is a script to insert some dummy data into these two tables:

--Insert the Data for CompanyId = 1001
DECLARE @count INT =1
DECLARE @currentid INT= 0
DECLARE @companyid INT= 1001 
WHILE (@count<=975669)
BEGIN
               INSERT INTO EmployeeDetails VALUES('FirstName - ' + CAST (@count AS VARCHAR(15)),'LastName - ' + CAST (@count AS VARCHAR(15)),GETDATE(),GETDATE())
               SET @currentid = SCOPE_IDENTITY()
               INSERT INTO EmployeeMaster VALUES(@currentid,@companyid,'Test code - ' + CAST (@count AS VARCHAR(15)))
               SET @count+=1
END 
--Insert the Data for CompanyId = 1002
SET @count = 975670
SET @companyid = 1001 
WHILE (@count<=1075669)
BEGIN
               INSERT INTO EmployeeDetails VALUES('FirstName - ' + CAST (@count AS VARCHAR(15)),'LastName - ' + CAST (@count AS VARCHAR(15)),GETDATE(),GETDATE())
SET @currentid = SCOPE_IDENTITY()
               INSERT INTO EmployeeMaster VALUES(@currentid,@companyid,'Test code - ' + CAST (@count AS VARCHAR(15)))
               SET @count+=1
END 
--Insert the Data for CompanyId = 1003
SET @count =1075669
SET @companyid = 1004 
WHILE (@count<=1095670)
BEGIN
               INSERT INTO EmployeeDetails VALUES('FirstName - ' + CAST (@count AS VARCHAR(15)),'LastName - ' + CAST (@count AS VARCHAR(15)),GETDATE(),GETDATE())
               SET @currentid = SCOPE_IDENTITY()
               INSERT INTO EmployeeMaster VALUES(@currentid,@companyid,'Test code - ' + CAST (@count AS VARCHAR(15)))
               SET @count+=1
END 
--Insert the Data for CompanyId = 1004
SET @count =1095670
SET @companyid = 1004 
WHILE (@count<=1125669)
BEGIN
               INSERT INTO EmployeeDetails VALUES('FirstName - ' + CAST (@count AS VARCHAR(15)),'LastName - ' + CAST (@count AS VARCHAR(15)),GETDATE(),GETDATE())
               SET @currentid = SCOPE_IDENTITY()
               INSERT INTO EmployeeMaster VALUES(@currentid,@companyid,'Test code - ' + CAST (@count AS VARCHAR(15)))
               SET @count+=1
END
--Records = CompanyId
--975669 =  1001
--100000 =  1002
--20000 = 1003
--30000 = 1004

Before writing a Stored Procedure to identify the problem with parameter sniffing, let us examine the result and execution plan of the query (that is used by the Stored Procedure) with a different value for companyId (that is passed as parameter in the Stored Procedure) as in the following:

SELECT * FROM EmployeeMaster em
INNER JOIN EmployeeDetails ed ON em.DetailId = ed.EmployeeDetailId
WHERE em.CompanyId = ?  -- ? replace ? with various companyId

The following is the execution plan of the query above for companyId = 1001:



The following is the execution plan of the query above for companyId = 1002:



The following is the execution plan of the query above for companyId = 1003 and 1004:



Let us examine the result and execution plan of the above query with a Stored Procedure. Create a Stored Procedure that accepts Company Id as a parameter.

Script to create Stored Procedure

CREATE PROCEDURE TestStoreProcedure
(
      @CompanyId INT
)
AS
BEGIN
      SELECT * FROM EmployeeMaster em
      INNER JOIN EmployeeDetails ed ON em.DetailId = ed.EmployeeDetailId
      WHERE em.CompanyId = @CompanyId
END

Now execute this Stored Procedure with companyId = 1002.




This creates and caches the execution plan for the Stored Procedure with the supplied parameter. If we re-run this Stored Procedure with a different parameter then it will use the same execution plan that may differ when the Stored Procedure's query is run with this parameter.



Conclusion

The first call of the Stored Procedure creates the execution plan based on the parameters passed. This plan is stored in the procedure cache for the current and all future executions of this Stored Procedure. This is a good thing when good statics are maintained and typical parameters are used. If the Stored Procedure is used with different parameters then the optimizer will try to use the execution plan that is already created in a previous call, this plan may not be good with the new parameters and this plan that is  doing a table scan with the new parameters, hence a degradation of the performance.