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.

Leave a Comment



Latest Posts

Enhance Customer Registration Process with Cutting-Edge Solutions from Card Scanning Solutions Inc.

November 4, 2013

JavaScript Threading - Part 2 (Worker)

December 18, 2012

JavaScript Threading - Part 1 (Timers)

December 18, 2012

MS SQL: Parameter Sniffing

May 21, 2012

Solving Cross Browser Issues - Part 3 (Mootools and HTML5)

February 23, 2012

Solving Cross Browser Issues - Part 2 (GWT and Dart)

February 7, 2012

Solving Cross Browser Issues - Part 1 (JQuery and GWT)

January 9, 2012

Be the best stalker you can be

December 13, 2011

AjaxControlToolkit (ASP.NET/C#) : CascadingDropDown Extender - Part 2

November 3, 2011

AjaxControlToolkit (ASP.NET/C#) : CascadingDropDown Extender - Part 1

October 7, 2011


Most Commented on Posts

Moving items between listboxes in ASP.net/PHP example

Move items between two listboxes in ASP.net(C#, VB.NET) and PHP
June 12, 2008

Simple WYSIWYG Editor

Creating a WYSIWYG textbox for your website is actually quite simple.
February 1, 2007

C# YouTube : Google API

Post on how to integrate with YouTube using the Google Data API
March 12, 2011

Populate a TreeView Control C#

Populate a TreeView control in a windows application.
August 27, 2009

Cross Browser Issues: Firefox Word Wrapping

Firefox word wrapping issues
June 9, 2008


ASP.NET   C#   C++   Comedy   Cross Browser   Design Patterns   IIS   Integration   JavaScript   Microsoft Office   Personal   PHP   SQL   Threading   Visual Basic   XML