Parcourir les aide-mémoire · SQL Server

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

Démarche de diagnostic des performances SQL Server

Appliquez une démarche de diagnostic SQL Server reproductible pour distinguer blocages, CPU, E/S, attentes, régressions de requêtes et pression de charge avant de modifier quoi que ce soit.

Sur cette page
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Confirmer ce qui s’exécute en ce moment
  5. 2. Vérifier immédiatement les blocages
  6. 3. Vérifier les attentes actuelles
  7. 4. Vérifier les principales attentes cumulées
  8. 5. Vérifier les requêtes actives gourmandes en CPU
  9. 6. Vérifier les requêtes actives qui lisent beaucoup
  10. 7. Vérifier Query Store pour détecter une régression historique
  11. 8. Vérifier les transactions ouvertes en cas de blocages ou de croissance du journal
  12. 9. Vérifier l’heure de démarrage de SQL Server avant d’interpréter les DMV cumulatives
  13. 10. Vérifier l’utilisation des index seulement après avoir identifié la charge
  14. 11. Vérifier avec prudence les recommandations d’index manquants
  15. Interpréter le résultat
  16. Points à vérifier
  17. Les blocages expliquent le ralentissement
  18. Le CPU est élevé et les requêtes actives consomment du CPU
  19. Le temps écoulé est élevé mais le CPU est faible
  20. Query Store montre un changement de plan au moment où les performances se sont dégradées
  21. Le serveur semble sain mais une application est lente
  22. Rien ne semble anormal en ce moment
  23. Notes de production
  24. Diagnostiquer avant de modifier la configuration
  25. Les DMV en direct sont éphémères
  26. Query Store fait souvent le lien entre diagnostic en direct et diagnostic historique
  27. L’infrastructure compte toujours
  28. Comparer avant et après une seule modification
  29. Les autorisations varient selon la version de SQL Server
  30. Erreurs fréquentes
  31. 1. Commencer par les index
  32. 2. Tuer des sessions trop tôt
  33. 3. Ne regarder qu’une seule métrique
  34. 4. Utiliser les attentes cumulées comme preuve actuelle
  35. 5. Considérer une recommandation d’index manquant comme la solution
  36. 6. Ignorer les couches applicative et d’infrastructure
  37. 7. Faire plusieurs modifications à la fois
  38. Étape suivante
  39. Pour aller plus loin

Problème

SQL Server est lent, les utilisateurs se plaignent, les tableaux de bord expirent ou une application prend soudain plus de temps que d’habitude.

Le plus difficile n’est pas d’exécuter une requête sur une DMV.

Le plus difficile est de décider quoi vérifier en premier et d’éviter l’optimisation au hasard.

Une démarche de diagnostic utile doit répondre rapidement aux questions suivantes :

  • Le problème se produit-il en ce moment ?
  • S’agit-il de blocages ?
  • S’agit-il d’une pression sur le CPU ?
  • La charge attend-elle le stockage, des verrous, la mémoire, des workers ou autre chose ?
  • Une seule requête est-elle responsable, ou toute la charge est-elle sous pression ?
  • Un plan a-t-il changé ?
  • Le trafic ou la concurrence ont-ils augmenté ?
  • Le problème est-il ponctuel ou historique ?

Réponse rapide

Suivez cette séquence :

1. Confirmer le symptôme et la fenêtre temporelle
2. Vérifier les requêtes actives
3. Vérifier les blocages
4. Vérifier les attentes actuelles
5. Vérifier les traitements gourmands en CPU / en lectures
6. Vérifier les régressions dans Query Store
7. Vérifier les transactions ouvertes en cas de blocage / croissance du journal
8. Vérifier les index seulement après avoir analysé la charge de travail
9. Vérifier l’infrastructure si les preuves SQL pointent hors du moteur
10. Retester après une seule modification contrôlée

La première requête SQL doit généralement être un instantané des requêtes actives :

