Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    5

    Unanswered: Stored Procedure Optimization

    Hi All, I am new to this forum and this is my first thread. I have one pretty messy stored procedure which in turns call around 8-10 stored procedures. Now I have been asked to optimize it. For this big procedure I don't understand where to start.

    Here are few points:

    1. This procedure takes around 4-5 parameters but the processing happens mostly based on one parameter for which the processing rows can vary from as low as few hundreds to as high as thousands of records.

    2. For biggest size of processing records, the procedure takes almost an hour to complete in production but when we take fresh dump from production to equivalent (in hardware and resources) non-production environment, it takes around 20-25 minutes.

    Any suggestions on this please ?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Maybe in prod there are other processes contending for the same data/locks/cash/hardware/

    Start with the longest running queries

  3. #3
    Join Date
    Mar 2013
    Posts
    5
    Where to check for longest running queries ? I used following query but didn't got the results I was anticipating (we have MDA configured):

    /*
    ** Build work table
    */
    select ProcName = isnull(object_name(ProcedureID, DBID), "UNKNOWN"),
    DBName = convert(char(15), isnull(db_name(DBID), "UNKNOWN")),
    LineNumber,
    ElapsedTime = datediff(ms, StartTime, EndTime)
    into #t1
    from master..monSysStatement
    where ProcedureID != 0
    /*
    ** Calculate aggregate values and find problematic statements
    */
    select ProcName, DBName, LineNumber, "AvgElapsed" = avg(ElapsedTime)
    from #t1
    group by DBName, ProcName, LineNumber
    having avg(ElapsedTime) > (select avg(ElapsedTime) from #t1)
    order by 4 desc

  4. #4
    Join Date
    Mar 2013
    Posts
    5
    Anyone ? Please ?

  5. #5
    Join Date
    Mar 2013
    Posts
    5
    Is this forum usually this silent ? Was looking forward for some good advices here.

  6. #6
    Join Date
    Aug 2003
    Posts
    43
    During the running of the stored procedure in the production environment, you can keep running this query to figure out which piece of query and in which specific SP it is taking long time.

    SELECT A.spid, A.hostname, A.program_name, A.hostprocess, A.cmd, DB_NAME(A.dbid), B.name, A.stmtnum, A.linenum
    FROM master..sysprocesses A,
    sysobjects B
    WHERE A.id*=B.id
    AND A.spid=21 -- If you give SPID you will get specific result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •