Parcourir les aide-mémoire · SQL Server
Trouver les requêtes à forte lecture
Identifiez les requêtes SQL Server qui effectuent le plus de lectures logiques et physiques, distinguez la charge de travail dominée par les lectures de celle dominée par le CPU et choisissez la bonne étape d’optimisation suivante.
Sur cette page
- Problème
- Réponse rapide
- Code / commandes
- 1. Trouver les requêtes actives qui effectuent le plus de lectures logiques
- 2. Afficher l’instruction en cours d’exécution dans le lot
- 3. Trouver les requêtes en cache par lectures logiques totales
- 4. Trouver les requêtes avec la moyenne de lectures logiques la plus élevée
- 5. Trouver les consommateurs de lectures à haute fréquence
- 6. Regrouper les requêtes logiquement équivalentes par query hash
- 7. Afficher le plan d’exécution des requêtes en cache à forte lecture
- 8. Comparer les lectures logiques et les lectures physiques
- 9. Utiliser Query Store pour l’historique des requêtes à forte lecture
- 10. Vérifier si les lectures élevées se concentrent sur une seule table
- Interpréter le résultat
- Points à vérifier
- Lectures logiques totales élevées + nombre d’exécutions élevé
- Lectures logiques moyennes élevées + faible nombre d’exécutions
- Lectures logiques élevées + CPU élevé
- Lectures logiques élevées + CPU faible
- Lectures physiques élevées
- Écart important entre lectures logiques minimales et maximales
- Notes de production
- Les lectures logiques ne sont pas des lectures disque
- Des lectures logiques élevées ne sont pas automatiquement un problème
- sys.dm_exec_query_stats repose sur le cache
- Les statistiques de requête sont mises à jour après la fin de l’exécution
- Les effets du buffer pool comptent
- Les autorisations varient selon la version
- Erreurs fréquentes
- 1. Assimiler les lectures logiques à des E/S disque
- 2. Supposer que des lectures élevées signifient « index manquant »
- 3. Ne regarder que les lectures totales
- 4. Ne regarder que les lectures moyennes
- 5. Ignorer la variabilité des paramètres
- 6. Optimiser avant de vérifier les besoins du jeu de résultats
- 7. Ignorer la fréquence des appels de l’application
- Étape suivante
- Pour aller plus loin
Problème
SQL Server effectue trop d’E/S, la pression sur le buffer pool augmente, la latence du stockage est visible, ou certaines requêtes accèdent à bien plus de pages que prévu.
L’objectif est de répondre aux questions suivantes :
- Quelles requêtes effectuent le plus de lectures logiques ?
- Quelles requêtes actives sont dominées par les lectures en ce moment ?
- Quelles requêtes en cache sont responsables du plus grand volume total de lectures ?
- Quelles requêtes sont coûteuses par exécution ?
- Les lectures élevées sont-elles dues à des scans, à des prédicats peu efficaces, à la stratégie de jointure, ou simplement à des charges de travail volumineuses et légitimes ?
- Le problème relève-t-il des E/S logiques, des E/S physiques, ou des deux ?
Réponse rapide
Pour les requêtes actives, commencez par sys.dm_exec_requests.
SELECT TOP (20)
r.session_id,
DB_NAME(r.database_id) AS database_name,
r.status,
r.command,
r.logical_reads,
r.reads AS physical_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,
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.logical_reads DESC;
Pour la charge de travail récemment terminée, utilisez sys.dm_exec_query_stats.
SELECT TOP (50)
qs.execution_count,
qs.total_logical_reads,
CAST(
qs.total_logical_reads * 1.0
/ NULLIF(qs.execution_count, 0)
AS decimal(18,2)
) AS avg_logical_reads,
qs.total_physical_reads,
qs.last_execution_time,
txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
qs.total_logical_reads DESC;
Considérez les lectures logiques comme un indice du travail effectué sur les pages en mémoire. Ne traitez pas un nombre élevé de lectures logiques comme la preuve automatique d’un index manquant.
Code / commandes
1. Trouver 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 AS physical_reads,
r.cpu_time AS cpu_ms,
r.total_elapsed_time AS elapsed_ms,
r.wait_type,
r.wait_resource,
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;
Utilisez cette requête pendant un incident en cours.
Un nombre élevé de lectures logiques indique souvent des scans volumineux, des accès répétés ou des chemins d’accès inefficaces.
2. Afficher l’instruction en cours d’exécution dans le lot
SELECT TOP (20)
r.session_id,
r.logical_reads,
r.reads AS physical_reads,
r.cpu_time AS cpu_ms,
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.logical_reads DESC;
C’est plus utile que d’examiner la procédure stockée entière lorsqu’une seule instruction est à l’origine de la pression en lecture.
3. Trouver les requêtes en cache par lectures logiques totales
SELECT TOP (50)
qs.execution_count,
qs.creation_time,
qs.last_execution_time,
qs.total_logical_reads,
CAST(
qs.total_logical_reads * 1.0
/ NULLIF(qs.execution_count, 0)
AS decimal(18,2)
) AS avg_logical_reads,
qs.total_physical_reads,
CAST(
qs.total_physical_reads * 1.0
/ NULLIF(qs.execution_count, 0)
AS decimal(18,2)
) AS avg_physical_reads,
txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
qs.total_logical_reads DESC;
Utilisez cette requête lorsque la question est :
Quelle charge de travail en cache a accédé au plus grand nombre de pages au total ?
4. Trouver les requêtes avec la moyenne de lectures logiques la plus élevée
SELECT TOP (50)
qs.execution_count,
CAST(
qs.total_logical_reads * 1.0
/ NULLIF(qs.execution_count, 0)
AS decimal(18,2)
) AS avg_logical_reads,
qs.total_logical_reads,
qs.max_logical_reads,
qs.min_logical_reads,
qs.last_execution_time,
txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
WHERE
qs.execution_count > 0
ORDER BY
avg_logical_reads DESC;
Utilisez cette requête pour trouver les exécutions individuellement coûteuses.
Gardez toujours execution_count en vue.
5. Trouver les consommateurs de lectures à haute fréquence
SELECT TOP (50)
qs.execution_count,
qs.total_logical_reads,
CAST(
qs.total_logical_reads * 1.0
/ NULLIF(qs.execution_count, 0)
AS decimal(18,2)
) AS avg_logical_reads,
qs.last_execution_time,
txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
WHERE
qs.execution_count >= 100
ORDER BY
qs.total_logical_reads DESC;
Le filtre 100 n’est qu’un exemple.
Ajustez-le à la charge de travail.
Une requête modérément gourmande en lectures peut devenir un consommateur de ressources majeur si elle est appelée fréquemment.
6. Regrouper les requêtes logiquement équivalentes par query hash
Les littéraux des requêtes ad hoc peuvent fragmenter le cache des plans.
SELECT TOP (50)
qs.query_hash,
SUM(qs.execution_count) AS executions,
SUM(qs.total_logical_reads) AS total_logical_reads,
CAST(
SUM(qs.total_logical_reads) * 1.0
/ NULLIF(SUM(qs.execution_count), 0)
AS decimal(18,2)
) AS avg_logical_reads,
MAX(qs.last_execution_time) AS last_execution_time
FROM sys.dm_exec_query_stats AS qs
GROUP BY
qs.query_hash
ORDER BY
total_logical_reads DESC;
Utilisez cette approche lorsque de nombreuses instructions ad hoc similaires font paraître la charge de travail plus faible qu’elle ne l’est réellement.
7. Afficher le plan d’exécution des requêtes en cache à forte lecture
SELECT TOP (20)
qs.execution_count,
qs.total_logical_reads,
CAST(
qs.total_logical_reads * 1.0
/ NULLIF(qs.execution_count, 0)
AS decimal(18,2)
) AS avg_logical_reads,
txt.text AS batch_text,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
qs.total_logical_reads DESC;
Recherchez :
- les scans volumineux de tables ou d’index
- les lookups répétés en boucles imbriquées (nested loops)
- les jointures trop larges
- les prédicats peu sélectifs
- les problèmes d’estimation du nombre de lignes
- le déplacement de lignes larges
- les index manquants ou inefficaces
Ne tirez pas de conclusion à partir d’un seul opérateur.
8. Comparer les lectures logiques et les lectures physiques
Les lectures logiques comptent les accès aux pages servis depuis le buffer pool.
Les lectures physiques comptent les pages qui ont dû être lues depuis le stockage.
SELECT TOP (50)
qs.execution_count,
qs.total_logical_reads,
qs.total_physical_reads,
CAST(
qs.total_logical_reads * 1.0
/ NULLIF(qs.execution_count, 0)
AS decimal(18,2)
) AS avg_logical_reads,
CAST(
qs.total_physical_reads * 1.0
/ NULLIF(qs.execution_count, 0)
AS decimal(18,2)
) AS avg_physical_reads,
txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
qs.total_logical_reads DESC;
Interprétez avec prudence :
- lectures logiques élevées + lectures physiques faibles → beaucoup de travail sur des pages en mémoire
- lectures physiques élevées → le stockage a dû servir davantage de lectures
- les deux élevées → la requête peut être à la fois inefficace et gourmande en stockage
9. Utiliser Query Store pour l’historique des requêtes à forte lecture
Si la charge de travail n’est plus dans le cache, Query Store est la meilleure source historique.
SELECT TOP (20)
q.query_id,
p.plan_id,
qt.query_sql_text,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_logical_io_reads * rs.count_executions)
AS decimal(18,2)
) AS total_logical_reads,
CAST(
SUM(rs.avg_logical_io_reads * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
AS decimal(18,2)
) AS weighted_avg_logical_reads
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,
p.plan_id,
qt.query_sql_text
ORDER BY
total_logical_reads DESC;
Utilisez cette approche pour :
- l’analyse des régressions
- les comparaisons avant/après
- les changements de plan
- les requêtes qui ne sont plus présentes dans le cache des plans
10. Vérifier si les lectures élevées se concentrent sur une seule table
Une fois la requête identifiée, examinez d’abord le plan d’exécution.
Examinez ensuite la taille des tables et des index ainsi que le chemin d’accès aux objets ciblés.
Pour vérifier rapidement la taille des tables :
SELECT
s.name AS schema_name,
t.name AS table_name,
SUM(ps.row_count) AS row_count,
CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
WHERE
t.is_ms_shipped = 0
GROUP BY
s.name,
t.name
ORDER BY
used_mb DESC;
Des scans volumineux sur de grandes tables peuvent être tout à fait normaux.
La décision dépend de la sélectivité et de l’intention métier.
Interpréter le résultat
| Champ | Pourquoi c’est important |
|---|---|
logical_reads |
Nombre de pages de 8 Ko lues depuis le buffer pool par une requête active. |
reads |
Lectures physiques effectuées par une requête active. |
total_logical_reads |
Lectures logiques cumulées représentées par une ligne de statistiques de requête en cache. |
total_physical_reads |
Lectures physiques cumulées représentées par une ligne de statistiques de requête en cache. |
avg_logical_reads |
Moyenne calculée des lectures par exécution. Utile avec le nombre d’exécutions. |
max_logical_reads |
Nombre de lectures logiques le plus élevé observé pour une exécution au cours du cycle de vie actuel du cache. |
min_logical_reads |
Nombre de lectures logiques le plus faible observé. Utile pour repérer les écarts. |
query_hash |
Aide à regrouper les instructions logiquement équivalentes. |
execution_count |
Nombre d’exécutions terminées représentées par la ligne de statistiques. |
Points à vérifier
Lectures logiques totales élevées + nombre d’exécutions élevé
Cela indique souvent un chemin critique exécuté fréquemment.
Étapes suivantes possibles :
- réduire la fréquence des appels
- améliorer le chemin d’accès aux données
- mettre en cache les résultats répétés
- regrouper les requêtes par lots
- optimiser les jointures répétées
Lectures logiques moyennes élevées + faible nombre d’exécutions
Il s’agit probablement d’une requête coûteuse en elle-même.
Examinez :
- la taille des scans
- les prédicats
- l’ordre des jointures
- les estimations de cardinalité
- la couverture des index
- le volume de données
Lectures logiques élevées + CPU élevé
Cela indique souvent un traitement coûteux des lignes et des pages.
Le plan lit et traite peut-être bien plus de données que nécessaire.
Lectures logiques élevées + CPU faible
Causes possibles :
- des scans volumineux mais efficaces
- des attentes qui masquent le CPU
- des données déjà en cache
- un mode d’accès simple mais étendu
Ne supposez pas que c’est sans danger ou problématique sans connaître le contexte de la charge de travail.
Lectures physiques élevées
Cela suggère une sollicitation plus forte du stockage.
Corrélez avec :
PAGEIOLATCH_*- la latence au niveau des fichiers
- la pression mémoire
- le comportement des scans
- les effets d’un cache froid
Écart important entre lectures logiques minimales et maximales
Cela peut indiquer une variabilité d’exécution.
Causes possibles :
- la sensibilité aux paramètres
- une distribution des données différente
- la réutilisation d’un plan pour des entrées inégales
- une cardinalité changeante
Query Store est utile dans ce cas.
Notes de production
Les lectures logiques ne sont pas des lectures disque
Cette distinction est importante.
Une lecture logique signifie que SQL Server a accédé à une page dans le buffer pool.
La page était peut-être déjà en mémoire.
Les lectures physiques signifient que SQL Server a dû charger des pages depuis le stockage.
Des lectures logiques élevées ne sont pas automatiquement un problème
Une requête d’entrepôt de données qui parcourt une grande table de faits peut légitimement lire des millions de pages.
Les questions importantes sont :
- Ce travail est-il attendu ?
- Est-il efficace ?
- Respecte-t-il le SLA ?
- Pénalise-t-il d’autres charges de travail ?
sys.dm_exec_query_stats repose sur le cache
Les lignes disparaissent lorsque les plans associés quittent le cache.
Utilisez Query Store lorsque vous avez besoin de données historiques durables.
Les statistiques de requête sont mises à jour après la fin de l’exécution
Utilisez sys.dm_exec_requests pour les requêtes actives.
Utilisez sys.dm_exec_query_stats pour les exécutions terminées présentes dans le cache.
Les effets du buffer pool comptent
Une requête peut présenter :
- des lectures logiques élevées
- des lectures physiques faibles
parce que les données sont en cache.
Cela peut tout de même consommer du CPU et de la bande passante mémoire.
Les autorisations varient selon la version
Pour un accès étendu aux 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. Assimiler les lectures logiques à des E/S disque
Ce n’est pas la même chose.
Les lectures logiques peuvent être servies entièrement depuis la mémoire.
2. Supposer que des lectures élevées signifient « index manquant »
Des lectures élevées sont un symptôme.
Le plan utilise peut-être déjà le meilleur chemin d’accès disponible pour un jeu de résultats légitimement volumineux.
3. Ne regarder que les lectures totales
Une requête à haute fréquence peut dominer les totaux tout en restant peu coûteuse par appel.
Comparez :
- les lectures totales
- les lectures moyennes
- le nombre d’exécutions
4. Ne regarder que les lectures moyennes
Un rapport rarement exécuté peut afficher une moyenne de lectures énorme tout en comptant moins qu’une requête modérée appelée des milliers de fois.
5. Ignorer la variabilité des paramètres
Une requête peut lire 100 pages pour une valeur de paramètre et 1 000 000 pour une autre.
Utilisez les valeurs min/max, Query Store et l’analyse des plans.
6. Optimiser avant de vérifier les besoins du jeu de résultats
Si la demande métier nécessite légitimement la plupart des lignes, un scan peut être le bon choix.
N’éliminez pas par l’optimisation un travail nécessaire.
7. Ignorer la fréquence des appels de l’application
Parfois, le SQL est acceptable, mais l’application l’appelle beaucoup trop souvent.
Le volume de la charge de travail compte.
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.