SELECT
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    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.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.total_elapsed_time DESC;

Ensuite, suivez le symptôme au lieu de passer directement aux index ou aux modifications de configuration.

Code / commandes

1. Confirmer ce qui s’exécute en ce moment

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,
    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;

Utilisez cette requête lorsque le problème est en cours.

Si rien d’anormal n’apparaît, l’incident est peut-être déjà passé. Passez à Query Store ou aux outils de supervision.


2. Vérifier immédiatement les blocages

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,
    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;

Si cette requête renvoie de nombreuses lignes, arrêtez l’optimisation générale et trouvez d’abord le bloqueur principal.

Les blocages peuvent faire paraître lente une requête saine.


3. Vérifier les attentes actuelles

SELECT
    wt.session_id,
    wt.wait_duration_ms,
    wt.wait_type,
    wt.blocking_session_id,
    wt.resource_description
FROM sys.dm_os_waiting_tasks AS wt
ORDER BY
    wt.wait_duration_ms DESC;

Cette requête aide à savoir ce que les sessions attendent en ce moment.

Utilisez la catégorie d’attente pour choisir la branche suivante.


4. Vérifier les principales attentes cumulées

SELECT TOP (30)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE
    wait_time_ms > 0
ORDER BY
    wait_time_ms DESC;

Utilisez les attentes cumulées pour dégager des tendances et du contexte, pas comme preuve de l’incident en cours.

Tenez toujours compte de la fenêtre d’observation.


5. Vérifier les requêtes actives gourmandes en CPU

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    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,
    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.cpu_time DESC;

Un CPU élevé avec peu d’attente indique souvent un travail actif coûteux.

Un temps écoulé élevé avec peu de CPU indique plutôt une attente.


6. Vérifier les requêtes actives qui lisent beaucoup

SELECT TOP (20)
    r.session_id,
    DB_NAME(r.database_id) AS database_name,
    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,
    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 volume de lectures élevé peut indiquer :

  • des scans
  • une faible sélectivité
  • des chemins d’accès inefficaces
  • de grosses requêtes analytiques
  • un comportement sensible aux paramètres

Ne vous précipitez pas sur « ajouter un index ».


7. Vérifier Query Store pour détecter une régression historique

Si le problème est intermittent ou déjà passé :

