Parcourir les aide-mémoire · SQL Server
Activité par login, hôte et application dans SQL Server
Regroupez l’activité SQL Server par login, hôte, application et base de données pour identifier le client ou la charge de travail à l’origine des connexions, des requêtes, de la consommation CPU, des lectures ou des blocages.
Sur cette page
- Problème
- Réponse rapide
- Code / commandes
- 1. Regrouper les requêtes actives par login
- 2. Regrouper les requêtes actives par application
- 3. Regrouper les requêtes actives par hôte
- 4. Regrouper l’activité par login, hôte, application et base de données
- 5. Compter les sessions, y compris les connexions en veille
- 6. Trouver les sessions avec des transactions ouvertes par application
- 7. Trouver les requêtes bloquées par application
- 8. Trouver les sessions bloquantes et leur application d’origine
- 9. Afficher le détail des sessions d’une application
- 10. Examiner l’origine de la connexion et les détails d’authentification
- Interpréter le résultat
- Points à vérifier
- Une application domine les requêtes actives
- Un login domine le CPU ou les lectures
- Beaucoup de sessions mais peu de requêtes actives
- Nombreuses requêtes bloquées pour une même application
- La session bloquante est en veille avec une transaction ouverte
- Application ou hôte inattendu
- Notes de production
- host_name et program_name sont fournis par le client
- Requêtes actives et sessions connectées répondent à des questions différentes
- Les résultats agrégés des DMV sont des instantanés
- Les logins partagés réduisent l’observabilité
- Le pool de connexions influe sur le nombre de sessions
- Les autorisations varient selon la version
- Erreurs fréquentes
- 1. Considérer host_name comme une source fiable
- 2. Supposer que de nombreuses sessions en veille posent problème
- 3. Accuser l’application bloquée
- 4. Regrouper uniquement par login
- 5. Désactiver un login pendant un incident sans confirmer son propriétaire
- 6. Prendre un seul instantané pour un historique du trafic
- 7. Ignorer les transactions ouvertes
- Étape suivante
- Pour aller plus loin
Problème
Une instance SQL Server est très sollicitée, le volume de requêtes a augmenté ou une application semble exercer une pression particulière.
Vous devez répondre à des questions comme celles-ci :
- Quel login génère le plus de requêtes actives ?
- Quel hôte ouvre le plus de sessions ?
- Quelle application consomme du CPU ou des lectures ?
- Quelle base de données reçoit l’activité ?
- Un login est-il responsable des blocages ?
- Des clients inattendus sont-ils connectés à l’instance ?
C’est particulièrement utile lors d’incidents où plusieurs applications partagent le même SQL Server.
Réponse rapide
Commencez par regrouper les requêtes actives par login, hôte, application et base de données.
SELECT
s.login_name,
s.host_name,
s.program_name,
DB_NAME(r.database_id) AS database_name,
COUNT(*) AS active_request_count,
SUM(r.cpu_time) AS total_cpu_ms,
SUM(r.logical_reads) AS total_logical_reads,
SUM(r.reads) AS total_physical_reads,
SUM(r.writes) AS total_writes,
SUM(
CASE
WHEN r.blocking_session_id > 0 THEN 1
ELSE 0
END
) AS blocked_request_count,
MAX(r.total_elapsed_time) AS max_elapsed_ms
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
GROUP BY
s.login_name,
s.host_name,
s.program_name,
DB_NAME(r.database_id)
ORDER BY
active_request_count DESC,
total_cpu_ms DESC;
Utilisez cette requête en premier lorsque vous devez identifier la charge de travail cliente active en ce moment.
Code / commandes
1. Regrouper les requêtes actives par login
SELECT
s.login_name,
COUNT(*) AS active_request_count,
SUM(r.cpu_time) AS total_cpu_ms,
SUM(r.logical_reads) AS total_logical_reads,
SUM(r.reads) AS total_physical_reads,
SUM(r.writes) AS total_writes
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
GROUP BY
s.login_name
ORDER BY
active_request_count DESC,
total_cpu_ms DESC;
Utilisez cette requête lorsque le même login SQL ou le même utilisateur contenu peut être partagé par de nombreuses sessions applicatives.
2. Regrouper les requêtes actives par application
SELECT
s.program_name,
COUNT(*) AS active_request_count,
SUM(r.cpu_time) AS total_cpu_ms,
SUM(r.logical_reads) AS total_logical_reads,
SUM(r.writes) AS total_writes,
MAX(r.total_elapsed_time) AS max_elapsed_ms
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
GROUP BY
s.program_name
ORDER BY
total_cpu_ms DESC,
active_request_count DESC;
C’est utile lorsque plusieurs services utilisent le même login mais s’identifient grâce au nom d’application de la connexion.
3. Regrouper les requêtes actives par hôte
SELECT
s.host_name,
COUNT(*) AS active_request_count,
SUM(r.cpu_time) AS total_cpu_ms,
SUM(r.logical_reads) AS total_logical_reads,
SUM(r.writes) AS total_writes
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
GROUP BY
s.host_name
ORDER BY
active_request_count DESC,
total_cpu_ms DESC;
Utilisez cette requête pour identifier le serveur d’applications, le poste de travail ou l’hôte de traitement par lots qui produit la charge de travail.
4. Regrouper l’activité par login, hôte, application et base de données
SELECT
s.login_name,
s.host_name,
s.program_name,
DB_NAME(r.database_id) AS database_name,
COUNT(*) AS active_request_count,
SUM(r.cpu_time) AS total_cpu_ms,
SUM(r.logical_reads) AS total_logical_reads,
SUM(r.reads) AS total_physical_reads,
SUM(r.writes) AS total_writes,
SUM(
CASE
WHEN r.blocking_session_id > 0 THEN 1
ELSE 0
END
) AS blocked_request_count
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
GROUP BY
s.login_name,
s.host_name,
s.program_name,
DB_NAME(r.database_id)
ORDER BY
active_request_count DESC,
total_cpu_ms DESC;
C’est souvent la meilleure vue à l’échelle d’un incident, car elle montre à la fois à qui appartient la charge de travail et quelles ressources elle consomme.
5. Compter les sessions, y compris les connexions en veille
Les requêtes actives ne montrent que le travail en cours d’exécution.
Pour examiner l’empreinte des connexions :
SELECT
s.login_name,
s.host_name,
s.program_name,
s.status,
COUNT(*) AS session_count
FROM sys.dm_exec_sessions AS s
WHERE
s.is_user_process = 1
GROUP BY
s.login_name,
s.host_name,
s.program_name,
s.status
ORDER BY
session_count DESC;
Cela permet de répondre aux questions suivantes :
- quelles applications maintiennent le plus de connexions
- si les pools de connexions sont très volumineux
- si de nombreuses sessions sont en veille
Un grand nombre de sessions en veille n’est pas automatiquement un problème.
6. Trouver les sessions avec des transactions ouvertes par application
SELECT
s.login_name,
s.host_name,
s.program_name,
s.status,
COUNT(*) AS sessions_with_open_transactions,
SUM(s.open_transaction_count) AS total_open_transactions
FROM sys.dm_exec_sessions AS s
WHERE
s.is_user_process = 1
AND s.open_transaction_count > 0
GROUP BY
s.login_name,
s.host_name,
s.program_name,
s.status
ORDER BY
total_open_transactions DESC;
Utilisez cette requête lorsqu’une application est soupçonnée de laisser des transactions ouvertes.
Poursuivez par une analyse des transactions au niveau de la session avant d’agir.
7. Trouver les requêtes bloquées par application
SELECT
s.login_name,
s.host_name,
s.program_name,
COUNT(*) AS blocked_request_count,
MAX(r.wait_time) AS max_wait_ms
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE
r.blocking_session_id > 0
AND s.is_user_process = 1
GROUP BY
s.login_name,
s.host_name,
s.program_name
ORDER BY
blocked_request_count DESC,
max_wait_ms DESC;
Cette requête montre quelles applications sont bloquées.
Elle ne vous indique pas quelle application est à l’origine du blocage.
8. Trouver les sessions bloquantes et leur application d’origine
WITH blockers AS
(
SELECT DISTINCT
blocking_session_id AS session_id
FROM sys.dm_exec_requests
WHERE
blocking_session_id > 0
)
SELECT
b.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status,
s.open_transaction_count
FROM blockers AS b
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = b.session_id
ORDER BY
b.session_id;
Utilisez cette requête pour identifier l’application ou le login associé à la session bloquante.
9. Afficher le détail des sessions d’une application
DECLARE @program_name nvarchar(128) = N'Your Application Name';
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
s.status AS session_status,
s.login_time,
s.last_request_start_time,
s.last_request_end_time,
s.open_transaction_count,
r.status AS request_status,
DB_NAME(r.database_id) AS database_name,
r.command,
r.cpu_time AS cpu_ms,
r.logical_reads,
r.wait_type,
r.blocking_session_id,
txt.text AS batch_text
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.is_user_process = 1
AND s.program_name = @program_name
ORDER BY
s.session_id;
Utilisez cette requête après que la vue agrégée a mis en évidence une application suspecte.
10. Examiner l’origine de la connexion et les détails d’authentification
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
c.client_net_address,
c.net_transport,
c.protocol_type,
c.auth_scheme,
c.connect_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c
ON c.session_id = s.session_id
WHERE
s.is_user_process = 1
ORDER BY
c.connect_time DESC;
C’est utile lorsque l’identité de l’application n’est pas claire ou que plusieurs systèmes déclarent le même program_name.
Soyez prudent lorsque vous partagez des adresses IP de clients ou des noms d’hôte en dehors des équipes d’exploitation.
Interpréter le résultat
| Champ | Pourquoi c’est important |
|---|---|
login_name |
Login SQL Server ou identité d’authentification associée à la session. |
host_name |
Nom de l’hôte client déclaré par la connexion. Utile pour remonter jusqu’au propriétaire de la charge de travail. |
program_name |
Nom de l’application déclaré par le pilote client. C’est souvent le moyen le plus simple de distinguer les services. |
active_request_count |
Nombre de requêtes en cours d’exécution dans le groupe. |
session_count |
Nombre de sessions connectées, y compris les sessions en veille. |
total_cpu_ms |
CPU accumulé par les requêtes en cours du groupe. |
total_logical_reads |
Activité sur les pages générée par les requêtes en cours. |
blocked_request_count |
Nombre de requêtes actives actuellement bloquées. |
open_transaction_count |
Indicateur, au niveau de la session, que des transactions restent ouvertes. |
client_net_address |
Adresse réseau côté client telle que SQL Server la voit. |
auth_scheme |
Mécanisme d’authentification utilisé par la connexion. |
Points à vérifier
Une application domine les requêtes actives
Cela peut indiquer :
- un pic de trafic
- une tempête de nouvelles tentatives (retry storm)
- une charge de travail bavarde et inefficace
- un job de traitement par lots
- une charge de reporting
- une vague de connexions dans le pool
Comparez le nombre de requêtes avec le CPU, les lectures et le temps écoulé.
Un login domine le CPU ou les lectures
Le login peut correspondre à :
- une seule application
- plusieurs applications partageant les mêmes identifiants
- un job planifié
- un utilisateur de reporting
Analysez host_name et program_name avant d’attribuer la responsabilité.
Beaucoup de sessions mais peu de requêtes actives
C’est souvent le comportement normal du pool de connexions.
Ne supposez pas que le nombre de sessions suffit à indiquer une pression.
Examinez :
- les requêtes actives
- l’utilisation des workers
- les transactions ouvertes
- le renouvellement des connexions
- le comportement de l’application
Nombreuses requêtes bloquées pour une même application
Cela identifie la charge de travail victime.
Ensuite, trouvez la session réellement bloquante et l’application dont elle provient.
La session bloquante est en veille avec une transaction ouverte
C’est un signal fort de problèmes de gestion des transactions.
Examinez :
- la gestion des commit/rollback
- les erreurs applicatives
- les chemins de dépassement de délai (timeout)
- le pool de connexions
Application ou hôte inattendu
Cela peut révéler :
- une activité SSMS ad hoc
- des jobs oubliés
- d’anciennes versions d’applications
- des outils de test qui frappent la production
- un trafic de reporting imprévu
Validez le propriétaire avant de bloquer ou de désactiver un accès.
Notes de production
host_name et program_name sont fournis par le client
Ces champs sont utiles pour l’exploitation, mais ce ne sont pas des identités de sécurité fiables.
Un client peut les définir ou les omettre.
Utilisez-les pour le diagnostic, pas pour l’autorisation.
Requêtes actives et sessions connectées répondent à des questions différentes
sys.dm_exec_requests montre le travail en cours d’exécution.
sys.dm_exec_sessions montre les sessions connectées.
Une charge de travail peut avoir :
- de nombreuses sessions en veille
- peu de requêtes actives
ou :
- peu de sessions
- de nombreuses requêtes coûteuses au fil du temps
Interprétez les deux.
Les résultats agrégés des DMV sont des instantanés
Les requêtes ci-dessus vous indiquent ce qui existe maintenant.
Pour les pics de trafic ou les tendances récurrentes, conservez des instantanés ou utilisez :
- les Extended Events
- Query Store
- la télémétrie applicative
- des outils de supervision
Les logins partagés réduisent l’observabilité
Si plusieurs applications utilisent le même login SQL, l’agrégation par login peut être trop grossière.
Des valeurs Application Name bien renseignées dans les chaînes de connexion facilitent grandement le diagnostic en production.
Le pool de connexions influe sur le nombre de sessions
Les pools de connexions maintiennent volontairement des connexions ouvertes.
Un nombre élevé de sessions en veille peut être normal.
Concentrez-vous sur :
- le renouvellement des connexions
- la pression des requêtes actives
- les transactions laissées ouvertes
- les symptômes d’épuisement du pool
Les autorisations varient selon la version
Pour un accès étendu à ces DMV de performance :
- SQL Server 2019 et les versions antérieures exigent généralement
VIEW SERVER STATE - SQL Server 2022 et les versions ultérieures exigent généralement
VIEW SERVER PERFORMANCE STATE
Azure SQL Database applique des règles de portée et d’autorisation différentes.
Erreurs fréquentes
1. Considérer host_name comme une source fiable
Il s’agit de métadonnées fournies par le client.
Utilisez-les comme un indice, pas comme une preuve de sécurité.
2. Supposer que de nombreuses sessions en veille posent problème
Les pools de connexions peuvent volontairement maintenir de nombreuses sessions inactives.
Examinez la charge de travail et la pression sur les ressources avant d’agir.
3. Accuser l’application bloquée
Une application avec de nombreuses requêtes bloquées peut être la victime.
Trouvez la session bloquante avant de désigner la cause racine.
4. Regrouper uniquement par login
Les logins partagés peuvent masquer l’application réelle.
Combinez login, hôte, programme et base de données.
5. Désactiver un login pendant un incident sans confirmer son propriétaire
Un login peut servir plusieurs applications.
Avant de désactiver un accès, confirmez :
- le responsable métier
- les applications dépendantes
- le plan de retour arrière
- la procédure de réactivation
- la fenêtre de l’incident
6. Prendre un seul instantané pour un historique du trafic
Une seule requête sur les DMV ne peut pas vous dire comment l’activité a évolué au cours de la dernière heure.
Conservez des instantanés si vous avez besoin d’analyser des tendances.
7. Ignorer les transactions ouvertes
Une application apparemment inactive peut toujours détenir des verrous via une transaction ouverte.
Vérifiez l’état des transactions avant de conclure que les sessions en veille sont inoffensives.
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.