SQL Server - Top CPU Consuming Queries

The query mention below will list down top 10 top resource (CPU) consuming queries. This can be a good tool for a SQL Server database professional to get to know how is your database doing and what are the queries which are taking maximum CPU of the server.

Also, you can get to know if any query is getting recompiled using the columns “execution_count” and “plan_generation_num”.

I use this query to find the database queries which require performance optimization and hoping this will help to others as well.

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)

AS sql_text,

      qs.execution_count,

      qs.plan_generation_num,

      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_worker_time DESC

Query Result