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
- Propriétaire (SA)
- Réutilisation et utilisation du journal
- Niveau de compatibilité
- Paramètres des statistiques
- Niveau d'isolation
- Taille et espace utilisé
Remarques
- Le cache des plans ne contient que les plans encore en mémoire. Une requête évincée n'apparaît pas dans ces résultats.
- Le ring buffer de l'historique CPU conserve un nombre limité d'échantillons. Pour le suivi du CPU, voir l'article Microsoft Monitor CPU usage on SQL Server and Azure SQL.
- Les scripts de supervision tiers plus longs (alertes par seuil, CPU par base) ne sont pas repris dans cette synthèse.