SQL Server lent : diagnostiquer blocages, attentes et requêtes coûteuses

Commencer par le symptôme, pas par un réglage

« SQL Server est lent » peut désigner une requête précise, toute une application, une période de blocage ou une saturation de l'hôte. Avant de modifier un index ou une configuration, notez l'heure, la durée, la base, l'action utilisateur, la fréquence, le dernier changement et si les autres applications sont touchées.

Ce guide sert au dépannage ciblé d'un incident de performance. Il complète l'audit de santé SQL Server, qui couvre aussi sauvegardes, intégrité, capacité, SQL Agent, sécurité et haute disponibilité. Les requêtes ci-dessous lisent l'état du moteur; elles ne corrigent rien automatiquement.

• Microsoft — Dépanner une instance SQL Server lente ↗

Collecte légère ou capture approfondie?

Les DMV ponctuelles sont rapides et utiles pendant l'incident, mais une photo après le retour à la normale peut ne rien montrer. Query Store conserve un historique par base. Extended Events convient aux événements intermittents. Un plan d'exécution réel, une trace trop large ou une collecte très fréquente peuvent ajouter une charge mesurable : pilotez-les et limitez durée, filtres et rétention.

1. Capturer les requêtes actives et leurs attentes

Exécutez cette collecte pendant la lenteur. wait_type indique ce que la requête attend maintenant; blocking_session_id pointe vers une session qui la retient. CPU, lectures et temps écoulé doivent être comparés à la même unité de travail, pas à un seuil universel.

Une session en attente n'est pas nécessairement le problème. L'objectif est de relier l'attente au texte, à la base, à l'application et à la chaîne de blocage.

SELECT r.session_id, s.login_name, s.host_name, s.program_name,
       DB_NAME(r.database_id) AS database_name,
       r.status, r.command, r.wait_type, r.wait_time,
       r.blocking_session_id, r.cpu_time, r.total_elapsed_time,
       r.logical_reads, r.reads, r.writes,
       txt.text AS batch_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE r.session_id <> @@SPID
  AND s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;

• Microsoft — sys.dm_exec_requests ↗

2. Trouver le bloqueur principal, pas seulement les victimes

Le blocage bref est normal dans un moteur transactionnel. Il devient un incident lorsque la durée et l'impact dépassent ce que l'application tolère. Une session peut bloquer sans requête actuellement active, par exemple si elle conserve une transaction ouverte; c'est pourquoi dm_exec_sessions et dm_exec_connections complètent dm_exec_requests.

Ne terminez pas une session uniquement parce qu'elle apparaît comme bloqueur. Confirmez le propriétaire, la transaction, la possibilité de rollback, l'impact métier et la durée estimée d'annulation. KILL peut prolonger l'indisponibilité si une grande transaction doit être annulée.

SELECT blocked.session_id AS blocked_session_id,
       blocked.blocking_session_id AS blocker_session_id,
       blocked.wait_type, blocked.wait_time, blocked.wait_resource,
       DB_NAME(blocked.database_id) AS database_name,
       blocked_text.text AS blocked_batch,
       blocker.login_name AS blocker_login,
       blocker.host_name AS blocker_host,
       blocker.program_name AS blocker_program,
       blocker.open_transaction_count,
       blocker_text.text AS blocker_last_batch
FROM sys.dm_exec_requests AS blocked
JOIN sys.dm_exec_sessions AS blocker
  ON blocker.session_id = blocked.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(blocked.sql_handle) AS blocked_text
LEFT JOIN sys.dm_exec_connections AS bc
  ON bc.session_id = blocker.session_id
OUTER APPLY sys.dm_exec_sql_text(bc.most_recent_sql_handle) AS blocker_text
WHERE blocked.blocking_session_id > 0
ORDER BY blocked.wait_time DESC;

• Microsoft — Comprendre et résoudre les blocages SQL Server ↗

3. Lire les attentes comme une direction, pas un verdict

sys.dm_os_wait_stats agrège les attentes de tous les threads. Ses compteurs repartent au démarrage du moteur ou après une remise à zéro explicite. Un total depuis plusieurs semaines ne décrit pas forcément l'incident de ce matin; capturez deux photos et calculez le delta sur une période représentative.

Une attente élevée n'impose pas une correction unique. PAGEIOLATCH peut refléter des lectures physiques causées par un plan inefficace autant qu'un stockage lent; WRITELOG peut venir de la latence du journal, de transactions très fréquentes ou d'une topologie de disponibilité. CXPACKET et CXCONSUMER exigent de regarder plans, durée, CPU et distribution du travail avant de modifier MAXDOP.

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #waits_before
FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:00:10';

SELECT TOP (20) a.wait_type,
       a.waiting_tasks_count - b.waiting_tasks_count AS tasks_delta,
       a.wait_time_ms - b.wait_time_ms AS wait_ms_delta,
       a.signal_wait_time_ms - b.signal_wait_time_ms AS signal_ms_delta
FROM sys.dm_os_wait_stats AS a
JOIN #waits_before AS b ON b.wait_type = a.wait_type
WHERE a.wait_time_ms > b.wait_time_ms
ORDER BY wait_ms_delta DESC;

DROP TABLE #waits_before;

• Microsoft — sys.dm_os_wait_stats ↗

4. Corréler les attentes d'E/S aux fichiers

Les compteurs de dm_io_virtual_file_stats sont cumulatifs. Les moyennes ci-dessous peuvent masquer des pointes; elles servent à comparer fichiers, types de charge et périodes comparables. Capturez également un delta pendant l'incident et corrélez-le aux métriques du système d'exploitation ou de la plateforme de stockage.

Une latence élevée sur un fichier de données ne prouve pas que le stockage est la cause première. Une analyse complète inutile, un index absent ou un plan régressé peut générer le volume d'E/S. Pour le journal, examinez aussi la taille des transactions, les commits, la croissance et les réplicas.

SELECT DB_NAME(vfs.database_id) AS database_name,
       mf.type_desc, mf.physical_name,
       vfs.num_of_reads, vfs.num_of_writes,
       CAST(1.0 * vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0)
         AS decimal(18,2)) AS avg_read_ms,
       CAST(1.0 * vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0)
         AS decimal(18,2)) AS avg_write_ms,
       vfs.sample_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY avg_read_ms DESC;

• Microsoft — sys.dm_io_virtual_file_stats ↗

5. Classer les requêtes coûteuses encore en cache

dm_exec_query_stats permet de trier le travail cumulé par CPU, durée et lectures. Regardez les totaux et les moyennes : une requête rare et lourde n'a pas la même priorité qu'une petite requête exécutée des millions de fois.

Les lignes n'existent que tant que le plan demeure dans le cache. Un redémarrage, une recompilation, une éviction ou un vidage de cache change la période observée. Ne videz pas le cache pour « réparer » une lenteur : vous perdez la preuve et forcez des recompilations.

SELECT TOP (25) DB_NAME(txt.dbid) AS database_name,
       qs.execution_count, qs.creation_time, qs.last_execution_time,
       qs.total_worker_time / 1000.0 AS total_cpu_ms,
       qs.total_worker_time / NULLIF(qs.execution_count,0) / 1000.0 AS avg_cpu_ms,
       qs.total_elapsed_time / NULLIF(qs.execution_count,0) / 1000.0 AS avg_duration_ms,
       qs.total_logical_reads,
       qs.total_logical_reads / NULLIF(qs.execution_count,0) AS avg_logical_reads,
       SUBSTRING(txt.text,
         (qs.statement_start_offset / 2) + 1,
         ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(txt.text)
           ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1
       ) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY qs.total_worker_time DESC;

• Microsoft — sys.dm_exec_query_stats ↗

6. Utiliser Query Store pour comparer avant et après

Query Store conserve le texte, les plans et les statistiques par intervalle dans chaque base où il est actif. SQL Server 2022 l'active par défaut pour les nouvelles bases, mais une base mise à niveau ou restaurée peut avoir un autre état. Vérifiez actual_state_desc, desired_state_desc, capture mode, taille et rétention avant de conclure qu'il n'y a pas d'historique.

La requête suivante pondère les moyennes par le nombre d'exécutions. Elle utilise l'heure UTC et doit être lancée dans la base concernée. Ajustez la fenêtre à l'incident; un intervalle Query Store peut contenir plusieurs exécutions et plusieurs plans.

SELECT actual_state_desc, desired_state_desc, readonly_reason,
       current_storage_size_mb, max_storage_size_mb,
       query_capture_mode_desc, stale_query_threshold_days
FROM sys.database_query_store_options;

