SQL Server Top 10 Most CPU Consuming Queries

The query will generate the result as given:

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

This is a very important query for DBAs to know about the queries which are most CPU consuming, also the same query will let you know which are the queries have been recompiled using the columns “execution_count” and “plan_generation_num”. There are more into the result which can be a good tool for a DBA to investigate the query/database/server performance issue.