October 5, 2011 at 2:17 pm
Hi all, using SQL Server 2008 and the problem is relating to a stored procedure which I am modifying. The current procedure has a parameter @StartDate as SmallDateTime. The first 2 lines in the code are:
DECLARE @RunDate SMALLDATETIME
SET @RunDate = @StartDate
So I thought this was odd that you would replace the parameter variable @StartDate with a new variable with the same data type. I am replacing the destination table for an insert for the procedure so I have created an exact copy of this procedure with the only difference being the new destination table. The speeds of both these procedures are 17 seconds to execute.
I then amended the new procedure so that the parameter variable is @RunDate instead of @StartDate and then removed the 2 lines above as they are no longer necessesary and rerun the code. The procedure never completed (I left it running for 40 mins before I gave up). Adding the 2 lines back and amending the parameter variable back to @StartDate resulted in the 17 secs for execution as before.
The @StartDate parameter is not used anywhere else in the code. I have tried using just datetime instead of smalldatetime for the parameter but still no go unless the variable is redeclared.
The condition columns for the select are smalldatetime data types which are referring to the @RunDate variable.
Presumably the person who created the code redeclared the parameter variable because of the speed difference, but the reason why this makes so much difference escapes me. I have never redeclared variables in my procedures before and they have worked fine.
Perhaps this is an anomaly with the SmallDateTime data type - I usually use DateTime.
Any ideas on the reason why using the parameter variable causes such a degredation in performance?
regards,
Graham
October 5, 2011 at 2:24 pm
This sounds like a case of parameter sniffing, where an inefficent plan is being used due to parameters being passed in.
it is explained nicely here
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/"> http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
one of the devs must have noticed and put in a fix by using local variables
October 5, 2011 at 2:25 pm
I was just getting ready to send the same link.
October 5, 2011 at 2:51 pm
Thanks very much SSCrazy. That's an excellent article and was indeed the problem. Modifying with recompile resulted in execution of 18 secs which is slightly longer than the disabling method which was used in the original procedure.
I am pleasantly surprised I found an answer to this. It is nice to learn why things happen. 🙂
Cheers,
Graham
October 6, 2011 at 4:15 am
For additional methods of dealing with parameter sniffing, check out Chapter 38 of the SQL Server MVP Deep Dives II[/url] book. It covers things not included in the article such as using the OPTIMIZE FOR query hint and others.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 6, 2011 at 1:59 pm
Cheers thanks Grant.
October 7, 2011 at 6:21 am
Grant Fritchey (10/6/2011)
For additional methods of dealing with parameter sniffing, check out Chapter 38 of the SQL Server MVP Deep Dives II[/url] book. It covers things not included in the article such as using the OPTIMIZE FOR query hint and others.
I just grabbed my copy and finished reading your chapter. Great job, Grant!
-- Gianluca Sartori
October 7, 2011 at 7:20 pm
Gianluca Sartori (10/7/2011)
Grant Fritchey (10/6/2011)
For additional methods of dealing with parameter sniffing, check out Chapter 38 of the SQL Server MVP Deep Dives II[/url] book. It covers things not included in the article such as using the OPTIMIZE FOR query hint and others.I just grabbed my copy and finished reading your chapter. Great job, Grant!
Seriously? It was good? I really tried to put something out that would be really useful, not just... stuff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 10, 2011 at 4:00 am
Grant Fritchey (10/7/2011)
Gianluca Sartori (10/7/2011)
Grant Fritchey (10/6/2011)
For additional methods of dealing with parameter sniffing, check out Chapter 38 of the SQL Server MVP Deep Dives II[/url] book. It covers things not included in the article such as using the OPTIMIZE FOR query hint and others.I just grabbed my copy and finished reading your chapter. Great job, Grant!
Seriously? It was good? I really tried to put something out that would be really useful, not just... stuff.
No, it was not good. It was excellent! 🙂
You described the disease, all the known remedies and the reasons behind. What else could I ask for?
Just a side note: it's chapter 32 in my copy. 😛
-- Gianluca Sartori
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply