Identify Top 10 CPU-Consuming Queries in SQL Server

To monitor performance and identify resource-heavy operations, it is often necessary to find queries that consume the most CPU. SQL Server provides a useful Dynamic Management View (DMV) called sys.dm_exec_query_stats, which contains performance-related information for queries currently in the cache, including CPU, memory, and I/O usage.

By joining this DMV with sys.dm_exec_query_plan and sys.dm_exec_sql_text, we can retrieve not only the query statistics but also the corresponding execution plan and SQL text. This allows us to analyze and troubleshoot expensive queries effectively.

Below is a script that returns the Top 10 CPU-intensive queries currently in the cache:

1. SELECT TOP 10   
2.     qs.total_worker_time AS TotalCPU,  
3.     qs.execution_count AS ExecutionCount,  
4.     qs.total_worker_time / qs.execution_count AS AvgCPUPerExecution,  
5.     qs.total_elapsed_time / qs.execution_count AS AvgElapsedTime,  
6.     qs.total_elapsed_time AS TotalElapsedTime,  
7.     qt.text AS QueryText,  
8.     qp.query_plan AS QueryPlan  
9. FROM   
10.     sys.dm_exec_query_stats AS qs  
11. CROSS APPLY   
12.     sys.dm_exec_sql_text(qs.sql_handle) AS qt  
13. CROSS APPLY   
14.     sys.dm_exec_query_plan(qs.plan_handle) AS qp  
15. ORDER BY   
16.     qs.total_worker_time DESC;  

Conclusion:

This script is useful for DBAs and developers who want to quickly identify and optimize the most CPU-intensive queries in SQL Server. Regular use of this report can help improve database performance and reduce system load.