Parcourir les aide-mémoire · SQL Server

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

Voir ce qui s’exécute dans SQL Server

Identifiez rapidement les requêtes SQL Server actives : ce qu’elles exécutent, depuis combien de temps, quelles ressources elles consomment et si elles sont en attente ou bloquées.

Sur cette page
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Afficher les requêtes utilisateur actives les plus longues
  5. 2. Afficher l’instruction en cours, pas seulement le lot complet
  6. 3. Afficher les requêtes actives qui ont consommé le plus de CPU jusqu’ici
  7. 4. Afficher les requêtes actives qui effectuent le plus de lectures logiques
  8. 5. Trouver les requêtes actives bloquées
  9. 6. Examiner une session en détail
  10. Interpréter le résultat
  11. Points à vérifier
  12. Durée écoulée longue avec peu de CPU
  13. CPU élevé par rapport aux autres requêtes actives
  14. Lectures logiques élevées
  15. blocking_session_id > 0
  16. wait_type renseigné et statut suspended
  17. host_name ou program_name inattendu
  18. Notes de production
  19. Les données des DMV sont éphémères
  20. Les autorisations varient selon la version de SQL Server
  21. Le texte des requêtes peut contenir des informations sensibles
  22. Cette requête est un instantané, pas une référence de charge de travail
  23. Évitez SELECT * dans les outils DMV réutilisables
  24. Erreurs fréquentes
  25. 1. Ne regarder que status = 'running'
  26. 2. Confondre durée écoulée et temps CPU
  27. 3. Tuer une session avant de la comprendre
  28. 4. Supposer que la requête bloquée est le problème
  29. 5. Ajouter un index parce qu’une requête effectue beaucoup de lectures
  30. 6. Utiliser cette DMV comme preuve historique
  31. Étape suivante
  32. Pour aller plus loin

Problème

SQL Server est lent, le CPU est élevé, les utilisateurs signalent des lenteurs, ou vous avez simplement besoin de savoir ce qui s’exécute en ce moment.

L’objectif n’est pas seulement de lister les sessions. Il vous faut assez de contexte pour répondre aux questions suivantes :

  • Quelle instruction est en cours d’exécution ?
  • Depuis combien de temps s’exécute-t-elle ?
  • Quelle quantité de CPU et d’E/S a-t-elle consommée ?
  • Est-elle en attente ?
  • Une autre session la bloque-t-elle ?
  • Quel login, quel hôte et quelle application l’ont lancée ?

Réponse rapide

Utilisez sys.dm_exec_requests pour les requêtes en cours d’exécution, joignez sys.dm_exec_sessions pour le contexte de session et utilisez sys.dm_exec_sql_text pour récupérer le texte SQL.

SELECT
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.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,
    s.login_name,
    s.host_name,
    s.program_name,
    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
INNER 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;

Commencez ici pendant que le problème se produit. Il s’agit d’un instantané en temps réel : exécutez-le pendant que le ralentissement ou l’incident est en cours.

Code / commandes

1. Afficher les requêtes utilisateur actives les plus longues

Utilisez cette requête pour un premier coup d’œil rapide lorsque vous n’avez pas besoin de toutes les métadonnées de session.

SELECT
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    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.blocking_session_id
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
WHERE
    r.session_id <> @@SPID
    AND s.is_user_process = 1
ORDER BY
    r.total_elapsed_time DESC;

Utilisez-la comme requête de tri légère lorsque vous savez déjà comment examiner une session précise ensuite.


2. Afficher l’instruction en cours, pas seulement le lot complet

Un lot peut contenir de nombreuses instructions. Pendant un diagnostic, la question utile est souvent :

Quelle instruction de ce lot est en cours d’exécution en ce moment ?

SELECT
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.start_time,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    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 <> @@SPID
ORDER BY
    r.total_elapsed_time DESC;

Utilisez statement_text pour l’instruction que SQL Server exécute actuellement et batch_text lorsque vous avez besoin du lot qui l’entoure ou de l’appel de procédure stockée.


3. Afficher les requêtes actives qui ont consommé le plus de CPU jusqu’ici

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.cpu_time AS cpu_ms,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.logical_reads,
    r.wait_type,
    r.blocking_session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER 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.cpu_time DESC;

Utilisez cette requête lorsque le CPU est actuellement élevé et que vous voulez identifier les requêtes actives qui ont accumulé le plus de temps CPU.

Ne la confondez pas avec un rapport historique des « requêtes les plus consommatrices de CPU ». sys.dm_exec_requests affiche les requêtes actives, pas l’historique de la charge de travail terminée.


