For long stored procedures, how to quickly identity the most time-consuming part?

Question

Sometimes we need to deal with long stored procedure to make them run faster. What's the best way to quickly identify which part of the code is the slowest part? For me I just add some PRINT statement in the stored procedure and run it, then I can find which part is slow. I want to know are there any alternative methods?

Answer 1

For me almost the same as you, just insert the start time and end time of each part of the procedure into a log table and then check the records. print just help you to check 1 time. log table could help you to see if the procedure got some problems.

Answer 2

Execute the the procedure with "execution plan". This will help you to identify which part of the procedure is taking more time. Also it will suggest you if you require to add any indexes.

Before executing your script in "SQL Server Management Studio" select the "Include Actual Execution plan" or use Ctrl+M and then run the Script / Procedure call.

In the Execution Plan window (next to result tab) you can see and analyse it in detail.

Answer 3

Concur with Raffaello. Specifically:

    --initialise
    DELETE FROM DB..Perf_Log;
    DECLARE @lastTime datetime
    set @lastTime=getdate()

    /* do some shit */

    --add this block after each big block of functionality that you want to test
    insert into DB..Perf_Log values ('did some stuff 1',datediff("MILLISECOND",@lastTime,getdate()))
    set @lastTime=getdate()

This way you can see what's causing the trouble instantly, even if the stored proc takes ages to run. It's useful even if the stored proc hits a snag, because you can see what the last successful thing was. Good luck.

Answer 4

Use SQL Profiler to connect and observe each statement and it's timing.

Use events starting with SP: to observe but be aware Profiler can have it's own impact on performance.

https://dba.stackexchange.com/questions/29284/how-to-profile-stored-procedures