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.

30 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!