Parcourir les aide-mémoire · SQL Server

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

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
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Regrouper les requêtes actives par login
  5. 2. Regrouper les requêtes actives par application
  6. 3. Regrouper les requêtes actives par hôte
  7. 4. Regrouper l’activité par login, hôte, application et base de données
  8. 5. Compter les sessions, y compris les connexions en veille
  9. 6. Trouver les sessions avec des transactions ouvertes par application
  10. 7. Trouver les requêtes bloquées par application
  11. 8. Trouver les sessions bloquantes et leur application d’origine
  12. 9. Afficher le détail des sessions d’une application
  13. 10. Examiner l’origine de la connexion et les détails d’authentification
  14. Interpréter le résultat
  15. Points à vérifier
  16. Une application domine les requêtes actives
  17. Un login domine le CPU ou les lectures
  18. Beaucoup de sessions mais peu de requêtes actives
  19. Nombreuses requêtes bloquées pour une même application
  20. La session bloquante est en veille avec une transaction ouverte
  21. Application ou hôte inattendu
  22. Notes de production
  23. host_name et program_name sont fournis par le client
  24. Requêtes actives et sessions connectées répondent à des questions différentes
  25. Les résultats agrégés des DMV sont des instantanés
  26. Les logins partagés réduisent l’observabilité
  27. Le pool de connexions influe sur le nombre de sessions
  28. Les autorisations varient selon la version
  29. Erreurs fréquentes
  30. 1. Considérer host_name comme une source fiable
  31. 2. Supposer que de nombreuses sessions en veille posent problème
  32. 3. Accuser l’application bloquée
  33. 4. Regrouper uniquement par login
  34. 5. Désactiver un login pendant un incident sans confirmer son propriétaire
  35. 6. Prendre un seul instantané pour un historique du trafic
  36. 7. Ignorer les transactions ouvertes
  37. Étape suivante
  38. 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.

Étape suivante

Servez-vous de la répartition par propriétaire pour choisir la piste d’analyse suivante.

  • Une application domine le CPU → inspectez ses requêtes actives ou gourmandes en CPU et leurs plans.
  • Une application domine les lectures → inspectez ses requêtes à forte lecture et ses chemins d’accès.
  • Une application a de nombreuses requêtes bloquées → trouvez la session bloquante et l’application dont elle provient.
  • Une application détient des sessions en veille avec des transactions ouvertes → examinez la gestion des transactions.
  • Beaucoup de sessions mais peu de travail actif → validez le pool de connexions avant de modifier les limites.
  • Un hôte ou une application inattendus apparaissent → confirmez le propriétaire et la légitimité de l’accès.
  • Vous avez besoin des tendances de trafic horaires ou quotidiennes → conservez des instantanés des DMV ou utilisez la supervision ou les Extended Events.
  • Rien d’anormal du côté de l’identité des clients → poursuivez avec Query Store, les attentes et le diagnostic au niveau du système.

← Tous les aide-mémoire