DevOps et observabilité

Superviser les charges SQL Server en temps réel

Examinez les requêtes, sessions et connexions SQL Server actives, puis reliez l’état actuel à l’historique conservé dans Query Store.

Par JaviPublié Mis à jour 13 min de lectureIntermédiaire

Supervision de l’état actuel de SQL Server reliée aux éléments historiques

Réponse courte : la supervision en temps réel répond à « que fait SQL Server maintenant, et où dois-je regarder ensuite ? ». Prenez le pouls de la charge en un coup d’œil, déterminez ce qui domine (blocage, CPU, E/S ou attentes) et passez au guide plus ciblé. Dès que la question porte sur le passé, quittez les DMV en direct pour Query Store, les Extended Events ou votre historique de supervision : une vue instantanée ne peut pas décrire ce qui est déjà terminé.

Cette étape appartient à la démarche d’optimisation des performances SQL Server. Pour une requête d’incident ciblée, consultez les requêtes en cours.

État actuel : requêtes, sessions et connexions

Trois vues de gestion dynamique décrivent des parties différentes de la charge active :

  • sys.dm_exec_requests contient les requêtes en cours d’exécution ou d’attente, avec CPU, durée, lectures, écritures, attentes et blocages.
  • sys.dm_exec_sessions identifie la session connectée, le login, l’hôte, le programme et les processus utilisateur.
  • sys.dm_exec_connections ajoute le contexte de connexion, notamment l’adresse cliente et le dernier handle SQL.

Une session peut exister sans requête active. Observer seulement sys.dm_exec_sessions peut faire passer des connexions dormantes pour du travail actif ; observer seulement sys.dm_exec_requests peut manquer une session dormante qui possède encore une transaction ouverte.

La boucle de supervision

  1. ObserverPouls de la charge, requêtes actives, attentes en cours
  2. ClasserQu’est-ce qui domine maintenant ?
    • attentes de verrou
      BlocageDiagnostiquer les blocages, puis le runbook
    • CPU élevé
      CPU ou une requêteRequêtes en cours, plan, Query Store
    • attentes E/S
      E/SLectures par requête, latence du stockage
    • attentes
      Autres attentesÉcarts d’attente, allocations mémoire
    • déjà terminé
      Régression passéeQuery Store, Extended Events
  3. Collecter les élémentsÉchantillons horodatés, ID, plans
  4. ValiderMême charge, mesurée de nouveau
La boucle de supervision en temps réel. Observez toute la charge, déterminez ce qui domine, approfondissez avec le guide adapté, puis collectez les éléments et validez avant de changer quoi que ce soit.

Le pouls de la charge en un coup d’œil

Avant de lire les requêtes une par une, obtenez la forme de la charge. Ces requêtes en lecture seule résument les requêtes utilisateur actives au lieu de les lister :

Requête SQL: Le pouls de la charge en un coup d’œilT-SQL
-- 1. One-row summary of the current user workload
SELECT
    SYSDATETIMEOFFSET()                                          AS sample_time,
    COUNT(*)                                                     AS active_requests,
    SUM(CASE WHEN r.blocking_session_id > 0 THEN 1 ELSE 0 END)   AS blocked_requests,
    SUM(CASE WHEN r.status = 'running'   THEN 1 ELSE 0 END)      AS running,
    SUM(CASE WHEN r.status = 'runnable'  THEN 1 ELSE 0 END)      AS runnable,
    SUM(CASE WHEN r.status = 'suspended' THEN 1 ELSE 0 END)      AS suspended,
    MAX(r.total_elapsed_time) / 1000                             AS longest_request_seconds
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID;

-- 2. What active requests are waiting on at this instant
SELECT r.wait_type, COUNT(*) AS requests, MAX(r.wait_time) AS max_wait_ms
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
  AND r.wait_type IS NOT NULL
GROUP BY r.wait_type
ORDER BY requests DESC, max_wait_ms DESC;
T-SQL23 lignesAffichage en lecture seule. Aucune connexion à une base de données.

