Parcourir les aide-mémoire · SQL Server

Technologie: SQL ServerCatégorie: DépannageNiveau: IntermédiaireMis à jour

Inspecter une session SQL Server

Inspectez en détail une session SQL Server : état de la requête, attentes, contexte transactionnel, métadonnées du client, texte SQL et handles de plan d’exécution.

Sur cette page
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Inspecter les détails essentiels de la session et de la requête
  5. 2. Afficher l’instruction en cours d’exécution dans le lot
  6. 3. Vérifier si la session est bloquée ou en bloque une autre
  7. 4. Inspecter le contexte des transactions ouvertes
  8. 5. Afficher la dernière commande soumise par une session en veille
  9. 6. Inspecter les métadonnées au niveau de la connexion
  10. 7. Récupérer le texte SQL le plus récent à partir du handle de connexion
  11. 8. Récupérer le handle du plan d’exécution actif et le XML du plan
  12. Interpréter le résultat
  13. Points à vérifier
  14. Session sleeping avec open_transaction_count > 0
  15. Requête suspended
  16. Requête runnable
  17. CPU élevé avec un temps écoulé relativement faible
  18. Temps écoulé élevé avec peu de CPU
  19. program_name ou host_name inattendu
  20. Transaction démarrée il y a longtemps
  21. Notes de production
  22. Session et requête sont deux notions distinctes
  23. Les autorisations varient selon la version de SQL Server
  24. Le texte SQL et les métadonnées du client peuvent être sensibles
  25. Les métriques d’exécution concernent la requête en cours
  26. Le plan d’exécution observé n’explique pas forcément le comportement passé
  27. Erreurs fréquentes
  28. 1. Supposer qu’une session en veille est inoffensive
  29. 2. Lire le CPU de la session comme celui de la requête en cours
  30. 3. Considérer DBCC INPUTBUFFER comme un historique complet
  31. 4. Tuer la session avant de vérifier les transactions
  32. 5. Se fier à host_name ou program_name comme preuve de sécurité
  33. 6. Ne regarder que le texte de la requête
  34. Étape suivante
  35. Pour aller plus loin

Problème

Vous connaissez déjà le session_id (SPID) qui vous intéresse et vous devez comprendre précisément ce que fait cette connexion.

Les questions utiles sont les suivantes :

  • La session est-elle active ou en veille ?
  • A-t-elle actuellement une requête en cours ?
  • Quel code SQL s’exécute ?
  • Qu’attend-elle ?
  • Est-elle bloquée ?
  • A-t-elle une transaction ouverte ?
  • Quels login, hôte et application sont à l’origine de la connexion ?
  • Combien de CPU, d’E/S et de temps écoulé la requête en cours a-t-elle accumulés ?

Il s’agit de l’étape d’analyse détaillée qui suit une requête plus large ayant identifié une session suspecte.

Réponse rapide

Commencez par sys.dm_exec_sessions, ajoutez la requête active de sys.dm_exec_requests par une jointure externe gauche, puis récupérez le texte SQL avec sys.dm_exec_sql_text.

DECLARE @session_id int = 57;

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.original_login_name,
    s.host_name,
    s.program_name,
    s.client_interface_name,
    s.login_time,
    s.last_request_start_time,
    s.last_request_end_time,
    s.open_transaction_count,

    r.status AS request_status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.start_time AS request_start_time,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    r.blocking_session_id,

    txt.text AS batch_text,
    r.plan_handle
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    s.session_id = @session_id;

Si la session est active, vous obtenez la requête en cours. Si elle est en veille, les colonnes de la requête vaudront NULL, ce qui constitue en soi une information utile.

Code / commandes

1. Inspecter les détails essentiels de la session et de la requête

Utilisez cette requête comme analyse détaillée par défaut.

DECLARE @session_id int = 57;

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.original_login_name,
    s.host_name,
    s.program_name,
    s.client_interface_name,
    s.login_time,
    s.last_request_start_time,
    s.last_request_end_time,
    s.open_transaction_count,

    r.status AS request_status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.start_time,
    r.total_elapsed_time,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,

    txt.text AS batch_text,
    r.sql_handle,
    r.plan_handle
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    s.session_id = @session_id;

