what''s my slow proc doing?

  • hi,

    I'm running a procedure and I can only tell which part of the proc is running by view what files it is updating and by check for certain data values I expect to see. Apart from this guesswork is there are definitive command that tells me which command is actually running?

    sp_who2 give a certain amount of information but not what I need.

    Basically I think there's a bottleneck which I want to find.

    Lastly......... its a 6.5 system

    ANy help wlecome,

    Eamon

  • Can you use the sql profiler to trace your stored procedure?

  • Or look at the execution plan for the proc...

  • >> can only tell which part of the proc is running by view what files it is updating and by check for certain data values I expect to see

    If you can see data changing as the proc runs, then it's not 1 efficient set-based operation within a transaction. Also, using the word "files" instead of tables ? I smell a cursor and a procedural language solution.

     

  • Create a table with two columns. One will be where in the process the sp is the other is the time. Now go to your proc and throughout it add inserts that say what the sp is doing and getdate() to insert data into your new table. Now you can see where the sp is slow by looking at the data in your table.

    This is a little brute force, but with 6.5 that is the road you have to take sometimes.

  • Recompile the stored procedure and see..if it works.

    jyothi..

  • Thanks PW !!!

    You're cursor suspicions were correct.

    It was the bottle neck in the process as I rewrote the logic using several updates.

    THANKS !!!!!!!!

Viewing 7 posts - 1 through 6 (of 6 total)

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