LLI Wiki — My Digital Knowledge Base

Diagnostiquer CPU et requêtes lentes avec les DMV

Requêtes sur les vues de gestion dynamique pour trouver la consommation CPU, les requêtes les plus coûteuses, les sessions actives et les statuts d'opérations Azure SQL.

Un ensemble de requêtes sur les vues de gestion dynamique (DMV) pour trouver ce qui consomme le CPU, les requêtes en cours et les requêtes qui font le plus de travail. La plupart lisent le cache des plans : elles ne montrent que les plans encore en cache.

Statut des opérations Azure SQL

Liste les opérations de gestion récentes de la base, avec leur état.

SELECT *
FROM sys.dm_operation_status
ORDER BY start_time DESC;

Requêtes en cours et leurs 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;

Utilisation CPU des dernières minutes

Le ring buffer du moniteur de planificateur enregistre, environ toutes les minutes, la part du CPU utilisée par SQL Server, le temps d'inactivité du système et celle des autres processus.

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;

Requêtes les plus coûteuses en CPU, E/S et exécutions

-- Top 10 par temps CPU total
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 par temps CPU moyen
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 par E/S logiques
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;

-- Nombre d'exécutions et de lignes de chaque requête en cache
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;

Procédures stockées les plus coûteuses en 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 et leurs requêtes

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;

Propriétés de base Azure SQL à vérifier

Remarques

Mastodon LLIMastodon SocialFosstodon