May 21, 2009 at 5:56 pm
Performance Difference between Stored Procedure Parameter and local variable in the procedure.
This is on an up to date patched SQL Server 2005 running on Windows Server 2003.
Has anyone seen this type of behaviour and might explain why a local variable copy of a procedure parameter makes the query run what I call normally from SQL Agent. We are trying to understand if maybe we should be duplicating procedure parameters into local variables inside procedures always.
The difference in performance is 2.5 minutes normal and 15-18 minutes slow. There is one very large table in the join set and when its running slow the server stops responding to our RDP session or new connections for the first 2-3 minutes of execution when its running slow. It appears its probably doing at least one table scan and totally thrashing the the hard disc.
Simplified example that is very slow when schedule from
SQL Agent job as "exec dbo.CaptureStats @ModeID=2"
but normal performance if run
from Query Analyser as "exec dbo.CaptureStats @ModeID=2"
create proc dbo.CaptureStats (@ModeID int) as begin insert into TableY selectfrom < 8 joined tables> and Table5.ModeID = @ModeID Where end go
If the procedure is modified to CaptureStatsHACK it runs fast in both SQL Agent and in Query Analyser.
create proc dbo.CaptureStatsHACK (@ModeID int) as begin declare @ModeIDDupe int set @ModeIDDupe = @ModeID insert into TableY selectfrom < 8 joined tables> and Table5.ModeID = @ModeIDDupe Where end go
Thanks for any ideas,
Rob.
May 21, 2009 at 9:43 pm
Search this site and google for articles on parameter sniffing. That is what you are running into here.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2009 at 10:59 pm
Thank you for the name that helps me a lot in getting context.
May 29, 2009 at 9:22 am
You are welcome - let use know if there is any additional help you need.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 29, 2009 at 9:31 am
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy