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

  • 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.

  • 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

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

  • 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

  • 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

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

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