SELECT TOP (25) q.query_id, p.plan_id,
       SUM(rs.count_executions) AS executions,
       CAST(SUM(rs.avg_duration * rs.count_executions) /
         NULLIF(SUM(rs.count_executions),0) / 1000.0 AS decimal(18,2)) AS avg_duration_ms,
       CAST(SUM(rs.avg_cpu_time * rs.count_executions) /
         NULLIF(SUM(rs.count_executions),0) / 1000.0 AS decimal(18,2)) AS avg_cpu_ms,
       CAST(SUM(rs.avg_logical_io_reads * rs.count_executions) /
         NULLIF(SUM(rs.count_executions),0) AS decimal(18,2)) AS avg_logical_reads,
       qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
  ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.end_time > DATEADD(hour, -2, SYSUTCDATETIME())
GROUP BY q.query_id, p.plan_id, qt.query_sql_text
ORDER BY avg_duration_ms DESC;

• Microsoft — Surveiller les performances avec Query Store ↗
• Microsoft — Bonnes pratiques Query Store ↗

7. Distinguer blocage et deadlock

Un blocage attend qu'une ressource soit libérée. Un deadlock forme un cycle que SQL Server résout en choisissant une victime et en retournant l'erreur 1205. Chercher seulement les sessions actives après l'erreur ne suffit donc pas.

Depuis SQL Server 2012, Microsoft recommande l'événement xml_deadlock_report plutôt que SQL Trace ou Profiler. La session Extended Events system_health le capture par défaut. Dans SSMS, ouvrez Management > Extended Events > Sessions > system_health, affichez les données de la cible et filtrez xml_deadlock_report. Conservez le graphe, les objets, l'ordre des verrous, les plans et le code applicatif; un retry peut masquer le symptôme sans corriger le cycle.

• Microsoft — Guide des deadlocks SQL Server ↗
• Microsoft — Session Extended Events system_health ↗

8. Lire un plan sans sauter aux conclusions

Forcer un plan Query Store peut stabiliser rapidement une régression, mais c'est un changement de production. Testez-le, documentez le query_id et le plan_id, surveillez les échecs et prévoyez sp_query_store_unforce_plan comme retour arrière. Une stabilisation temporaire ne dispense pas de corriger la cause.

• Comparer lignes estimées et réelles aux opérateurs clés; un grand écart peut signaler statistiques, corrélation ou paramètres sensibles.
• Repérer scans, lookups, sorts, spills, conversions implicites, avertissements et mémoire accordée, puis les relier au volume réel.
• Comparer plusieurs plans Query Store pour la même query_id et l'heure du changement.
• Vérifier valeurs de paramètres, variabilité des cardinalités et niveau de compatibilité avant de conclure à un parameter sniffing.
• SQL Server 2022 peut utiliser Parameter Sensitive Plan optimization dans les conditions prises en charge; cela ne remplace pas l'analyse du modèle de données.
• Traiter une suggestion d'index manquant comme une hypothèse : vérifier index existants, coût d'écriture, espace et chevauchements.

• Microsoft — Parameter Sensitive Plan optimization ↗

9. Permissions minimales et différences de version

Utilisez une identité de diagnostic dédiée et accordez seulement la visibilité nécessaire. Les permissions d'état peuvent révéler du texte SQL, des noms d'objets et parfois des valeurs sensibles. Un export de diagnostic doit avoir un propriétaire, un emplacement protégé et une rétention courte.

• Microsoft — Vues et fonctions de gestion dynamique ↗

Les raccourcis qui aggravent souvent l'incident

Plan d'intervention reproductible

• T0 — noter symptôme, utilisateurs, heure, base, requête et changement récent.
• T+5 — capturer requêtes actives, chaîne de blocage, attentes et métriques hôte sans modifier l'état.
• T+15 — comparer Query Store avant/pendant l'incident; récupérer deadlocks et événements pertinents.
• Triage — décider si la priorité est disponibilité, contention, plan, capacité ou dépendance externe.
• Changement — une correction à la fois, test représentatif, critères de succès et retour arrière.
• Après — mesurer durée, CPU, lectures, erreurs et concurrence; conserver la preuve avant/après.
• Prévention — automatiser une collecte légère et planifier l'analyse de fond hors incident.

Passer du symptôme à une correction durable

Un dépannage fiable relie une expérience utilisateur à une chronologie, une requête, un plan, une ressource et une cause. Il évite les seuils universels et les corrections de masse. Si le problème est critique, préservez d'abord les preuves et la capacité de retour arrière.

Un audit de santé élargit ensuite la vue aux sauvegardes, à l'intégrité, aux jobs, à la capacité et à la configuration. Cette séparation empêche qu'une urgence de performance fasse oublier un risque de restauration ou de sécurité plus important.

• Article — Audit de santé SQL Server : T-SQL et PowerShell →
• Service — Architecture et modernisation d'infrastructure →