4. Afficher les requêtes actives qui effectuent le plus de lectures logiques

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.logical_reads,
    r.reads,
    r.cpu_time AS cpu_ms,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.wait_type,
    r.blocking_session_id,
    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 <> @@SPID
ORDER BY
    r.logical_reads DESC;

Un nombre élevé de lectures logiques peut vous orienter vers des scans coûteux, une faible sélectivité, des chemins d’accès inefficaces ou simplement une charge de travail réellement volumineuse.

Ne supposez pas que « beaucoup de lectures = index manquant ». Examinez le plan d’exécution et le contexte de la charge de travail avant de choisir une correction.


5. Trouver les requêtes actives bloquées

SELECT
    r.session_id AS blocked_session_id,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    txt.text AS blocked_batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.blocking_session_id > 0
ORDER BY
    r.wait_time DESC;

Utilisez cette requête pour confirmer rapidement un blocage classique d’une session par une autre.

Un blocking_session_id positif désigne une autre session comme bloqueur. SQL Server peut aussi renvoyer des valeurs négatives spéciales dans certains scénarios internes ou de transactions distribuées : ne considérez donc pas toute valeur non nulle comme un identifiant de session ordinaire.


6. Examiner une session en détail

Dès que vous avez un session_id suspect, réduisez le périmètre.

DECLARE @session_id int = 57;

SELECT
    r.session_id,
    r.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,
    s.login_name,
    s.host_name,
    s.program_name,
    s.client_interface_name,
    s.open_transaction_count,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER 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 = @session_id;

C’est souvent une meilleure deuxième étape que d’analyser encore et encore toutes les requêtes actives.

Interpréter le résultat

Colonne Pourquoi c’est important
session_id Identifiant permettant de relier la requête aux sessions, aux blocages, aux transactions et aux autres DMV.
status État actuel de la requête, par exemple running, runnable ou suspended.
command Opération générale effectuée par SQL Server, par exemple SELECT, UPDATE ou BACKUP DATABASE.
database_name Contexte de base de données de la requête.
start_time Moment où la requête actuelle a démarré.
elapsed_seconds Durée murale pendant laquelle la requête actuelle a été active.
cpu_ms Temps CPU accumulé par la requête actuelle.
logical_reads Pages lues dans le buffer pool de SQL Server par la requête. Utile pour repérer un travail dominé par les lectures.
reads Lectures physiques effectuées par la requête.
writes Écritures effectuées par la requête.
wait_type Ce que la requête attend actuellement lorsqu’elle est suspendue.
wait_ms Durée de l’attente actuelle de la requête.
wait_resource Ressource associée à l’attente actuelle, lorsqu’elle est disponible.
blocking_session_id Session qui bloque actuellement cette requête. 0 signifie normalement qu’aucune session bloquante n’est identifiée.
login_name Login propriétaire de la session.
host_name Nom de la machine cliente indiqué par la connexion.
program_name Nom de l’application indiqué par la connexion cliente.
statement_text Instruction en cours d’exécution dans le lot.
batch_text Lot SQL complet associé à la requête.

Points à vérifier

Durée écoulée longue avec peu de CPU

Interprétation possible :

  • la requête est peut-être en attente plutôt qu’en train de consommer activement du CPU
  • examinez status, wait_type, wait_time et blocking_session_id

Si status = suspended, les informations d’attente sont généralement plus utiles que le CPU seul.

CPU élevé par rapport aux autres requêtes actives

Interprétation possible :

  • expression ou opérateur gourmand en CPU
  • jointures ou agrégations volumineuses
  • plan d’exécution inefficace
  • pression liée à la compilation ou à la recompilation
  • simplement une charge de travail réellement coûteuse

Examinez ensuite le plan d’exécution et comparez la requête avec l’état du CPU à l’échelle du serveur.

Lectures logiques élevées

Interprétation possible :

  • scan volumineux
  • faible sélectivité des prédicats
  • stratégie de jointure inefficace
  • indexation insuffisante pour le modèle d’accès
  • charge de travail volumineuse attendue

Ne créez pas d’index sur la seule base des lectures logiques. Confirmez le modèle d’accès et le plan d’exécution.

blocking_session_id > 0

La requête attend une autre session.

Passez immédiatement à l’analyse de la chaîne de blocage au lieu d’optimiser isolément la requête bloquée.

wait_type renseigné et statut suspended

La requête attend une ressource ou un événement.

Le type d’attente vous indique où chercher ensuite. Il n’identifie pas automatiquement la cause racine.

host_name ou program_name inattendu

