Parameter sniffing in SQL server 2005

I had an issue in one of our SQL server stored procedures recently with query execution plan caching. I had a datetime input parameter which caused a serious slowness to stored procedure. Initially we could not find out what was going on but after digging deep in the code base we found it is happening in stored procedural level. Query execution plan caching is a good thing as this helps the DB to deliver the data quite quickly but some cases caching can cause real night mares as what we faced in our stored proc.

We sorted the parameter sniffing issue with local variable declaration in the stored procedure. When declare the local variable and assign the input parameter into the local variable, SQL server doesn�t cache the parameter. This way we can disable to parameter sniffing in SQL server 2005.

Following is an example how the stored procedure was before the change made in receiving input parameter.


Code:
CREATE PROCEDURE usp_getdate(@IDate datetime=Null)
AS
  BEGIN
  SELECT CUSID,CUSTNAME FROM TB_Customer where DateAdded<@IDate AND ...;
  END
GO


After adding the local variable

Code:
CREATE PROCEDURE usp_getdate(@IDate datetime=Null)
AS
  BEGIN
   Decalre @lclDate datetime
   SET  @lclDate=@IDate;
  SELECT CUSID,CUSTNAME FROM TB_Customer where DateAdded<@IDate AND ...;
  END
GO


Above stored proc will remove the cache.

Also there is another way to disable the parameter sniffing is use OPTION hint in the query to recompile the execution plan.

So above query could have been something like

Code:
SELECT CUSID,CUSTNAME FROM TB_Customer where DateAdded<@IDate OPTION(OPTIMIZE FOR (@IDate = Null))


I hope this article will help someone out there.

Thanks for reading this.

Commentaires

Enregistrer un commentaire

Posts les plus consultés de ce blog

XAJAX with PHP – The future of web development

XAJAX is a class library or framework written purely on PHP that allows you to easily create powerful, Web-based, Ajax applications or plugins. You can download xajax framework from http://sourceforge.net/projects/xajax/ XAJAX is easy to impement. You need to just include XAJAX library, instantiate the xajax object, and register the name of function (or an object or class method) which you want to call from XAJAX framework. xajaxResponse() is an object to return the results of our requests as XML formats. Then call that particular function you want from xajax framework through your HTML or javascript. That's it. XAJAX takes care of most everything else. You just need to write the PHP functions and returning XAJAX XML responses from them, which is made extremely easy by the xajaxResponse class. Code: <?php include './xajax/xajax_core/xajax.inc.php'; $xajax = new xajax(); $rqstAdd =& $xajax->register(XAJAX_FUNCTION, 'ad...
Lire la suite

XAJAX with PHP – The future of web development

XAJAX is a class library or framework written purely on PHP that allows you to easily create powerful, Web-based, Ajax applications or plugins. You can download xajax framework from http://sourceforge.net/projects/xajax/ XAJAX is easy to impement. You need to just include XAJAX library, instantiate the xajax object, and register the name of function (or an object or class method) which you want to call from XAJAX framework. xajaxResponse() is an object to return the results of our requests as XML formats. Then call that particular function you want from xajax framework through your HTML or javascript. That's it. XAJAX takes care of most everything else. You just need to write the PHP functions and returning XAJAX XML responses from them, which is made extremely easy by the xajaxResponse class. Code: <?php include './xajax/xajax_core/xajax.inc.php'; $xajax = new xajax(); $rqstAdd =& $xajax->register(XAJAX_FUNCTION, 'a...
Lire la suite

Database connection pooling in ADO.Net

Imagine a situation that multiple users are connecting the same database through our application. In an application establishing a database connection and logging into it is a time consuming activity. It needs several seconds for every time it establishes a connection. So opening and maintaining a database connection for each user is not an ideal solution. When the number of users increases, and if the system�s memory set is limit, probably it leads to a system crash. This problem can solve through connection pooling. A connection pool is a cache or pool of database connections maintained by the application so that the connections can be reused when the database receives future requests for data. Establishing once and reserving it for future request can essentially improve the overall performance of the application. In connection pooling, after a connection is created, the connection object is placed in the pool. When a simultaneous connection request is made later, ...
Lire la suite