Calculating CPU percentage for active queries

The ability to measure CPU percentage for each active query can be useful to identify an issue, or set up alerting for abnormal usage.

Trying to identify the top queries consuming high CPU within SQL is not as simple as viewing a DMV; every active query can be viewed in the dynamic management view sys.dm_exec_requests. This view records the total elapsed time (total_elapsed_time) and the total CPU time (cpu_time).

As the name infers, the total elapsed time is the overall duration of the query. The column cpu_time may show a value larger than the overall duration, this is because a query may use more than one thread, this is called parallelism.

For an example, the below table shows 4 queries running against a SQL Server with 8 cores.

With 8 processors available, each millisecond has 8 CPU milliseconds available. If a query runs for 400ms and uses 320ms of cpu_time, overall, it uses 10% of the available CPU (3200ms).

The document Troubleshoot high-CPU-usage issues in SQL Server (under Step 2) shows how to identify if the CPU percentage is high and how to identify the top 10 queries by CPU time. As mentioned above, the cpu_time value is no indication of high CPU usage.

These queries can be adapted to provide an average CPU percentage based on the overall duration.

The below query consists of two sections. Section 1 stores the count of active schedulers available. Section 2 then compares the cpu_time based on the available cpu_time (total_elasped_time * count of schedulers) to calculate the CPU percentage.

--SECTION 1 - Count of available schedulers

DECLARE @CPU_Count INT

SELECT @CPU_Count = COUNT(*)

FROM sys.dm_os_schedulers

WHERE status = 'VISIBLE ONLINE'

--SECTION 2 Top 10 Queries running by CPU Percentage

SELECT TOP 10

s.session_id,

r.status,

r.cpu_time,

r.logical_reads,

r.reads,

r.writes,

r.total_elapsed_time / (1000 * 60) 'Elaps M',

100.0 / (r.total_elapsed_time * @CPU_Count) * r.cpu_time Average_CPU_Percentage,

SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,

((CASE r.statement_end_offset

WHEN -1 THEN DATALENGTH(st.TEXT)

ELSE r.statement_end_offset

END - r.statement_start_offset) / 2) + 1) AS statement_text,

COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))

+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,

r.command,

s.login_name,

s.host_name,

s.program_name,

s.last_request_end_time,

s.login_time,

r.open_transaction_count

FROM sys.dm_exec_sessions AS s

JOIN sys.dm_exec_requests AS r

ON r.session_id = s.session_id

CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

WHERE r.session_id != @@SPID

ORDER BY Average_CPU_Percentage DESC

Example results below.

Rather than getting an average CPU percentage, a more accurate CPU percentage for each query can be provided by capturing the current active queries, waiting for a set time and then capturing the active queries again, comparing the cpu_time difference to the available cpu_time.

The below query is similar to the above example, but CPU time will be captured for each query into a temporary table, then, after a defined delay, the CPU time will be captured again and used to calculate CPU over the duration it ran for. This example requires the query to be running the whole time, so reducing the delay is more likely to provide more results.

--Section 1 - Count of available schedulers

DECLARE @CPU_Count INT

SELECT @CPU_Count = COUNT(*)

FROM sys.dm_os_schedulers

WHERE status = 'VISIBLE ONLINE'

--Section 2 - Define Duration in seconds

DECLARE

@Delay INT,

@DelayMS INT

SET @Delay = 5--5

SET @DelayMS = @Delay * 1000

--Section 3 - Capture active queries and current cpu_time now

SELECT

session_id,

start_time,

cpu_time

INTO #RunningQueries

FROM sys.dm_exec_requests

--Section 4 - Wait For 5 Seconds

WAITFOR DELAY @Delay

--Section 5 - Compare against active queries now

SELECT TOP 10

s.session_id,

r.status,

r.cpu_time,

r.logical_reads,

r.reads,

r.writes,

r.total_elapsed_time / (1000 * 60) 'Elaps M',

CONVERT(DECIMAL(5,2), (100.0 / (@DelayMS * @CPU_Count)) * (r.cpu_time - rq.cpu_time)) CPU_Percentage,

SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,

((CASE r.statement_end_offset

WHEN -1 THEN DATALENGTH(st.TEXT)

ELSE r.statement_end_offset

END - r.statement_start_offset) / 2) + 1) AS statement_text,

COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))

+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,

r.command,

s.login_name,

s.host_name,

s.program_name,

s.last_request_end_time,

s.login_time,

r.open_transaction_count

FROM sys.dm_exec_sessions AS s

JOIN sys.dm_exec_requests AS r

ON r.session_id = s.session_id

CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

JOIN #RunningQueries rq

ON r.session_id = rq.session_id

AND r.start_time = rq.start_time

WHERE r.session_id != @@SPID

ORDER BY CPU_Percentage DESC

--Section 6 - Drop Temporary Table

DROP TABLE #RunningQueries

Example results below.

This can also be used as a stored procedure to run either scenario depending on the variable values provided.

CREATE PROCEDURE pCaptureQueryCPUPercent

@Mode BIT, --0 = AVG CPU, 1 = Current CPU

@DelayDuration INT = 5 --Default 5 seconds

AS

/*

Procedure pCaptureQueryCPUPercent

Examples:-

--Capture average CPU percentage since each query began

EXECUTE pCaptureQueryCPUPercent 0

--Capture average CPU percentage over 2 seconds.

EXECUTE pCaptureQueryCPUPercent 1, 2

*/

SET NOCOUNT ON

--Count of available schedulers

DECLARE @CPU_Count INT

SELECT @CPU_Count = COUNT(*)

FROM sys.dm_os_schedulers

WHERE status = 'VISIBLE ONLINE'

IF @Mode = 0

BEGIN

--SECTION 2 Top 10 Queries running by CPU Percentage

SELECT TOP 10

s.session_id,

r.status,

r.cpu_time,

r.logical_reads,

r.reads,

r.writes,

r.total_elapsed_time / (1000 * 60) 'Elaps M',

100.0 / (r.total_elapsed_time * @CPU_Count) * r.cpu_time Average_CPU_Percentage,

SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,

((CASE r.statement_end_offset

WHEN -1 THEN DATALENGTH(st.TEXT)

ELSE r.statement_end_offset

END - r.statement_start_offset) / 2) + 1) AS statement_text,

COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))

+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,

r.command,

s.login_name,

s.host_name,

s.program_name,

s.last_request_end_time,

s.login_time,

r.open_transaction_count

FROM sys.dm_exec_sessions AS s

JOIN sys.dm_exec_requests AS r

ON r.session_id = s.session_id

CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

WHERE r.session_id != @@SPID

ORDER BY Average_CPU_Percentage DESC

END

IF @Mode = 1 --Current CPU

BEGIN

--Define Duration in seconds

DECLARE

@DelayDurationMS INT

SET @DelayDurationMS = @DelayDuration * 1000

--Capture active queries and current cpu_time now

SELECT

session_id,

start_time,

cpu_time

INTO #RunningQueries

FROM sys.dm_exec_requests

--Wait For 5 Seconds

WAITFOR DELAY @DelayDuration

--Compare against active queries now

SELECT TOP 10

s.session_id,

r.status,

r.cpu_time,

r.logical_reads,

r.reads,

r.writes,

r.total_elapsed_time / (1000 * 60) 'Elaps M',

CONVERT(DECIMAL(5,2), (100.0 / (@DelayDurationMS * @CPU_Count)) * (r.cpu_time - rq.cpu_time)) CPU_Percentage,

SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,

((CASE r.statement_end_offset

WHEN -1 THEN DATALENGTH(st.TEXT)

ELSE r.statement_end_offset

END - r.statement_start_offset) / 2) + 1) AS statement_text,

COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))

+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,

r.command,

s.login_name,

s.host_name,

s.program_name,

s.last_request_end_time,

s.login_time,

r.open_transaction_count

FROM sys.dm_exec_sessions AS s

JOIN sys.dm_exec_requests AS r

ON r.session_id = s.session_id

CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st

JOIN #RunningQueries rq

ON r.session_id = rq.session_id

AND r.start_time = rq.start_time

WHERE r.session_id != @@SPID

ORDER BY CPU_Percentage DESC

--Drop Temporary Table

DROP TABLE #RunningQueries

END

I hope this article was helpful for you, please like it on this page and share through social media. please feel free to share your feedback in the comments section below.

Continue reading...