Why Reads Matter Most When Tuning T-SQL: Logical Reads, STATISTICS IO and Indexes

When trying to speed up a query, the measure people look at most is usually duration. But duration is unreliable: the same query takes a different time on every run depending on the server’s current load, whether the data is in memory and other queries’ locks. There’s a more stable and more revealing measure: the number of pages the query reads.

SQL Server reads data in 8 KB pages. The number of pages a query reads directly determines the CPU it spends, the memory it uses and, when memory runs short, the disk access it causes. This article shows how to measure reads, the difference between logical and physical reads, and how to speed up queries by reducing reads.

Measuring Reads: STATISTICS IO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT OrderNo, Amount
FROM dbo.Orders
WHERE CustomerId = 1001;

On SSMS’s Messages tab, you’ll see a line like this for each table:

• logical reads: the number of pages the query read from memory (the buffer pool). This is the main measure for tuning.
• physical reads and read-ahead reads: pages read from disk because they weren’t in memory. They usually drop to zero the second time the same query runs, so they shouldn’t be used on their own for comparison.
• Scan count: how many times the table or index was accessed.

In the example above, about 48,000 pages, or 375 MB of data, were read from memory to find a single customer’s orders. That most likely means there’s no index on CustomerId and the whole table was scanned.

Why Logical Reads?

• It’s stable: with the same data and the same plan, it gives the same number every run, unaffected by the server’s current load. That makes it ideal for before/after comparisons.
• It tracks CPU: processing every page read costs CPU. Reducing reads usually lowers CPU too.
• It protects memory: pages read unnecessarily fill the buffer pool and push out data other queries need, which then causes their disk reads.
• It reaches the disk: when memory isn’t enough, part of the logical reads turns into physical reads; the disk is the slowest layer in the system.

Ways to Reduce Reads

1. The right index

A suitable index on filter and join columns lets SQL Server go straight to the relevant pages (an index seek) instead of scanning the whole table. If the columns the query returns are added to the index with INCLUDE, the need to go back to the base table (a key lookup) disappears too:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId)
INCLUDE (OrderNo, Amount);

After this index, the same query’s reads usually drop from tens of thousands to single or double digits. See our index guide for index design details.

2. Only the columns you need

SELECT * makes it impossible for an index to cover all columns and can cause unnecessary LOB reads. Select the columns the application actually uses.

3. SARGable predicates

Wrapping a column in a function (for example YEAR(OrderDate) = 2026) stops the index from being used and forces a scan. Details are in our date math in the WHERE clause article.

4. Up-to-date statistics

The optimizer estimates how many rows will come back from statistics. Stale statistics lead to wrong plan choices, such as a scan for a few rows or nested loops for many rows, and unnecessary reads.

5. Set-based work instead of row by row

Running a separate query for every row in a cursor or loop reads the same pages over and over. A single set-based statement does the same work with far fewer reads.

Finding the Queries with the Most Reads

SELECT TOP (20)
       qs.total_logical_reads,
       qs.execution_count,
       qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
       SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
                 ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
                   ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_logical_reads DESC;

For a broader approach, see our guide to finding slow queries.

Checklist

• Logical reads are measured with STATISTICS IO before and after tuning.
• Comparisons use logical reads and CPU time instead of duration.
• Filter and join columns have suitable indexes; needed columns are covered with INCLUDE.
• SELECT * and non-SARGable predicates are cleaned up.
• The queries with the most reads are reviewed regularly.