Troubleshoot CPU and slow queries with DMVs
DMV queries to find CPU usage, the most expensive queries, active sessions and Azure SQL operation status.
A set of queries on dynamic management views (DMVs) to find what uses the CPU, which requests are running and which queries do the most work. Most of them read the plan cache, so they show only the plans that are still cached.
Azure SQL operation status
Lists the recent management operations on the database, with their state.
SELECT *
FROM sys.dm_operation_status
ORDER BY start_time DESC;
Current requests and their plans
SELECT
r.session_id,
st.TEXT AS batch_text,
SUBSTRING(st.TEXT, r.statement_start_offset / 2 + 1, (
(
CASE
WHEN r.statement_end_offset = -1
THEN (LEN(CONVERT(NVARCHAR(MAX), st.TEXT)) * 2)
ELSE r.statement_end_offset
END
) - r.statement_start_offset
) / 2 + 1) AS statement_text,
qp.query_plan AS 'XML Plan',
r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC;
CPU usage of the last minutes
The scheduler monitor ring buffer records the CPU share of SQL Server, of the system idle time and of the other processes, about once per minute.
DECLARE @ms_ticks_now BIGINT;
SELECT @ms_ticks_now = ms_ticks
FROM sys.dm_os_sys_info;
SELECT TOP 15
record_id,
DATEADD(ms, -1 * (@ms_ticks_now - [timestamp]), GETDATE()) AS EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
FROM (
SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
[timestamp]
FROM (
SELECT [timestamp], CONVERT(XML, record) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
) AS x
) AS y
ORDER BY record_id DESC;
Top queries by CPU, I/O and executions
-- Top 10 by total CPU time
SELECT TOP 10
QT.TEXT AS STATEMENT_TEXT,
QP.QUERY_PLAN,
QS.TOTAL_WORKER_TIME AS CPU_TIME
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.SQL_HANDLE) AS QT
CROSS APPLY sys.dm_exec_query_plan(QS.PLAN_HANDLE) AS QP
ORDER BY TOTAL_WORKER_TIME DESC;
-- Top 10 by average CPU time
SELECT TOP 10
TOTAL_WORKER_TIME,
EXECUTION_COUNT,
TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME],
QT.TEXT AS QUERYTEXT
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.PLAN_HANDLE) AS QT
ORDER BY QS.TOTAL_WORKER_TIME DESC;
-- Top 10 by logical I/O
SELECT TOP 10
TOTAL_LOGICAL_READS,
TOTAL_LOGICAL_WRITES,
EXECUTION_COUNT,
TOTAL_LOGICAL_READS + TOTAL_LOGICAL_WRITES AS [IO_TOTAL],
QT.TEXT AS QUERY_TEXT,
DB_NAME(QT.DBID) AS DATABASE_NAME,
QT.OBJECTID AS OBJECT_ID
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(SQL_HANDLE) QT
WHERE TOTAL_LOGICAL_READS + TOTAL_LOGICAL_WRITES > 0
ORDER BY [IO_TOTAL] DESC;
-- Execution count and row counts of each cached query
SELECT
QS.EXECUTION_COUNT,
QT.TEXT AS QUERY_TEXT,
QT.DBID,
DBNAME = DB_NAME(QT.DBID),
QT.OBJECTID,
QS.TOTAL_ROWS,
QS.LAST_ROWS,
QS.MIN_ROWS,
QS.MAX_ROWS
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.SQL_HANDLE) AS QT
ORDER BY QS.EXECUTION_COUNT DESC;
Top stored procedures by CPU
SELECT TOP (20)
st.TEXT AS Query,
qs.execution_count,
qs.total_worker_time AS Total_CPU,
qs.total_worker_time / 1000000 AS total_CPU_inSeconds,
(qs.total_worker_time / 1000000) / qs.execution_count AS average_CPU_inSeconds,
qs.total_elapsed_time,
qs.total_elapsed_time / 1000000 AS total_elapsed_time_inSeconds,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);
Sessions and their requests
SELECT
s.session_id AS SPID,
s.status AS SessionStatus,
r.status AS RequestStatus,
r.command,
s.login_name,
s.host_name,
s.program_name,
r.blocking_session_id AS BlkBy,
r.wait_type,
r.wait_time,
r.wait_resource,
r.cpu_time,
r.total_elapsed_time AS Elapsed_ms,
s.last_request_start_time,
s.last_request_end_time,
txt.text AS SQL_Text
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(
COALESCE(r.sql_handle, c.most_recent_sql_handle)
) txt
WHERE s.is_user_process = 1
ORDER BY
CASE WHEN r.session_id IS NULL THEN 1 ELSE 0 END,
r.total_elapsed_time DESC;
Azure SQL database properties to check
- Owner (SA)
- Log reuse and log used
- Compatibility level
- Statistics settings
- Isolation level
- Size and space used
Notes
- The plan cache only holds plans that are still in memory. A query that has been evicted does not appear in these results.
- The CPU history ring buffer keeps a limited number of samples. For CPU monitoring, see the Microsoft article Monitor CPU usage on SQL Server and Azure SQL.
- Longer third-party monitoring scripts (threshold alerts, CPU per database) were left out of this summary.