In last week’s blog posting I introduced the basic concept and the problems behind Parameter Sniffing in SQL Server. As you have seen, it can lead to serious performance problems when a cached plan is blindly reused by SQL Server. Today I want to show you how you can deal with this problem, and how to overcome it using a number of different techniques.

Indexing

The underlying root cause of the Parameter Sniffing problem the we discussed last week is the fact that in some cases the SQL statement produced a Bookmark Lookup and in some other cases produced a Table/Clustered Index Scan operator in the execution plan. If you are able to change the indexes in your database, the easiest way is to provide a Covering Non-Clustered Index for this specific query. In our case you have to include the additional requested columns from the Bookmark Lookup in the leaf level of the Non-Clustered Index. If you do that, you have also achieved so-called Plan Stability: regardless of the provided input parameters, the Query Optimizer always compiles the same execution plan – in our case an Index Seek (Non Clustered) operator.

If you don’t have a chance to work on your indexing strategy (maybe you deal with a 3rd party application, where you are not allowed to make indexing changes), you can work with a number of “transparent” SQL Server options that I will describe in the following sections.

Recompilations

The first option that SQL Server offers you is a recompilation of the execution plan. SQL Server provides 2 different options for you to use:

• A recompilation of the whole, complete stored procedure
• A recompilation of the problematic SQL statement – a so called Statement Level Recompilation (available since SQL Server 2005)

Let’s have a more detailed look at both options. The following code shows how you can apply a recompilation of the whole stored procedure with the RECOMPILE option.

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

When you run such a stored procedure, the Query Optimizer always recompiles the stored procedure at the beginning of the execution. Therefore you always get an execution plan which is optimized for the currently provided input parameter values. As a side-effect the execution plan no longer gets cached, because it doesn’t make sense to cache a query plan which is recompiled every time. When you have a large, complicated stored procedure a RECOMPILE query hint at the stored procedure level doesn’t always make sense, because your whole stored procedure is recompiled.

Maybe you have a Parameter Sniffing problem in just one specific SQL statement. In that case the overhead for the recompilation of the whole stored procedure would be too much. For that reason SQL Server, since version 2005, offers a so-called Statement Level Recompilation. You are able to mark a specific SQL statement for recompilation instead of the complete stored procedure. Let’s have a look at the following code.

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

        SELECT * FROM Table1
	WHERE Column2 = @Col2Value
        OPTION (RECOMPILE)
GO

In that example the second SQL statement is recompiled every time that the stored procedure is executed. The first statement is compiled during the initial execution, and the generated plan is cached for further reuse. That’s the preferred way to deal with Parameter Sniffing when you have no influence on the indexing strategy of your database.

OPTIMIZE FOR

In addition to the recompilation of a stored procedure or the SQL statement, SQL Server also offers you the OPTIMIZE FOR query hint. With that query hint you are able to tell the Query Optimizer for which specific parameter values the generated query plan should be optimized. Let’s have a more detailed look at the following example.

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

As you can see from the stored procedure definition, the execution plan of the SQL statement is always optimized for an input parameter value of 1 for the parameter @Col2Value. Regardless of which input value you provide for this parameter, you will always get a plan compiled for the value of 1. With this approach you are already working with a sledgehammer against SQL Server, because the Query Optimizer no longer has any choice – it must always produce a plan optimized for the parameter value of 1. You can implement this query hint when you know that a query plan optimized for a specific parameter value should almost always be generated. You will be able to predict your query plans when you restart SQL Server or when you perform a cluster failover.

If you are going to go down this route, you really have to know your data distribution, and you also need to know when your data distribution changes. If the data distribution changes, you also have to review your query hint to see if it’s still appropriate. You can’t rely on the Query Optimizer, because you just have overruled the Query Optimizer with the OPTIMIZE FOR query hint. You must always keep this in mind! In addition to the OPTIMIZE FOR query hint, SQL Server also offers the OPTIMIZE FOR UNKNOWN query hint. If you decide to use that query hint, the Query Optimizer uses the Density Vector of the underlying statistics object to derive the cardinality. The plan that the Query Optimizer generates for you depends on the data distribution. If your logical reads are over the Tipping Point, you end up with a Table/Clustered Index Scan…

Summary

In this blog posting I have shown you multiple ways to deal with the Parameter Sniffing problem in SQL Server. One of the most common root causes of this specific problem is a bad indexing strategy, where with a selective parameter value the Query Optimizer introduces a Bookmark Lookup into the execution plan. If such a plan gets reused in the future, your I/O costs will explode. I’ve already seen execution plans in production, which generated more than 100 GB of logical reads, just because of this problem. A simple RECOMPILE query hint on the statement level fixed the problem, and the query produced just a few logical reads.

If you can’t influence the indexing strategy of the database, you can work with the RECOMPILE query hint on the stored procedure or SQL statement level. As a side effect the recompiled plan will no longer be cached. In addition to these query hints, SQL Server also offers you the OPTIMIZE FOR and OPTIMIZE FOR UNKNOWN query hints. If you work with these query hints, you really have to know your data and your data distribution, because you are overruling the Query Optimizer. Be always aware of this fact!

Thanks for your time,

-Klaus