Capturing High Resource Queries in SQL Server

Hello everyone,

In this article, I will try to give information about capturing high resource queries in SQL Server.

In SQL Server, in some cases, you may want to capture your high resource queries.

You can easily do this using the code below.

SELECT TOP 10
       SUBSTRING(   qt.text,
                    (qs.statement_start_offset / 2) + 1,
                    ((CASE qs.statement_end_offset
                          WHEN -1 THEN
                              DATALENGTH(qt.text)
                          ELSE
                              qs.statement_end_offset
                      END - qs.statement_start_offset
                     ) / 2
                    ) + 1
                ),
       qs.execution_count,
       qs.total_logical_reads,
       qs.last_logical_reads,
       qs.total_logical_writes,
       qs.last_logical_writes,
       qs.total_worker_time,
       qs.last_worker_time,
       qs.total_elapsed_time / 1000000 total_elapsed_time_in_S,
       qs.last_elapsed_time / 1000000 last_elapsed_time_in_S,
       qs.last_execution_time,
       qp.query_plan
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC; -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Good luck to everyone in business and life.