Lisez le premier résultat en proportions. Beaucoup de requêtes runnable signifient que du travail attend le CPU. Beaucoup de requêtes suspended signifient que du travail attend autre chose, et la seconde requête dit quoi. Un blocked_requests non nul oriente vers un blocage. Pour le détail par requête (instruction, CPU, lectures, attentes et origine), utilisez Voir les requêtes en cours, la requête de référence du site sur l’activité en cours.

Orienter selon la question

Qu’est-ce qui s’exécute maintenant ?

Le pouls donne la forme ; Voir les requêtes en cours donne chaque requête active avec son coût, son attente, son origine et son instruction.

Le travail est-il bloqué ?

Un blocked_requests non nul, ou des LCK_M_* parmi les attentes en cours, signifie que des requêtes attendent des verrous. Remontez la chaîne jusqu’au bloqueur principal avec Diagnostiquer les blocages. Si les utilisateurs sont touchés maintenant, suivez le runbook de blocage SQL Server (en anglais).

Qu’attend SQL Server ?

Les attentes des requêtes en cours décrivent cet instant. Les attentes à l’échelle du serveur, dans sys.dm_os_wait_stats, sont cumulées depuis le dernier redémarrage ou la dernière remise à zéro manuelle : une lecture isolée décrit surtout le passé. Prenez deux échantillons à une minute environ d’intervalle et comparez la différence. Ne remettez pas les statistiques d’attente à zéro pour obtenir une image propre : vous détruiriez la référence dont d’autres dépendent. L’aide-mémoire Types d’attente contient les requêtes d’échantillonnage et la signification des attentes courantes.

Une requête consomme-t-elle le système ?

Triez les requêtes actives par CPU ou lectures logiques dans Voir les requêtes en cours. Si la même requête revient sans cesse, comparez sa durée et ses plans dans le temps avec Query Store. Aide-mémoire : requêtes gourmandes en CPU et requêtes à forte lecture.

Pic passager ou schéma récurrent ?

Les DMV en direct ne peuvent pas répondre. Comparez des fenêtres de temps dans Query Store, consultez la session Extended Events system_health pour les longues attentes de verrou et les interblocages, et regardez votre historique de supervision. Pour un signalement général « SQL Server est lent », les 15 premières minutes de triage fixent d’abord le périmètre.

Faut-il des éléments en continu ?

Quand les problèmes vont et viennent plus vite que vous ne pouvez échantillonner, capturez-les au moment où ils se produisent : Query Store pour l’historique des requêtes et des plans, une session Extended Events pour des événements précis, ou votre plateforme de supervision. Le rapport des processus bloqués est désactivé par défaut et s’active par une modification de configuration du serveur (blocked process threshold) : traitez-le comme une décision avec un responsable, pas comme un réflexe d’incident.

Éléments instantanés et éléments historiques

Source Nature Ce qu’elle dit Durée de vie
DMV de requêtes, sessions et connexions Instantanée Ce qui s’exécute, attend ou est bloqué maintenant Jusqu’à la fin de la requête ou la fermeture de la session
sys.dm_os_wait_stats Compteurs cumulés Ce que l’instance a attendu depuis son dernier redémarrage Jusqu’au redémarrage ou à une remise à zéro manuelle ; comparez deux échantillons
Moniteur d’activité (SSMS) Vue interactive en direct Un premier coup d’œil aux processus, attentes et requêtes coûteuses Tant que la fenêtre est ouverte ; pas un élément conservé
Query Store Historique, par base Quelles requêtes et quels plans ont changé, et quand Selon sa rétention ; activé par défaut seulement pour les nouvelles bases à partir de SQL Server 2022
Session Extended Events system_health Historique glissant, active par défaut Interblocages, attentes de verrou de plus de 30 secondes, autres longues attentes Jusqu’à la rotation de ses fichiers
Votre propre session Extended Events ou plateforme de supervision Historique, conçu par vous Ce que vous avez choisi de capturer Selon votre rétention