Cette requête utilise volontairement un LEFT JOIN. Une session en veille n’a aucune ligne de requête active, et vous voulez tout de même voir les métadonnées de la session.


2. Afficher l’instruction en cours d’exécution dans le lot

Lorsqu’une procédure stockée ou un lot de plusieurs instructions s’exécute, le lot complet apporte souvent trop de contexte.

DECLARE @session_id int = 57;

SELECT
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.start_time,
    CASE
        WHEN r.statement_start_offset IS NULL THEN txt.text
        ELSE SUBSTRING(
            txt.text,
            (r.statement_start_offset / 2) + 1,
            (
                (
                    CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(txt.text)
                        ELSE r.statement_end_offset
                    END
                    - r.statement_start_offset
                ) / 2
            ) + 1
        )
    END AS statement_text,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id = @session_id;

Utilisez statement_text pour vous concentrer sur ce qui s’exécute maintenant, tout en conservant batch_text pour le contexte.


3. Vérifier si la session est bloquée ou en bloque une autre

DECLARE @session_id int = 57;

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    r.status,
    r.command
FROM sys.dm_exec_requests AS r
WHERE
    r.session_id = @session_id

UNION ALL

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    r.status,
    r.command
FROM sys.dm_exec_requests AS r
WHERE
    r.blocking_session_id = @session_id;

Interprétez le premier jeu de résultats comme « cette session est-elle bloquée ? » et le second comme « cette session bloque-t-elle des requêtes actives ? ».

Pour une chaîne complète, utilisez l’aide-mémoire dédié aux blocages.


4. Inspecter le contexte des transactions ouvertes

Utilisez cette requête lorsque la session est en veille, bloque d’autres sessions ou semble détenir des verrous après la fin de sa requête.

DECLARE @session_id int = 57;

SELECT
    s.session_id,
    s.status,
    s.open_transaction_count,
    st.transaction_id,
    at.name AS transaction_name,
    at.transaction_begin_time,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.session_id = @session_id;

Une session en veille avec une transaction active ancienne est un signal important lors d’un incident de blocage.


5. Afficher la dernière commande soumise par une session en veille

S’il n’y a aucune requête active, sys.dm_exec_requests ne peut pas afficher le texte SQL en cours.

Pour une session ciblée :

DBCC INPUTBUFFER (57);

Utilisez cette commande pour obtenir la dernière instruction soumise, à titre de contexte.

Ne supposez pas que l’instruction renvoyée est forcément celle qui a acquis tous les verrous que la session détient encore.


6. Inspecter les métadonnées au niveau de la connexion

Utilisez sys.dm_exec_connections lorsque vous avez besoin de détails réseau ou de protocole, ou du handle SQL le plus récent associé à la connexion.

DECLARE @session_id int = 57;

SELECT
    c.session_id,
    c.connect_time,
    c.net_transport,
    c.protocol_type,
    c.auth_scheme,
    c.client_net_address,
    c.local_net_address,
    c.local_tcp_port,
    c.most_recent_sql_handle
FROM sys.dm_exec_connections AS c
WHERE
    c.session_id = @session_id;

Utilisez cette requête lorsque le propriétaire ou l’origine de la connexion n’est pas clair.

Soyez prudent lorsque vous partagez des adresses réseau dans des tickets ou des exemples publics.


7. Récupérer le texte SQL le plus récent à partir du handle de connexion

Cela peut aider lorsque la session n’a aucune requête active mais que la connexion expose encore un handle SQL récent.

DECLARE @session_id int = 57;

SELECT
    c.session_id,
    txt.text AS most_recent_batch_text
FROM sys.dm_exec_connections AS c
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt
WHERE
    c.session_id = @session_id;

Considérez ce résultat comme le contexte récent de la connexion, et non comme un enregistrement garanti de ce qui s’exécute actuellement.


