Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: PerformanceDifficulty: IntermediateUpdated

Find High Read Queries

Identify SQL Server queries performing the most logical and physical reads, separate read-heavy workload from CPU-heavy workload, and choose the right next tuning step.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Find active requests doing the most logical reads
  5. 2. Show the currently executing statement inside the batch
  6. 3. Find cached queries by total logical reads
  7. 4. Find queries with the highest average logical reads
  8. 5. Find high-frequency read consumers
  9. 6. Group logically equivalent queries by query hash
  10. 7. Show execution plan for read-heavy cached queries
  11. 8. Compare logical reads with physical reads
  12. 9. Use Query Store for historical read-heavy queries
  13. 10. Check whether high reads are concentrated on one table
  14. What the result means
  15. What to look for
  16. High total logical reads + high execution count
  17. High average logical reads + low execution count
  18. High logical reads + high CPU
  19. High logical reads + low CPU
  20. High physical reads
  21. Large difference between min and max logical reads
  22. Production notes
  23. Logical reads are not disk reads
  24. High logical reads are not automatically bad
  25. sys.dm_exec_query_stats is cache-based
  26. Query stats update after execution completes
  27. Buffer pool effects matter
  28. Permissions vary by version
  29. Common mistakes
  30. 1. Treating logical reads as disk I/O
  31. 2. Assuming high reads mean “missing index”
  32. 3. Looking only at total reads
  33. 4. Looking only at average reads
  34. 5. Ignoring parameter variability
  35. 6. Optimizing before checking result-set requirements
  36. 7. Ignoring application call frequency
  37. Next step
  38. Related deep dives

Problem

SQL Server is doing too much I/O, buffer pool pressure is rising, storage latency is visible, or some queries are touching far more pages than expected.

The goal is to answer:

  • Which queries are doing the most logical reads?
  • Which active requests are read-heavy right now?
  • Which cached queries are responsible for the most total read work?
  • Which queries are expensive per execution?
  • Are high reads caused by scans, poor predicates, join strategy, or simply legitimate large workloads?
  • Is the problem logical I/O, physical I/O, or both?

Quick answer

For active requests, start with 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;

For recent completed workload, use 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;

Use logical reads as evidence of work done against pages in memory. Do not treat high logical reads as automatic proof of a missing index.

Code / commands

1. Find active requests doing the most logical reads

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;

Use this during an active incident.

High logical reads often point toward large scans, repeated access, or inefficient access paths.


2. Show the currently executing statement inside the batch

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;

This is more useful than reviewing the full stored procedure when only one statement is causing the read pressure.


3. Find cached queries by total logical reads

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;

Use this when the question is:

Which cached workload has touched the most pages overall?


4. Find queries with the highest average logical reads

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;

Use this to find individually expensive executions.

Always keep execution_count in view.


5. Find high-frequency read consumers

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;

The 100 filter is only an example.

Adjust it to the workload.

A moderately read-heavy query can become a major resource consumer if it is called frequently.


6. Group logically equivalent queries by query hash

Ad hoc literals can fragment the plan cache.

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;

Use this when many similar ad hoc statements make the workload look smaller than it really is.


7. Show execution plan for read-heavy cached queries

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;

Look for:

  • large table/index scans
  • repeated nested-loop lookups
  • broad joins
  • weak predicates
  • row-estimate problems
  • wide row movement
  • missing or ineffective indexes

Do not decide from one operator alone.


8. Compare logical reads with physical reads

Logical reads count page accesses satisfied from the buffer pool.

Physical reads count pages that had to be read from storage.

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;

Interpret carefully:

  • high logical reads + low physical reads → lots of in-memory page work
  • high physical reads → storage had to satisfy more reads
  • both high → query may be both inefficient and storage-intensive

9. Use Query Store for historical read-heavy queries

If the workload is no longer in cache, Query Store is the better historical source.

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;

Use this for:

  • regression analysis
  • before/after comparisons
  • plan changes
  • queries that are no longer in plan cache

10. Check whether high reads are concentrated on one table

Once a query is identified, inspect the execution plan first.

Then review table/index size and access path for the target objects.

For a quick table-size check:

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;

Large scans against large tables may be completely expected.

The decision depends on selectivity and business intent.

What the result means

