Parcourir les aide-mémoire · SQL Server
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
- Problème
- Réponse rapide
- Code / commandes
- 1. Afficher les requêtes utilisateur actives les plus longues
- 2. Afficher l’instruction en cours, pas seulement le lot complet
- 3. Afficher les requêtes actives qui ont consommé le plus de CPU jusqu’ici
- 4. Afficher les requêtes actives qui effectuent le plus de lectures logiques
- 5. Trouver les requêtes actives bloquées
- 6. Examiner une session en détail
- Interpréter le résultat
- Points à vérifier
- Durée écoulée longue avec peu de CPU
- CPU élevé par rapport aux autres requêtes actives
- Lectures logiques élevées
- blocking_session_id > 0
- wait_type renseigné et statut suspended
- host_name ou program_name inattendu
- Notes de production
- Les données des DMV sont éphémères
- Les autorisations varient selon la version de SQL Server
- Le texte des requêtes peut contenir des informations sensibles
- Cette requête est un instantané, pas une référence de charge de travail
- Évitez SELECT * dans les outils DMV réutilisables
- Erreurs fréquentes
- 1. Ne regarder que status = 'running'
- 2. Confondre durée écoulée et temps CPU
- 3. Tuer une session avant de la comprendre
- 4. Supposer que la requête bloquée est le problème
- 5. Ajouter un index parce qu’une requête effectue beaucoup de lectures
- 6. Utiliser cette DMV comme preuve historique
- Étape suivante
- 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_timeetblocking_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_timecpu_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.
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.