Cela peut révéler :

  • du trafic applicatif qui atteint la mauvaise base de données
  • des jobs planifiés inattendus
  • du trafic de reporting pendant le pic de charge
  • des clients API qui génèrent plus d’activité que prévu
  • des outils d’administration restés ouverts

Utilisez les métadonnées de connexion comme indice complémentaire, pas comme preuve de causalité.

Notes de production

Les données des DMV sont éphémères

sys.dm_exec_requests décrit les requêtes qui s’exécutent maintenant.

Dès qu’une requête se termine, elle disparaît de cette DMV. Si un incident est intermittent, capturez le résultat pendant que le problème se produit ou utilisez Query Store et la supervision pour l’analyse historique.

Les autorisations varient selon la version de SQL Server

Pour SQL Server 2019 et les versions antérieures, voir toutes les sessions en cours d’exécution nécessite normalement VIEW SERVER STATE.

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

Azure SQL Database applique des règles de visibilité et d’autorisation différentes : ne supposez pas qu’une requête de niveau serveur ou on-premises y exposera toutes les sessions.

Le texte des requêtes peut contenir des informations sensibles

sys.dm_exec_sql_text peut exposer des lots SQL contenant des littéraux ou d’autres données applicatives.

Soyez prudent lorsque vous :

  • copiez le texte des requêtes dans des tickets
  • stockez des instantanés de DMV
  • envoyez des résultats de diagnostic par chat ou par e-mail
  • publiez des exemples

Expurgez les valeurs sensibles avant de partager.

Cette requête est un instantané, pas une référence de charge de travail

Une requête en tête de liste n’est pas automatiquement la cause racine.

Comparez :

  • le CPU
  • les lectures
  • la durée écoulée
  • l’état d’attente
  • les blocages
  • la concurrence
  • les symptômes au niveau du serveur

avant de prendre une décision d’optimisation.

Évitez SELECT * dans les outils DMV réutilisables

Sélectionnez explicitement les colonnes dont vous avez besoin. Les schémas des DMV peuvent évoluer d’une version de SQL Server à l’autre, et des colonnes explicites rendent aussi les résultats de diagnostic plus faciles à interpréter.

Erreurs fréquentes

1. Ne regarder que status = 'running'

Une requête à l’état suspended peut être la plus importante du serveur, car elle attend peut-être à cause d’un blocage, des E/S, de la mémoire ou d’une autre ressource.

Examinez les requêtes actives, qu’elles soient ou non sur le CPU à cet instant.

2. Confondre durée écoulée et temps CPU

Une requête qui s’exécute depuis 60 secondes n’a pas forcément consommé 60 secondes de CPU.

Comparez :

  • total_elapsed_time
  • cpu_time
  • les attentes

avant de conclure à une pression sur le CPU.

3. Tuer une session avant de la comprendre

Ne faites pas de KILL <session_id> l’action de diagnostic par défaut.

Avant de mettre fin à une session, déterminez :

  • ce qu’elle fait
  • si elle détient une transaction ouverte
  • quelle application en est propriétaire
  • l’impact métier
  • le coût du rollback

4. Supposer que la requête bloquée est le problème

Une requête bloquée est souvent la victime.

Trouvez le bloqueur principal et déterminez pourquoi il conserve des verrous avant d’optimiser l’instruction bloquée.

5. Ajouter un index parce qu’une requête effectue beaucoup de lectures

Un nombre élevé de lectures est un symptôme, pas une recommandation d’index.

Validez le plan d’exécution, les prédicats, la distribution des données, les index existants et le coût en écriture avant de modifier l’indexation.

6. Utiliser cette DMV comme preuve historique

Les requêtes terminées disparaissent de sys.dm_exec_requests.

Pour une investigation historique, passez à Query Store, aux données de supervision, aux Extended Events ou à une autre source de télémétrie persistante.

Étape suivante

Utilisez le résultat pour choisir la prochaine piste d’investigation.

  • blocking_session_id > 0 → examinez la chaîne de blocage et identifiez le bloqueur principal.
  • cpu_time élevé → examinez les requêtes à forte consommation de CPU et le plan d’exécution actuel.
  • logical_reads élevé → examinez les requêtes à forte lecture, les chemins d’accès, les prédicats et l’indexation.
  • Durée écoulée longue + wait_type significatif → étudiez la catégorie d’attente.
  • Hôte ou application inattendu → regroupez la charge de travail par login, hôte et nom de programme.
  • La requête est déjà terminée → utilisez Query Store ou une supervision persistante plutôt que sys.dm_exec_requests.
  • Rien d’évident n’apparaît → poursuivez avec la démarche plus large de diagnostic des performances SQL Server.

← Tous les aide-mémoire