Browse cheat sheets · SQL Server
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
- Problem
- Quick answer
- Code / commands
- 1. Find active requests doing the most logical reads
- 2. Show the currently executing statement inside the batch
- 3. Find cached queries by total logical reads
- 4. Find queries with the highest average logical reads
- 5. Find high-frequency read consumers
- 6. Group logically equivalent queries by query hash
- 7. Show execution plan for read-heavy cached queries
- 8. Compare logical reads with physical reads
- 9. Use Query Store for historical read-heavy queries
- 10. Check whether high reads are concentrated on one table
- What the result means
- What to look for
- High total logical reads + high execution count
- High average logical reads + low execution count
- High logical reads + high CPU
- High logical reads + low CPU
- High physical reads
- Large difference between min and max logical reads
- Production notes
- Logical reads are not disk reads
- High logical reads are not automatically bad
- sys.dm_exec_query_stats is cache-based
- Query stats update after execution completes
- Buffer pool effects matter
- Permissions vary by version
- Common mistakes
- 1. Treating logical reads as disk I/O
- 2. Assuming high reads mean “missing index”
- 3. Looking only at total reads
- 4. Looking only at average reads
- 5. Ignoring parameter variability
- 6. Optimizing before checking result-set requirements
- 7. Ignoring application call frequency
- Next step
- 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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.