Physical Reads per Query: Finding What Drives Disk I/O

Physical reads tell you which queries made SQL Server fetch pages from storage. The catch is that the biggest total and the biggest cost per call are often two different queries. Look at both.

The storage team wants a query name

Here is a call most of us have had. The storage team says the disks were busy at 9 AM. They ask which query did it. A graph of disk throughput cannot answer that. It has no query names on it.

SQL Server keeps a counter for every cached statement: how many pages it had to read from storage. That counter is physical reads, and it lives in sys.dm_exec_query_stats. Let me build a small demo so you can see how to read it.

Build a table and start with a cold cache

The demo creates a database named SqlAuthorityDemo and drops it at the end. The table has 100,000 rows, a clustered key on OrderId and a narrow index on CustomerId. The procedure does a simple lookup by key.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.OrderDemo
(
    OrderId    int IDENTITY(1,1) PRIMARY KEY,
    CustomerId int NOT NULL,
    Notes      char(200) NOT NULL
);
CREATE INDEX IX_OrderDemo_CustomerId ON dbo.OrderDemo (CustomerId);

INSERT dbo.OrderDemo (CustomerId, Notes)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 500 + 1, 'sample'
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS b;
GO
CREATE OR ALTER PROCEDURE dbo.GetOrder @OrderId int
AS
    SELECT OrderId, CustomerId, Notes FROM dbo.OrderDemo WHERE OrderId = @OrderId;
GO

Physical reads only appear when the pages are not already in memory. To get a cold cache for this one database, take it offline and bring it back. That clears only this database from the buffer pool. Do this on a test server. Never flush caches on production to make a point.

CHECKPOINT;
USE master;
ALTER DATABASE SqlAuthorityDemo SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE SqlAuthorityDemo SET ONLINE;
GO
USE SqlAuthorityDemo;
GO

Run two very different workloads

The first workload is one report. It counts orders per customer and scans the whole CustomerId index once. The second is a busy lookup: 1,000 calls to the procedure, each asking for a different order.

SELECT TOP (3) CustomerId, COUNT(*) AS orders
FROM dbo.OrderDemo
GROUP BY CustomerId
ORDER BY orders DESC, CustomerId;
GO
DECLARE @n int = 1, @id int;
DECLARE @Result table (OrderId int, CustomerId int, Notes char(200));
WHILE @n <= 1000
BEGIN
    SET @id = (@n * 97) % 100000 + 1;
    INSERT @Result EXEC dbo.GetOrder @OrderId = @id;
    SET @n += 1;
END;
GO

I collect the rows in a table variable, so SSMS does not draw 1,000 result grids. We only care about the counters the loop leaves behind.

Rank by total physical reads

This is the query most people write first. The SUBSTRING expression cuts out the one statement from its batch, using the offsets stored in the DMV.

SELECT SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,
         (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(t.text)
               ELSE qs.statement_end_offset END
          - qs.statement_start_offset) / 2 + 1) AS statement_text,
       qs.execution_count, qs.total_physical_reads, qs.total_logical_reads,
       qs.total_physical_reads * 1.0 / qs.execution_count AS physical_reads_per_call
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE t.text LIKE N'%OrderDemo%'
  AND t.text NOT LIKE N'%dm_exec_query_stats%'
ORDER BY qs.total_physical_reads DESC, qs.statement_start_offset;

The lookup wins. It ran 1,000 times, and each call fetched a page or less, but the sum is larger than the single report. On my run the lookup total was several times the report total. Yours will differ.

Rank by cost per call

Now change only the ORDER BY. Sort by physical_reads_per_call and the order flips. The report fetches a lot of pages in one go, while a lookup fetches under one page per call on average.

SELECT SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,
         (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(t.text)
               ELSE qs.statement_end_offset END
          - qs.statement_start_offset) / 2 + 1) AS statement_text,
       qs.execution_count, qs.total_physical_reads, qs.total_logical_reads,
       qs.total_physical_reads * 1.0 / qs.execution_count AS physical_reads_per_call
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE t.text LIKE N'%OrderDemo%'
  AND t.text NOT LIKE N'%dm_exec_query_stats%'
ORDER BY physical_reads_per_call DESC, qs.statement_start_offset;

Neither ranking is wrong. If users complain about one slow report, use the per-call view. If the storage team complains about steady load, use the total view.

Why a second run looks cheaper

Run the report again. This time its pages are already in memory, so it reads nothing from storage. Look at the counters afterward: execution_count goes to 2, total_logical_reads doubles, and total_physical_reads stays put. The per-call average drops by half without any tuning.

SELECT TOP (3) CustomerId, COUNT(*) AS orders
FROM dbo.OrderDemo
GROUP BY CustomerId
ORDER BY orders DESC, CustomerId;
GO
SELECT qs.execution_count, qs.total_physical_reads, qs.total_logical_reads,
       qs.total_physical_reads * 1.0 / qs.execution_count AS physical_reads_per_call
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE t.text LIKE N'%GROUP BY CustomerId%'
  AND t.text NOT LIKE N'%dm_exec_query_stats%';

This is the mistake I see most often. Someone tunes a query, runs it twice, and celebrates because physical reads went down. The cache did that, not the tuning. Compare logical reads before and after a change, and treat physical reads as a hint about your workload.

Know what these counters cannot tell you

The numbers vanish when a plan leaves the cache, and they reset when the server restarts. They are totals, not a rate per second. To see a recent period, save two snapshots with the plan_handle and compare only rows that survive in both.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Next time someone asks which query hit the disks, ask which of the two questions they mean.

A physical read count is not a disk speed, it is cached work.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.