Field Why it matters
logical_reads Number of 8 KB pages accessed from the buffer pool by an active request.
reads Physical reads performed by an active request.
total_logical_reads Cumulative logical reads represented by a cached query-stats row.
total_physical_reads Cumulative physical reads represented by a cached query-stats row.
avg_logical_reads Derived average reads per execution. Useful with execution count.
max_logical_reads Highest logical-read count observed for one execution in the current cache lifecycle.
min_logical_reads Lowest logical-read count observed. Useful for spotting skew.
query_hash Helps aggregate logically equivalent statements.
execution_count Number of completed executions represented by the stats row.

What to look for

High total logical reads + high execution count

This often indicates a frequently executed hot path.

Potential next steps:

  • reduce call frequency
  • improve access path
  • cache repeated results
  • batch requests
  • optimize repeated joins

High average logical reads + low execution count

Likely an individually expensive query.

Inspect:

  • scan size
  • predicates
  • join order
  • cardinality estimates
  • index coverage
  • data volume

High logical reads + high CPU

This often points to expensive row/page processing.

The plan may be reading and processing far more data than necessary.

High logical reads + low CPU

Possible causes:

  • efficient large scans
  • waits masking CPU
  • data already cached
  • simple but broad access pattern

Do not assume it is harmless or problematic without workload context.

High physical reads

This suggests storage is being used more heavily.

Correlate with:

  • PAGEIOLATCH_*
  • file-level latency
  • memory pressure
  • scan behavior
  • cold cache effects

Large difference between min and max logical reads

This can indicate execution variability.

Possible causes:

  • parameter sensitivity
  • different data distribution
  • plan reuse across uneven inputs
  • changing cardinality

Query Store is useful here.

Production notes

Logical reads are not disk reads

This distinction matters.

A logical read means SQL Server accessed a page in the buffer pool.

The page may already have been in memory.

Physical reads mean SQL Server had to bring pages in from storage.

High logical reads are not automatically bad

A data warehouse query scanning a large fact table may legitimately read millions of pages.

The important questions are:

  • Is the work expected?
  • Is it efficient?
  • Is it within SLA?
  • Is it crowding out other workloads?

sys.dm_exec_query_stats is cache-based

Rows disappear when related plans leave cache.

Use Query Store when you need durable historical evidence.

Query stats update after execution completes

Use sys.dm_exec_requests for active requests.

Use sys.dm_exec_query_stats for completed cached executions.

Buffer pool effects matter

A query may have:

  • high logical reads
  • low physical reads

because data is cached.

That can still consume CPU and memory bandwidth.

Permissions vary by version

For broad access to performance DMVs:

  • SQL Server 2019 and earlier commonly require VIEW SERVER STATE
  • SQL Server 2022 and later commonly require VIEW SERVER PERFORMANCE STATE

Azure SQL Database has different scope and permission rules.

Common mistakes

1. Treating logical reads as disk I/O

They are not the same.

Logical reads can be served entirely from memory.

2. Assuming high reads mean “missing index”

High reads are a symptom.

The plan may already use the best available access path for a legitimately large result set.

3. Looking only at total reads

A high-frequency query may dominate totals while still being cheap per call.

Compare:

  • total reads
  • average reads
  • execution count

4. Looking only at average reads

One rare report can have enormous average reads and still be less important than a moderate query called thousands of times.

5. Ignoring parameter variability

A query may read 100 pages for one parameter and 1,000,000 for another.

Use min/max, Query Store, and plan analysis.

6. Optimizing before checking result-set requirements

If the business request legitimately needs most rows, a scan may be correct.

Do not optimize away necessary work.

7. Ignoring application call frequency

Sometimes the SQL is acceptable, but the application calls it far too often.

Workload volume matters.

Next step

Use the read pattern to choose the next investigation.

  • High reads + high CPU → inspect execution plan, scans, joins, predicates, and estimates.
  • High physical reads + PAGEIOLATCH_* → inspect storage latency, memory pressure, and access paths.
  • High total reads + high execution count → inspect application call frequency and hot-path design.
  • High average reads + low execution count → inspect the individual query and plan.
  • Large min/max spread → investigate parameter sensitivity and plan stability.
  • Similar ad hoc queries → group by query_hash and inspect parameterization.
  • Current issue already passed → use Query Store.
  • Reads are high but expected → confirm the workload is within SLA before tuning.

← All cheat sheets