|
|
| Article |
net guru
Contributing Member
Articles: 5 Comments: 2
|
|
|
| Posted: Mon Jun 23, 2008 9:55 am |
|
|
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. |
|
|
| Comments |
net guru
Contributing Member
Joined: 14 Feb 2007
Articles: 5
Comments: 2
Location: New Delhi |
|
|
| |
Page 1 of 1 |
All times are GMT
You cannot post articles in this chapter
You cannot edit your articles in this chapter
You cannot delete your articles in this chapter
You cannot rate articles in this chapter
You cannot post comments in this chapter
You cannot edit your comments in this chapter
You cannot delete your comments in this chapter
You cannot rate comments in this chapter
|
|
