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.
|
Query |
CPU Time (ms) |
Available CPU Time (ms) |
CPU Usage (%) |
|
Query 1 |
600 |
800 |
75% |
|
Query 2 |
200 |
1600 |
20% |
|
Query 3 |
1200 |
2400 |
50% |
|
Query 4 |
320 |
3200 |
10% |
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.