Performance Difference between Stored Procedure Parameter and local variable in procedure.

Question

Performance Difference between Stored Procedure Parameter and local variable in procedure.

Robin Luiten

SSC Enthusiast

Points: 144
More actions
May 21, 2009 at 5:56 pm

#137964
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
select 
from  < 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
select 
from  < 8 joined tables>  and  Table5.ModeID = @ModeIDDupe
Where 
end
go
```
Thanks for any ideas,
Rob.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90348 More actions · Answer 1

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

Robin Luiten SSC Enthusiast Points: 144 More actions · Answer 2

Thank you for the name that helps me a lot in getting context.

Jeffrey Williams SSC Guru Points: 90348 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Performance Difference between Stored Procedure Parameter and local variable in procedure.

Cookies on SQLServerCentral