How to identify the slowest queries in stored procedure or batch using SET STATISTICS TIME?

Question

I have a very long SQL script including 1500 rows of code and someone think it's slow and hope I can find slow queries inside it and tune them.

I use SET STATISTICS TIME ON to print out real elapsed time for each steps in the script and found some of them are quit slow, such as :

/***********************************************/  
   CPU time = 0 ms,  elapsed time = 1 ms.

(0 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(4 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:  
   CPU time = 15 ms,  elapsed time = 10548 ms.  
SQL Server parse and compile time:   
   CPU time = 0 ms, elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

/***********************************************/

But the problem is I cannot pinpoint which query spent such long time, because the script is too long. How can I know which query in this long script spent 10548 ms?

Thanks!

http://stackoverflow.com/questions/2499910/how-to-find-the-worst-performing-queries-in-sql-server-2008 This has multiple answers.If you dont find there, search over internet there will be loads of profiler usages — Recursive, Oct 09 '14 at 09:58
https://www.simple-talk.com/sql/performance/how-to-identify-slow-running-queries-with-sql-profiler/ — mohan111, Oct 09 '14 at 10:08

score 1 · Answer 1 · answered Oct 09 '14 at 11:05

I got the answer by using profiler: Using SP:STMTCompleted trace event and use filter to only trace your connection. Each single TSQL inside Stored procedure will be printed out including duration. When the running of SP is done, coping the trace result to an Excel and sort it by durations then you get the slowest TSQL. But Note SP:STMTCompleted only works for stored procedure. I have no idea how to do it for batch.

score 0 · Answer 2 · answered Oct 09 '14 at 09:39

lists out data with the slowest performing first and slowest running queries.

SELECT  creation_time 
            ,last_execution_time
            ,total_physical_reads
            ,total_logical_reads 
            ,total_logical_writes
            , execution_count
            , total_worker_time
            , total_elapsed_time
            , total_elapsed_time / execution_count avg_elapsed_time
            ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
             ((CASE statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END
                - qs.statement_start_offset)/2) + 1) AS statement_text
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    ORDER BY total_elapsed_time / execution_count DESC;

I know this query but it's not what I want. I need to know the slowest queries inside a long stored procedure, not the slowest query in whole SQL Server. We have a lots of slow queries running on SQL Server but this time I only want to focus on one stored procedure. — user3093893, Oct 09 '14 at 09:43

How to identify the slowest queries in stored procedure or batch using SET STATISTICS TIME?

2 Answers2