In this article, we will see a few scripts that help us to find CPU usage using DMVs & DMFs. These will be quite handy in day-to-day DBA query tuning efforts.
SQL Server Dynamic Management Views (DMVs) & Dynamic Management Functions (DMFs) are used to investigate the health of SQL Server engine. The metrics & data items produced by them are very useful in analyzing and fixing performance problems.
Query Level CPU Usage
Often, we want to find out queries that are consuming a lot of CPU, IO & Memory. You can do that using sys.dm_exec_query_stats DMV. The below script uses sys.dm_exec_query_plan DMV along with sys.dm_exec_sql_text & sys.dm_exec_query_plan DMFs to get the actual culprit queries and their respective execution plans.
SELECT TOP 10 est.[text], eqp.query_plan AS SQLStatement, [execution_count] ,[total_worker_time]/1000 AS [TotalCPUTime_ms] ,[total_elapsed_time]/1000 AS [TotalDuration_ms] ,query_hash ,plan_handle ,[sql_handle] FROM sys.dm_exec_query_stats eqs CROSS APPLY sys.dm_exec_query_plan(eqs.plan_handle) eqp CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS est ORDER BY [TotalCPUTime_ms] DESC
In the above query, if we change the column in the ORDER BY clause to total_logical_reads column then we can find out TOP 10 I/O intensive queries (from a read perspective). If we do a sort on the total_grant_kb column, then we can find out top queries that are asking for extra memory grant. If we do a sort on the [TotalDuration_ms] column, then we can extract long-running queries.
Request Level CPU Usage
We can also extract CPU cycles information of each request that is executing in SQL Server by using sys.dm_exec_requests DMV. In this DMV, we need to look at the cpu_time column, which tells us the CPU consumption of each request sent to the engine. We can do a CROSS APPLY with sys.dm_exec_query_plan & sys.dm_exec_sql_text DMFs to get the query plan and the query text. We can also exclude all the background tasks. The DMV gives a whole lot of other information like wait types, wait time, etc. Below is the query and a screenshot of the results.
SELECT session_id, wait_type, wait_time, cpu_time, eqp.query_plan, est.[text] FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) eqp CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS est WHERE session_id > 54 AND [status] NOT LIKE 'background' ORDER BY cpu_time DESC
Procedure/Function/Trigger Level CPU Usage
Similar to the query stats DMV (which gives us query level info), we can also find out CPU consumption of each stored procedure, trigger and user-defined functions by using sys.dm_exec_procedure_stats, sys.dm_exec_trigger_stats and sys.dm_exec_function_stats DMVs, respectively. All these DMVs have common columns that gives us CPU information: total_worker_time, last_worker_time, min_worker_time & max_worker_time.
We can CROSS APPLY with sys.dm_exec_query_plan & sys.dm_exec_sql_text DMFs to extract the query text and the query plan.
Here is one example using procedure stats.
SELECT total_worker_time, min_worker_time, max_worker_time, last_worker_time, eqp.query_plan, est.[text] FROM sys.dm_exec_procedure_stats ps CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) eqp CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) AS est
This was just a level 100 introduction to extract workloads causing excessive CPU usage. What are your techniques? Post/participate in the discussion: LinkedIn, Twitter, FB.