Les DMV font autorité pour « maintenant » et sont la mauvaise source pour « avant ». Une requête terminée disparaît de sys.dm_exec_requests, une instruction rapide peut commencer et finir entre deux échantillons, et des compteurs se réinitialisent au redémarrage ou au basculement de SQL Server. Un instantané ne peut pas prouver ce qui s’est passé dix minutes plus tôt ni établir une référence normale.

Le Moniteur d’activité est pratique pour un premier regard, mais c’est un écran, pas un enregistrement : interrogez les DMV et conservez des résultats horodatés lorsque vous avez besoin d’éléments.

Échantillonner prudemment sur une courte période

Un échantillonnage périodique montre si une requête progresse, attend de façon répétée ou reste derrière le même bloqueur. Capturez l’horodatage, session_id, request_id, la base, l’état, CPU, durée, lectures, écritures, attente, bloqueur et un identifiant SQL stable lorsque cela convient.

Pendant un incident, exécuter les requêtes de pouls et la requête sur les requêtes actives toutes les 5 à 15 secondes durant une période limitée suffit souvent. Enregistrez les résultats hors de la base affectée. Évitez par défaut l’interrogation chaque seconde et ne conservez pas indéfiniment le texte SQL complet lorsqu’un hash, un ID de requête ou un enregistrement restreint suffit.

Passer à Query Store pour l’historique

Utilisez Query Store lorsque la question devient « qu’est-ce qui a changé dans le temps ? ». C’est la meilleure source pour :

  • les tendances de durée, CPU et lectures logiques sur des fenêtres comparables ;
  • les requêtes ralenties après un déploiement ou un changement de données ;
  • plusieurs plans pour une même requête ;
  • un changement de plan qui coïncide avec une régression.

Suivez Comment trouver les régressions de performance SQL Server avec Query Store. La fiche Query Store fournit les requêtes opérationnelles associées.

Query Store n’est activé par défaut que pour les nouvelles bases à partir de SQL Server 2022 ; sur SQL Server 2016, 2017 et 2019, il doit être activé base par base, et il n’enregistre rien d’antérieur à ce moment.

Précautions en production

  • Autorisations : limitez la visibilité sur l’état serveur aux rôles qui en ont besoin.
  • Surcharge : les requêtes DMV sont légères, pas gratuites. Choisissez les colonnes utiles, un intervalle mesuré et arrêtez l’interrogation lorsqu’elle n’est plus nécessaire.
  • Texte sensible : les lots SQL peuvent contenir des données personnelles, identifiants clients ou secrets littéraux. Restreignez, masquez et conservez correctement les captures.
  • Métadonnées clientes : host_name et program_name sont des indices, pas des identités de sécurité.
  • Temps réel contre historique : n’utilisez pas un instantané pour conclure sur un incident terminé. Utilisez Query Store, Extended Events ou un historique existant.
  • Réponse sûre : observer une requête n’autorise pas à la tuer, vider les caches, ajouter un index ou modifier le serveur. Validez d’abord la cause.

Où aller ensuite

Vous devez… Allez à
Voir chaque requête active et ce qu’elle coûte Voir les requêtes en cours
Remonter une chaîne de blocage jusqu’au bloqueur principal Diagnostiquer les blocages
Répondre à un incident de blocage en cours Runbook de blocage SQL Server (en anglais)
Trier un signalement général « SQL Server est lent » SQL Server est lent : les 15 premières minutes
Examiner une session en détail Obtenir l’ID de session SQL Server
Analyser une régression déjà passée Régressions Query Store
Suivre l’ordre complet d’investigation Démarche d’optimisation et la liste de contrôle d’optimisation (en anglais)

Aide-mémoire utiles : Démarche de diagnostic, Types d’attente, requêtes gourmandes en CPU, requêtes à forte lecture et Query Store.

Sources

Vérifié en novembre 2026.

Étiquettes

  • SQL Server
  • DMV
  • Supervision
  • Observabilité
  • Performance
  • Blocages
  • Query Store