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,
— Insert 1500 records into Table1
INSERT INTO Table1 (Column2) VALUES (1)
SELECT TOP 1499 IDENTITY(INT, 1, 1) AS n INTO #Temp
INSERT INTO Table1 (Column2)
SELECT 2 FROM #Temp
DROP TABLE #Temp
****************CREATE A NON CLUSTERED INDEX ON COLUMN2**********************
CREATE NONCLUSTERED INDEX [indexTable1_Column2] ON [dbo].[Table1]
)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]
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.
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
SELECT * FROM Table1
WHERE Column2 = @Col2Value
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.