Parcourir les aide-mémoire · SQL Server

Technologie: SQL ServerCatégorie: PerformanceNiveau: IntermédiaireMis à jour

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
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Trouver les requêtes actives qui effectuent le plus de lectures logiques
  5. 2. Afficher l’instruction en cours d’exécution dans le lot
  6. 3. Trouver les requêtes en cache par lectures logiques totales
  7. 4. Trouver les requêtes avec la moyenne de lectures logiques la plus élevée
  8. 5. Trouver les consommateurs de lectures à haute fréquence
  9. 6. Regrouper les requêtes logiquement équivalentes par query hash
  10. 7. Afficher le plan d’exécution des requêtes en cache à forte lecture
  11. 8. Comparer les lectures logiques et les lectures physiques
  12. 9. Utiliser Query Store pour l’historique des requêtes à forte lecture
  13. 10. Vérifier si les lectures élevées se concentrent sur une seule table
  14. Interpréter le résultat
  15. Points à vérifier
  16. Lectures logiques totales élevées + nombre d’exécutions élevé
  17. Lectures logiques moyennes élevées + faible nombre d’exécutions
  18. Lectures logiques élevées + CPU élevé
  19. Lectures logiques élevées + CPU faible
  20. Lectures physiques élevées
  21. Écart important entre lectures logiques minimales et maximales
  22. Notes de production
  23. Les lectures logiques ne sont pas des lectures disque
  24. Des lectures logiques élevées ne sont pas automatiquement un problème
  25. sys.dm_exec_query_stats repose sur le cache
  26. Les statistiques de requête sont mises à jour après la fin de l’exécution
  27. Les effets du buffer pool comptent
  28. Les autorisations varient selon la version
  29. Erreurs fréquentes
  30. 1. Assimiler les lectures logiques à des E/S disque
  31. 2. Supposer que des lectures élevées signifient « index manquant »
  32. 3. Ne regarder que les lectures totales
  33. 4. Ne regarder que les lectures moyennes
  34. 5. Ignorer la variabilité des paramètres
  35. 6. Optimiser avant de vérifier les besoins du jeu de résultats
  36. 7. Ignorer la fréquence des appels de l’application
  37. Étape suivante
  38. 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.

Étape suivante

Utilisez le profil de lecture pour choisir la prochaine investigation.

  • Lectures élevées + CPU élevé → examinez le plan d’exécution, les scans, les jointures, les prédicats et les estimations.
  • Lectures physiques élevées + PAGEIOLATCH_* → examinez la latence du stockage, la pression mémoire et les chemins d’accès.
  • Lectures totales élevées + nombre d’exécutions élevé → examinez la fréquence des appels de l’application et la conception du chemin critique.
  • Lectures moyennes élevées + faible nombre d’exécutions → examinez la requête concernée et son plan.
  • Écart min/max important → analysez la sensibilité aux paramètres et la stabilité des plans.
  • Requêtes ad hoc similaires → regroupez par query_hash et examinez le paramétrage.
  • Le problème en cours est déjà passé → utilisez Query Store.
  • Les lectures sont élevées mais attendues → vérifiez que la charge de travail respecte le SLA avant d’optimiser.

← Tous les aide-mémoire