Parcourir les aide-mémoire · SQL Server
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
- Problème
- Réponse rapide
- Code / commandes
- 1. Inspecter les détails essentiels de la session et de la requête
- 2. Afficher l’instruction en cours d’exécution dans le lot
- 3. Vérifier si la session est bloquée ou en bloque une autre
- 4. Inspecter le contexte des transactions ouvertes
- 5. Afficher la dernière commande soumise par une session en veille
- 6. Inspecter les métadonnées au niveau de la connexion
- 7. Récupérer le texte SQL le plus récent à partir du handle de connexion
- 8. Récupérer le handle du plan d’exécution actif et le XML du plan
- Interpréter le résultat
- Points à vérifier
- Session sleeping avec open_transaction_count > 0
- Requête suspended
- Requête runnable
- CPU élevé avec un temps écoulé relativement faible
- Temps écoulé élevé avec peu de CPU
- program_name ou host_name inattendu
- Transaction démarrée il y a longtemps
- Notes de production
- Session et requête sont deux notions distinctes
- Les autorisations varient selon la version de SQL Server
- Le texte SQL et les métadonnées du client peuvent être sensibles
- Les métriques d’exécution concernent la requête en cours
- Le plan d’exécution observé n’explique pas forcément le comportement passé
- Erreurs fréquentes
- 1. Supposer qu’une session en veille est inoffensive
- 2. Lire le CPU de la session comme celui de la requête en cours
- 3. Considérer DBCC INPUTBUFFER comme un historique complet
- 4. Tuer la session avant de vérifier les transactions
- 5. Se fier à host_name ou program_name comme preuve de sécurité
- 6. Ne regarder que le texte de la requête
- Étape suivante
- 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_typewait_timewait_resourceblocking_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.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.