8. Récupérer le handle du plan d’exécution actif et le XML du plan

Si la requête est actuellement active et dispose d’un handle de plan :

DECLARE @session_id int = 57;

SELECT
    r.session_id,
    r.plan_handle,
    qp.query_plan
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE
    r.session_id = @session_id;

Utilisez le plan d’exécution pour comprendre les opérateurs, les analyses (scans), les jointures, les estimations, l’utilisation de la mémoire et les autres comportements au niveau du plan.

Ne prenez pas de décision d’optimisation sur la base d’un seul opérateur. Lisez le plan à la lumière des symptômes observés à l’exécution et de la charge de travail.

Interpréter le résultat

Champ Pourquoi c’est important
session_status État de la connexion ou de la session. Une session peut être sleeping tout en détenant une transaction ouverte.
request_status État de la requête en cours, s’il y en a une. Les valeurs courantes sont running, runnable et suspended.
login_name Login actuellement associé à la session.
original_login_name Identité de login d’origine, avant tout changement de contexte d’exécution.
host_name Hôte client déclaré par la connexion.
program_name Nom de l’application cliente déclaré par la connexion.
last_request_start_time Moment où la requête la plus récente de la session a démarré.
last_request_end_time Moment où sa dernière requête terminée s’est achevée.
open_transaction_count Nombre de transactions ouvertes associées à la session.
blocking_session_id Session qui bloque la requête en cours, le cas échéant.
wait_type Catégorie d’attente actuelle d’une requête active.
wait_resource Ressource liée à l’attente en cours, lorsqu’elle est disponible.
logical_reads Lectures de pages dans le buffer pool accumulées par la requête en cours.
cpu_time Millisecondes de CPU accumulées par la requête en cours.
plan_handle Handle permettant d’inspecter le plan d’exécution en cache actuel.
most_recent_sql_handle Handle SQL associé à la requête la plus récente sur la connexion.

Points à vérifier

Session sleeping avec open_transaction_count > 0

C’est une preuve de grande valeur.

Les causes possibles sont notamment :

  • une transaction applicative laissée ouverte
  • un chemin d’erreur qui a ignoré le commit ou le rollback
  • une transaction manuelle restée non validée
  • une connexion rendue au pool avec un état transactionnel encore ouvert

Si cette même session bloque d’autres sessions, examinez le cycle de vie de la transaction avant toute action destructive.

Requête suspended

Examinez :

  • wait_type
  • wait_time
  • wait_resource
  • blocking_session_id

Une requête suspendue attend quelque chose. La catégorie d’attente indique généralement où chercher ensuite.

Requête runnable

La requête est prête à s’exécuter mais attend du temps CPU sur un scheduler.

Si de nombreuses requêtes sont runnable et que le CPU du serveur est élevé, examinez la pression CPU plutôt que de traiter la session comme un problème de blocage.

CPU élevé avec un temps écoulé relativement faible

La requête consomme peut-être activement beaucoup de CPU.

Inspectez le plan d’exécution et comparez-le avec le reste de la charge active avant de conclure qu’il s’agit de la cause racine.

Temps écoulé élevé avec peu de CPU

La requête passe peut-être l’essentiel de son temps à attendre.

Examinez les attentes en cours et les blocages.

program_name ou host_name inattendu

Cela peut révéler une source de charge imprévue :

  • outil de reporting
  • job planifié
  • service d’API
  • session SSMS ad hoc
  • serveur d’applications
  • processus ETL

Servez-vous-en pour identifier le propriétaire avant de modifier ou d’arrêter quoi que ce soit.

Transaction démarrée il y a longtemps

Une transaction ouverte ancienne peut expliquer :

  • des blocages prolongés
  • une rétention de verrous
  • des problèmes de troncation du journal dans certains scénarios
  • une pression inattendue sur la concurrence

Examinez l’application ou le processus propriétaire de la transaction.

Notes de production

Session et requête sont deux notions distinctes

sys.dm_exec_sessions décrit la connexion ou la session.

