MS SQL: Parameter Sniffing

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.

Posted by - Christoff Truter
Date - 2012-05-21 22:38:48
Comments - 0

Post/View comments

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

In the previous two parts, I had a look at GWT, JQuery and Dart as possible solutions for solving cross-browser issues.

In this post I am going to have a look at Mootools using the same criteria as in the previous posts and have a look at a HTML5 solution.

Mootools

Note that when I created the main snippet for this post I made use of Mootools version 1.3.2 and was forced to manually add the paste event required by my criteria (you know the event thats not supported by Opera but by everyone else).

 
// Required by older Mootools versions
 
Object.append(Element.NativeEvents, {
  'paste': 2
});
 
Element.Events.paste = {
  base : 'paste',
  condition: function(e) {
 
	// More of an afterpaste event
 
	this.fireEvent('paste', e, 1);
    return false;
  }
};

This all changed however with the latest version (currently 1.4.4) in which you can exclude the preceding snippet - which obviously means that the paste event is now included by default.

In the following snippet you can see the Mootools version of the criteria:

 
window.addEvent('domready', function() {
	$$('textarea').each(function(sender)
	{
		var maxlength = sender.get('maxlength');
		if (maxlength != null)
		{
			sender.addEvent('keypress', function(e){
				if ((e.code == 8) || (e.code > 36) && (e.code < 41)) return true;				
					return sender.value.length < maxlength;
			});
 
			sender.addEvent('paste', function()
			{
				setTimeout(function(){
					if(sender.value.length > maxlength)
						sender.value = sender.value.substring(0, maxlength);}, 1);
			});
		}
	});	
});

Looking at the snippet above you'll probably notice a few similarities to JQuery; the two technologies do infact overlap in quite a number of ways. There is nonetheless a few fundamental difference between them but I am not going to go into too much detail, read about it over here.

Like previously mentioned I initially used an older version of Mootools which gave me a different take on the technology. I felt a bit irritated about having to do something dicey in order to attach something as simple as a paste event, the exact same thing I had to do when using the GWT.

I did however start to see wisdom in an approach of excluding cross-browser unsupported functionality in a framework like Mootools (not sure if this is really one of their aims seeing that the newer version now supports the excluded event).

A framework can provide us with a way to establish boundaries, the premise being that if we play within these boundaries we'll likely be safe. We can of course still allow developers to play outside these boundaries but at their own risk of writing code thats NOT going to be cross-browser friendly (sounds like GWT/JSNI scenario).

I don't believe that the creators of Mootools & JQuery set out to create any real boundaries as such (someone correct me if I am wrong).

It would of course be very difficult to create real boundaries seeing as nothing stops the developer from accessing the raw DOM (even by accident) and using something cross-browser unfriendly.

If you were using something like GWT its a completely different matter, since you don't have real access to the raw DOM (minus using the JSNI, but thats not something you'll use by accident)

HTML5

There is currently a lot of buzz in the air about HTML5 which boasts features like:

Offline storage.
Canvas library.
Audio/Video support.
New inline elements (e.g. mark, time, meter, progress)
New form types (datetime, date, month, week, time, number, range, email, url)

How will this affect cross-browser issues? Well, some suggests that HTML5 will greatly improve the situation. I am personally not too conviced, especially if we look at how shady current implementations are.

Incidently the aim of the criteria used in this series of posts is to add support for something thats actually part of the HTML5 specification, but currently only implemented by 3/5 browsers - we're already busy creating workarounds.

I do nevertheless believe in HTML5 and see it as a step in the right direction.

Also in all fairness I managed to find a HTML5 solution to fully implement the criteria in 5/5 browsers, observe the following Mootools snippet.

 
window.addEvent('domready', function() {
	$$('textarea').each(function(sender)
	{
		var maxlength = sender.get('maxlength');
		if (maxlength != null)
		{
			sender.addEvent('input', function()
			{
				if(sender.value.length > maxlength)
					sender.value = sender.value.substring(0, maxlength);
			});
		}
	});	
});

Instead of attaching handlers to the keypress & paste event, I've only attached a handler to the input event (one of the many new events available in HTML5).

I will have a deeper look into HTML5 is future posts.

In the next part I am going to have a look at Yahoo's YUI library.

Posted by - Christoff Truter
Date - 2012-02-23 21:10:09
Comments - 2

Post/View comments

First 1 2 3 4 5 6 7 8 9 10 Last / 65 Pages (129 Entries)

MS SQL: Parameter Sniffing

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

Mootools

HTML5

Latest Posts

JavaScript Threading - Part 2 (Worker)

JavaScript Threading - Part 1 (Timers)

MS SQL: Parameter Sniffing

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

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

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

Be the best stalker you can be

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

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

Syntactic sugar (C#): Enum

Top 5 posts

Moving items between listboxes in ASP.net/PHP example

Simple WYSIWYG Editor

C# YouTube : Google API

Populate a TreeView Control C#

Cross Browser Issues: Firefox Word Wrapping