SQL Server Performance

StoredProcedure_Performence

Discussion in 'Performance Tuning for DBAs' started by gsv, Mar 20, 2003.

  1. gsv New Member

    I don't remember how I ran across this initially, but I have a very
    complicated stored proc (500 lines+) that uses some dozen parameters. I
    found that when I reassign the parameters to a local variable and then only
    use the local variables in the actual code, the stored proc runs much
    faster. Is any one come across the situation???can any one guess what could be the reason,any advantages/disadvantages using the parameters in this way???

    Any help is Greately apprciated.


    Here is the sample of StoredProcedure

    CREATE PROCEDURE dbo.ResultsGet
    @xFilter_A as uniqueidentifier,
    @xFilter__B as uniqueidentifier = null,
    @xFilter__c as uniqueidentifier = null
    AS

    DECLARE
    @Filter_A as uniqueidentifier,
    @Filter_B as uniqueidentifier,
    @Filter_C as uniqueidentifier

    SET @Filter_A = @xFilter_A
    SET @Filter_B = @xFilter_B
    SET @Filter_C = @xFilter_C


    Thanks
    Vishnu
  2. Chappy New Member

    Ive not encountered this before either.

    I would start by viewing the execution plans of the two versions and see if anything obvious is thrown up.
  3. gsv New Member

    Thanks for Quick Response...and taking time..

    Regards
    Vishnu
  4. trifunk New Member

  5. chandru New Member

    qsv,

    This is due to "parameter sniffing". If you have a number of parameters with defaults as null, sql server will create the execution plan, based on the basis of these nulls. There are 2 workarounds:

    a. What you have done above? Assign the parameters to local variables.

    b. Try and use defaults which are not nulls. I understand this is not possible always.

    -CR
  6. gsv New Member

    Hi
    Shaun and chandru
    Thanks a lot for your efforts solving the problem,With your postings I understood that "parameter sniffing" is the factor governing the performence.I could not find much documentation on BOL.Any way thanks for your inputs

    GSV

Share This Page