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