Monthly Archives: May 2014

SQL Server – Finding Resource Intesive or Expensive Queries

It’s suggested you use SQL Profiler trace data ove layed against system information found in the performance monitor. However, to get a quick look at what queries may be slowing down performance , run the following –

select ss.sum_execution_count
    , t.text
    , ss.sum_total_elapsed_time
    ,ss.sum_total_worker_time
    , ss.sum_total_logical_reads
    , ss.sum_total_logical_writes
    from 
        (Select s.plan_handle
        , sum(s.execution_count) sum_execution_count
        , sum(s.total_elapsed_time) sum_total_elapsed_time
        , sum(s.total_worker_time) sum_total_worker_time
        , sum(s.total_logical_reads) sum_total_logical_reads
        , sum(s.total_logical_writes) sum_total_logical_writes
        from sys.dm_exec_query_stats s
        group by s.plan_handle
        ) as ss
        cross apply sys.dm_exec_sql_text(ss.plan_handle) t
        order by sum_total_logical_reads DESC