Investigate CPU Usage In SQL Server by Satya Ramesh

This article first appeared in the SQLServerGeeks Magazine.
Author: Satya Ramesh
Subscribe to get your copy.

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.

This article first appeared in the SQLServerGeeks Magazine.
Author: Satya Ramesh
Subscribe to get your copy.