SELECT TOP (20)
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    SUM(rs.count_executions) AS executions,
    CAST(
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_duration_ms,
    CAST(
        SUM(rs.avg_cpu_time * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)
        / 1000.0
        AS decimal(18,2)
    ) AS weighted_avg_cpu_ms
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
GROUP BY
    q.query_id,
    qt.query_sql_text,
    p.plan_id
ORDER BY
    weighted_avg_duration_ms DESC;

Utilisez cette requête pour trouver les changements de plan et les régressions de charge que les DMV en direct ne peuvent plus montrer.


8. Vérifier les transactions ouvertes en cas de blocages ou de croissance du journal

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    s.open_transaction_count,
    at.transaction_begin_time,
    DATEDIFF(second, at.transaction_begin_time, SYSDATETIME()) AS transaction_age_seconds
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
INNER JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.is_user_process = 1
ORDER BY
    at.transaction_begin_time;

Les sessions inactives (sleeping) qui portent d’anciennes transactions méritent une attention particulière.


9. Vérifier l’heure de démarrage de SQL Server avant d’interpréter les DMV cumulatives

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

C’est important pour :

  • les statistiques d’attente
  • l’utilisation des index
  • les statistiques des procédures
  • les autres indices issus du cache ou des DMV

Un redémarrage récent peut rendre dénuées de sens les conclusions du type « inutilisé » ou « faible activité ».


10. Vérifier l’utilisation des index seulement après avoir identifié la charge

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    i.name AS index_name,
    COALESCE(us.user_seeks, 0) AS user_seeks,
    COALESCE(us.user_scans, 0) AS user_scans,
    COALESCE(us.user_lookups, 0) AS user_lookups,
    COALESCE(us.user_updates, 0) AS user_updates
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
    ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE
    t.is_ms_shipped = 0
    AND i.index_id > 0
ORDER BY
    user_seeks + user_scans + user_lookups DESC;

Utilisez cette requête seulement après avoir identifié une requête ou une table précise.

Optimiser les index sans preuve issue de la charge relève de la devinette.


11. Vérifier avec prudence les recommandations d’index manquants

SELECT TOP (20)
    OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
    migs.user_seeks,
    migs.avg_total_user_cost,
    migs.avg_user_impact,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mid.index_handle = mig.index_handle
WHERE
    mid.database_id = DB_ID()
ORDER BY
    migs.user_seeks
      * migs.avg_total_user_cost
      * (migs.avg_user_impact / 100.0) DESC;

Traitez ce résultat comme une liste de candidats.

Ne créez pas automatiquement des index à partir de la DMV.

Interpréter le résultat

La démarche de diagnostic compte davantage qu’une métrique isolée.

Signal Piste probable
blocking_session_id > 0 Analyse des verrous et des blocages
LCK_M_* Attentes sur verrou
cpu_time élevé Requête ou charge gourmande en CPU
Lectures logiques élevées Requête ou chemin d’accès qui lit beaucoup
Attentes de signal (signal waits) élevées Pression sur l’ordonnancement CPU
PAGEIOLATCH_* Piste des attentes d’E/S sur les pages de données
PAGELATCH_* Contention de latch en mémoire
WRITELOG Pression sur le vidage du journal
RESOURCE_SEMAPHORE Pression sur les allocations de mémoire (memory grants)
THREADPOOL Épuisement des workers ou concurrence
Changement de plan dans Query Store Régression potentielle
Session inactive + transaction ouverte Problème de gestion des transactions
Aucune preuve en direct Piste historique / supervision

Points à vérifier

Les blocages expliquent le ralentissement

Si de nombreuses sessions attendent derrière un même bloqueur, traitez en priorité le bloqueur principal.

Ne perdez pas de temps à optimiser d’abord les victimes.

Le CPU est élevé et les requêtes actives consomment du CPU

Examinez :

  • les plans d’exécution
  • la charge à haute fréquence
  • les scans et les jointures
  • le comportement des paramètres
  • la concurrence

Le temps écoulé est élevé mais le CPU est faible

La requête est probablement en attente.

Examinez :

  • les attentes actuelles
  • les blocages
  • les E/S
  • les attentes mémoire
  • la consommation externe des résultats

Query Store montre un changement de plan au moment où les performances se sont dégradées

C’est un indice fort de régression.

Validez :

  • le nombre d’exécutions
  • la sensibilité aux paramètres
  • les changements de schéma, d’index ou de statistiques
  • la comparabilité de la charge

Le serveur semble sain mais une application est lente

Utilisez :

  • le login
  • l’hôte
  • le nom du programme
  • la base de données
  • la corrélation entre sessions et requêtes

pour isoler la charge de l’application.

Rien ne semble anormal en ce moment

N’en concluez pas que le problème n’a pas existé.

Utilisez :

  • Query Store
  • une supervision persistante
  • Extended Events
  • la télémétrie applicative
  • les métriques d’infrastructure

pour obtenir des preuves historiques.

Notes de production

Diagnostiquer avant de modifier la configuration

N’effectuez pas de modifications au niveau du serveur parce qu’une métrique semble suspecte.

Évitez de modifier :

  • MAXDOP
  • le cost threshold
  • les paramètres mémoire
  • les worker threads
  • l’organisation de tempdb
  • les index

tant que les preuves n’orientent pas dans cette direction.

Les DMV en direct sont éphémères

Les données sur les requêtes actives et les tâches en attente disparaissent lorsque la charge se termine.

Si le problème est intermittent, capturez des preuves ou utilisez une télémétrie persistante.

Query Store fait souvent le lien entre diagnostic en direct et diagnostic historique

Utilisez les DMV en direct pour « ce qui se passe maintenant ».

Utilisez Query Store pour « ce qui a changé au fil du temps ».

L’infrastructure compte toujours

Les symptômes SQL Server peuvent avoir une origine extérieure au moteur.

Exemples :

  • latence du stockage
  • pression CPU sur la VM
  • contraintes mémoire
  • délais réseau
  • lenteur côté client
  • saturation d’IIS ou du serveur d’applications

Ne vous arrêtez pas à la base de données si les preuves SQL pointent vers l’extérieur.

Comparer avant et après une seule modification

Évitez de modifier plusieurs éléments à la fois.

Une boucle de diagnostic contrôlée se présente ainsi :

Observer
→ Formuler une hypothèse
→ Effectuer une seule modification
→ Mesurer
→ Conserver ou annuler

Les autorisations varient selon la version de SQL Server

Pour un accès large aux DMV de performance :

  • SQL Server 2019 et versions antérieures utilisent généralement VIEW SERVER STATE
  • SQL Server 2022 et versions ultérieures utilisent généralement VIEW SERVER PERFORMANCE STATE

Les plateformes Azure SQL ont des règles de portée et d’autorisation différentes.

Erreurs fréquentes

1. Commencer par les index

Les index sont importants, mais tous les ralentissements ne sont pas des problèmes d’indexation.

Les blocages, la pression CPU, les E/S, les allocations de mémoire et les régressions de plan peuvent tous ressembler à du « SQL lent ».

2. Tuer des sessions trop tôt

Tuer un bloqueur peut déclencher une annulation (rollback) et affecter l’application.

Comprenez :

  • l’ancienneté de la transaction
  • l’opération métier
  • le coût de l’annulation

avant d’agir.

3. Ne regarder qu’une seule métrique

Le CPU sans les attentes, les attentes sans les requêtes ou les lectures sans les plans peuvent tous induire en erreur.

Croisez plusieurs signaux.

4. Utiliser les attentes cumulées comme preuve actuelle

Les statistiques d’attente de l’instance peuvent refléter des jours ou des semaines de charge.

Utilisez les tâches en attente actuelles et des deltas sur une fenêtre de temps pour analyser un incident.

5. Considérer une recommandation d’index manquant comme la solution

Les DMV d’index manquants sont indicatives.

Validez au regard :

  • des index existants
  • du plan de requête
  • du coût en écriture
  • de l’importance de la charge

6. Ignorer les couches applicative et d’infrastructure

Une requête SQL peut sembler lente parce que :

  • le client lit les lignes lentement
  • l’API est surchargée
  • IIS ou le serveur d’applications est saturé
  • le stockage présente de la latence
  • la VM manque de CPU

Le contexte de bout en bout compte.

7. Faire plusieurs modifications à la fois

Si les performances s’améliorent, vous ne saurez pas ce qui a résolu le problème.

Si les performances se dégradent, le retour arrière devient plus difficile.

Étape suivante

Suivez cette démarche :

  • Blocages présents → trouvez le bloqueur principal et examinez les transactions ouvertes.
  • CPU élevé → identifiez les requêtes consommatrices de CPU, actives ou dans l’historique, et examinez les plans.
  • Lectures élevées → examinez les prédicats, les scans, les jointures et les index.
  • Les attentes dominent → suivez la catégorie d’attente.
  • Le plan a changé → comparez les plans et envisagez de forcer temporairement un plan si c’est justifié.
  • Aucune preuve en direct → passez à Query Store et à la supervision persistante.
  • SQL semble sain mais l’application est lente → examinez le serveur d’applications, le réseau et le comportement du client.
  • Une modification résout le problème → validez-la sous une charge normale avant de déclarer le problème résolu.

← Tous les aide-mémoire