Parameter Sniffing

What is parameter sniffing?

This problem occurs when you work with parametrized SQL statements like stored procedures in combination with the plan caching mechanism of SQL Server.

When you execute a parametrized SQL query in SQL Server, the Query Optimizer compiles an execution plan based on the first provided parameter values. Afterwards the generated query plan is cached in the plan cache for further reuse. This means that SQL Server will reuse the plan afterwards without regard for the parameter values that you provide at the time.

2 kind of parameter value:

  • Compile time values
  • Run time values

The Compile time parameter values are the values that are used by the Query Optimizer to generate a physical execution plan. And the Run time values are the values that you provide for the execution of the query plan.

For the first execution these values are identical, but for subsequent executions, these values will probably be different. This may introduce serious performance problems, because your execution plan is always optimized for the Compile time values, and not for the various Run time values that you subsequently provide.Imagine now what happens if a query plan with a Bookmark Lookup gets cached, and afterwards gets blindly reused with a non-selective value. In that case SQL Server doesn’t implement any protection mechanism and will just execute the found query plan from the plan cache. As a result   I/O costs (your logical reads) will just explode, and the query will end up with very bad performance.

To show you a concrete example of this behavior, the following code creates a simple table with an uneven data distribution in the second column.

— Create a test table
CREATE TABLE Table1
(
Column1 INT IDENTITY,
Column2 INT
)
GO

— Insert 1500 records into Table1
INSERT INTO Table1 (Column2) VALUES (1)

SELECT TOP 1499 IDENTITY(INT, 1, 1) AS n INTO #Temp
FROM
master.dbo.syscolumns sc1

INSERT INTO Table1 (Column2)
SELECT 2 FROM #Temp
DROP TABLE #Temp
GO

****************CREATE A NON CLUSTERED  INDEX ON COLUMN2**********************

CREATE NONCLUSTERED INDEX [indexTable1_Column2] ON [dbo].[Table1]
(
[Column2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Lets run the execute the select statement.

select * from Table1 where column2=1
select * from Table1 where Column2=2

This is an example of uneven distribution of data where value 1 occurs only once and that the value 2 occurs 1499 times.

We see 2 different execution plan for the same logical query.

SQL query1

Now when we create a stored procedure, the query optimizer will create a execution plan based on the initial parameter values and afterwards same execution plan will be used by the subsequent executions.

— Create a new stored procedure for data retrieval
CREATE PROCEDURE RetrieveData
(
@Col2Value INT
)
AS
SELECT * FROM Table1
WHERE Column2 = @Col2Value
GO

Now when we execute the procedure with value 1 it just return one record, query optimizer will choose a bookmark lookup in the execution plan.The query just itself produces a 3 logical reads. when you execute with the parameter value of 2, the cache plan get reused and bookmark lookup is done 1499 times as a result 1505 logical reads.This is a huge difference from the previous execution.