Home > Code > How to identify slow running queries in Sql Server

How to identify slow running queries in Sql Server

Recently, I was trying to pin point the queries in an application that were causing Sql Server to spike the CPU at 100%. This query was helpful in finding the top offenders.

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;

 

See this post for more information.

  • Facebook
  • Twitter
  • Delicious
  • Reddit
  • StumbleUpon
  • Share/Save/Bookmark
Author: Categories: Code Tags: ,
  1. May 29th, 2015 at 05:55 | #1

    Third Flower My spouse and i have alerady been now delighted that Albert could carry out his reports on account of the concepts he had through your online page. It really is now and again perplexing to only constantly be freely giving techniques which some people c

  2. June 2nd, 2015 at 05:23 | #2

    I don’t even know what to say, this made things so much easier!

  3. June 26th, 2015 at 13:15 | #3

    We need a lot more insights like this!

  1. No trackbacks yet.