In today’s blog posting I want to talk about one major performance problem that I see very often during SQL Server consulting engagements – the so-called Parameter Sniffing problem. This problem occurs when you work with parameterized SQL statements like stored procedures in combination with the plan caching mechanism of SQL Server. In the first part of this blog posting I will introduce the scope of this problem to you, and in the second part next week I will show you several techniques for solving it.

Parameter Sniffing – what?

When you execute a parameterized 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. We need to recognize 2 kinds of parameter values:

• Compile time values
• Runtime values

The Compile time parameter values are the values that are used by the Query Optimizer to generate a physical execution plan. And the Runtime 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 Runtime values that you subsequently provide.

Imagine that during the first query execution you provide a very selective value, and the Query Optimizer chooses a Non-Clustered Index Seek operation followed by a Bookmark Lookup operation to retrieve additional requested columns from your base table. Such an execution plan only makes sense with a selective value, because with a non-selective value your logical reads would be too high, and SQL Server would just scan the whole table and discard non-qualifying rows through a residual predicate in the Scan operator. The point where SQL Server decides between the two plan shapes is the so-called Tipping Point.

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 side-effect your 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 #Nums
FROM
master.dbo.syscolumns sc1

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

When you have a look at the table, you can see that the value 1 occurs only once, and that the value 2 occurs 1499 times. This is a simple example of an uneven data distribution but you will often see this in your own tables: you have a lot of customers in your own country, and just a few other customers in other countries. Based on that uneven data distribution, a Non-Clustered Index on this column, and the Tipping Point, you get 2 different execution plans for the same logical query:

When you now create a stored procedure, the Query Optimizer will generate the execution plan based on the first provided parameter values, and afterwards the query plan will be blindly reused by subsequent executions.

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

When you now execute the stored procedure with the parameter value of 1, where you just return one record, the Query Optimizer will choose a Bookmark Lookup in the execution plan. The query itself just produces 3 logical reads. But when you next execute the stored procedure– this time with a parameter value of 2 – the cached plan gets reused and the Bookmark Lookup is done 1499 times – over and over again. For every returned record! The query now results in1505 logical reads. That’s a huge difference from the previous execution. When you look at the properties window of the SELECT operator you can also see that we have a parameter list. For every parameter SQL Server shows the Compile and Runtime values.

And as you can see they are different. The Compile time value is 1, and the Runtime value is 2. This means that the execution in front of you was optimized for an input parameter value of 1, but actually you executed the stored procedure with a parameter value of 2. That’s the Parameter Sniffing problem in SQL Server.

Continue reading on part 2.

Summary

As you have seen it is very easy in SQL Server to hit that specific problem. Every time that you use parameterized SQL queries (such as in stored procedures) you have to deal with this side-effect when you have an uneven data distribution in your table, and your provided Non-Clustered Index is not a covering one. Now that I have introduced you to the problem, I will show you next week how you can deal with this specific problem, and which solutions SQL Server provides for you.

Thanks for your time,

-Klaus