sys.dm_exec_requests décrit les requêtes en cours d’exécution.

Une session en veille peut exister sans requête en cours. C’est pourquoi un LEFT JOIN est important dans les outils d’inspection de session.

Les autorisations varient selon la version de SQL Server

Pour SQL Server 2019 et les versions antérieures, une visibilité étendue sur les sessions et les requêtes nécessite généralement VIEW SERVER STATE.

Pour SQL Server 2022 et les versions ultérieures, l’autorisation correspondante est VIEW SERVER PERFORMANCE STATE.

Azure SQL Database applique des règles de portée et d’autorisation différentes.

Le texte SQL et les métadonnées du client peuvent être sensibles

L’inspection d’une session peut exposer :

  • des littéraux SQL
  • des identifiants clients
  • des noms d’hôte
  • des informations d’adresse IP du client
  • des noms d’application
  • des noms de login

Anonymisez la sortie avant de la placer dans des tickets publics, de la documentation ou des outils de messagerie.

Les métriques d’exécution concernent la requête en cours

Les champs comme le CPU, les lectures, les écritures, l’état d’attente et le temps écoulé issus de sys.dm_exec_requests décrivent la requête active.

Ce ne sont pas des totaux cumulés sur toute la durée de vie de la session.

Le plan d’exécution observé n’explique pas forcément le comportement passé

Le plan d’une requête active est précieux, mais les performances en production peuvent varier selon :

  • les valeurs des paramètres
  • les recompilations
  • les changements de plan
  • la concurrence
  • les allocations de mémoire (memory grants)
  • la distribution des données
  • le moment où s’exécute la charge de travail

Utilisez Query Store ou une supervision persistante lorsque l’historique compte.

Erreurs fréquentes

1. Supposer qu’une session en veille est inoffensive

En veille signifie seulement que la session n’a aucune requête active.

Elle peut toujours détenir une transaction ouverte et des verrous.

2. Lire le CPU de la session comme celui de la requête en cours

Les compteurs au niveau de la session et au niveau de la requête répondent à des questions différentes.

Utilisez sys.dm_exec_requests pour diagnostiquer la requête en cours d’exécution.

3. Considérer DBCC INPUTBUFFER comme un historique complet

Cette commande fournit le contexte de l’instruction récente, pas une chronologie complète de toutes les commandes exécutées par la session.

4. Tuer la session avant de vérifier les transactions

Une session avec une transaction ouverte volumineuse peut nécessiter un rollback important.

Comprenez d’abord la transaction.

5. Se fier à host_name ou program_name comme preuve de sécurité

Ces champs sont des métadonnées fournies par le client.

Ce sont des indices opérationnels utiles, pas une frontière de sécurité.

6. Ne regarder que le texte de la requête

Le texte de la requête indique ce qui a été soumis.

Il ne vous dit pas :

  • si la requête est bloquée
  • combien de CPU elle a consommé
  • ce qu’elle attend
  • si la session détient une transaction
  • si le plan est efficace

Utilisez ensemble le contexte de la session, de la requête, de la transaction et du plan.

Étape suivante

Utilisez les détails de la session pour choisir la suite de l’analyse.

  • blocking_session_id > 0 → passez à l’analyse de la chaîne de blocage.
  • La session bloque d’autres sessions → inspectez les transactions ouvertes et identifiez le bloqueur principal.
  • La session est en veille avec une transaction ouverte → examinez le cycle de vie de la transaction et le comportement de l’application.
  • CPU élevé → inspectez le plan d’exécution et la charge de travail gourmande en CPU.
  • Lectures logiques élevées → inspectez les chemins d’accès, les prédicats et l’indexation.
  • Type d’attente significatif → examinez la catégorie d’attente concernée.
  • Aucune requête active mais vous avez besoin de l’historique → utilisez Query Store, les Extended Events ou une supervision persistante.
  • Propriétaire ou application inattendu → remontez jusqu’à la source de la charge de travail grâce au login, à l’hôte, au programme et aux métadonnées de connexion.

← Tous les aide-mémoire