MS SQL: Parameter Sniffing

May 21, 2012 by Christoff Truter SQL  

A while ago one of our clients reported a bit of a peculiar performance problem when accessing a certain report in one of their systems. Generally they could retrieve the report in no time, but all of a sudden it took about 3 minutes to retrieve a relatively small amount of data.

Strangely enough the older entries for this report could still be retrieved almost instantly.

So I went through all the motions to find a solution and analyzed the database, found it to be terribly designed and poorly maintained. The developer didn't even bother to create indexes for any of the foreign keys.

Like a good Jedi I added the appropriate indexes for the appropriate fields, but that just made things worse, it now oddly enough even took longer to retrieve data.

I decided to have a look at the guilty stored procedure only to astonishingly find that the stored procedure runs super fast within SSMS (SQL Server Management Studio).

At this point I got up and phoned an exorcist, they came out and prayed over the source code and anointed the server while a bunch of scary looking evil spirits seeped out of the sides of the server box like toxic gases.

Unfortunately that didn't solve the problem at all (fixed an unusual buzzing sound I've been hearing lately though), but all of a sudden I heard the voice of my old master Obi-Wan Kenobi telling me to use the force (google).

I opened my browser and did some research using Google and found the real reason - parameter sniffing.

Uhm okay, maybe I stretched the truth a bit in the last few paragraphs (they're complete BS, except for the google part).

What is parameter sniffing all about?

When sending parameters to a stored procedure for the first time, SQL generates an execution plan based on those parameters and stores it for reuse (generating these plans can be resource intensive & lengthy at times, therefore storing it will improve performance).

Note that we wont necessarily only have one execution plan for a stored procedure, SQL will generate multiple plans based on various settings.

Now sometimes it happens that the stored execution plan sucks, so instead of being efficient it might turn out to be terribly inefficient.

So how do we solve this problem?

One popular solution is to reassign parameters to local variables like seen below (theory being that SQL won't/can't create a cached execution plan thanks to the local variables).

CREATE PROCEDURE [dbo].[ProcedureName]
  @Param1 DATETIME, 
  @Param2 DATETIME
AS
BEGIN
	DECLARE @LocalParam1 DATETIME
	DECLARE @LocalParam2 DATETIME
	
	SET @LocalParam1 = @Param1
	SET @LocalParam2 = @Param2
	
	-- Logic
END

Another option is to disable parameter sniffing alltogether for a stored procedure by creating it using the "WITH RECOMPILE" option like seen in the next snippet.

CREATE PROCEDURE [dbo].[ProcedureName]
  @Param1 DATETIME, 
  @Param2 DATETIME
WITH RECOMPILE
AS
BEGIN
	-- Logic
END

One can also disable sniffing per query using OPTION (RECOMPILE).

CREATE PROCEDURE [dbo].[ProcedureName]
  @Param1 DATETIME, 
  @Param2 DATETIME
AS
BEGIN
	SELECT * FROM SometTable
	WHERE SomeDate BETWEEN @Param1 AND @Param2
	OPTION (RECOMPILE)
END

Or optimize for a typical parameter value.

CREATE PROCEDURE [dbo].[ProcedureName]
  @Param1 DATETIME, 
  @Param2 DATETIME
AS
BEGIN
	SELECT * FROM SometTable
	WHERE SomeDate BETWEEN @Param1 AND @Param2
	OPTION (OPTIMIZE FOR (@Param1 = '2012/02/02'))
END

As of MSSQL 2008 its possible to use the optimize for "UNKNOWN" hint which instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized.

CREATE PROCEDURE [dbo].[ProcedureName]
  @Param1 DATETIME, 
  @Param2 DATETIME
AS
BEGIN
	SELECT * FROM SometTable
	WHERE SomeDate BETWEEN @Param1 AND @Param2
	OPTION (OPTIMIZE FOR (@Param1 UNKNOWN, @Param2 UNKNOWN))
END

I am not going to go into too much detail about the pros/cons of each solution, but you can read a lot more about this issue over here.