LLI Wiki — My Digital Knowledge Base

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

Notes

Mastodon LLIMastodon SocialFosstodon