Reads per Execution: Ranking Queries by Average I/O

Reads per execution ranks your cached queries by what one call costs, not by what they cost in total. The two rankings often disagree. A busy little query tops the first list, and one expensive call hides in the second.

Why the top of the list misleads you

A junior DBA shows me the top of the query stats list. “This one has the most reads, so I will tune it first.” The query runs thousands of times and each call touches three pages. It looks scary in total, and there is almost nothing to fix.

The query that is actually slow for a user sits further down. It runs rarely, so its total is small. Divide total reads by executions and it jumps to the top. You need both views. Let me build a demo with three kinds of workload.

Build the workload

The demo creates a database named SqlAuthorityDemo and drops it at the end. Customer 1 owns 10,000 of the 100,000 orders. Every other customer has about 180. That skew is the whole story.

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(100) NOT NULL
);

INSERT dbo.OrderDemo (CustomerId, Notes)
SELECT CASE WHEN n <= 10000 THEN 1 ELSE n % 500 + 2 END, 'sample'
FROM (SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;

CREATE INDEX IX_OrderDemo_CustomerId ON dbo.OrderDemo (CustomerId);
GO
CREATE OR ALTER PROCEDURE dbo.GetOrdersForCustomer @CustomerId int
AS
    SELECT OrderId, Notes FROM dbo.OrderDemo WHERE CustomerId = @CustomerId;
GO
CREATE OR ALTER PROCEDURE dbo.GetOrder @OrderId int
AS
    SELECT OrderId, CustomerId, Notes FROM dbo.OrderDemo WHERE OrderId = @OrderId;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

The last line clears the plan cache for this database only. It keeps the setup statements out of our ranking. Do not run it on a production database.

Now run three workloads. The first calls one procedure for 19 ordinary customers, then for customer 1. The second does 20,000 tiny lookups. The third runs the same report as plain text for customers 1, 7 and 8. I put the rows in a table variable so SSMS does not draw thousands of grids.

SET NOCOUNT ON;
DECLARE @Customer int = 7, @Order int = 1;
DECLARE @Rows table (OrderId int, CustomerId int, Notes char(100));

WHILE @Customer < 26
BEGIN
    INSERT @Rows (OrderId, Notes) EXEC dbo.GetOrdersForCustomer @CustomerId = @Customer;
    SET @Customer += 1;
END;
INSERT @Rows (OrderId, Notes) EXEC dbo.GetOrdersForCustomer @CustomerId = 1;

WHILE @Order <= 20000
BEGIN
    INSERT @Rows EXEC dbo.GetOrder @OrderId = @Order;
    SET @Order += 1;
END;
GO
SELECT COUNT(*) AS orders FROM dbo.OrderDemo WHERE CustomerId = 1 AND Notes <> 'x';
GO
SELECT COUNT(*) AS orders FROM dbo.OrderDemo WHERE CustomerId = 7 AND Notes <> 'x';
GO 5
SELECT COUNT(*) AS orders FROM dbo.OrderDemo WHERE CustomerId = 8 AND Notes <> 'x';
GO 5

Rank two ways

First save a snapshot of the cache into a temp table. Copying it once means both rankings look at exactly the same numbers. The SUBSTRING expression cuts out the single statement using the stored offsets.

SELECT qs.query_hash, qs.execution_count, qs.total_logical_reads,
       qs.min_logical_reads, qs.max_logical_reads,
       qs.total_logical_reads * 1.0 / qs.execution_count AS reads_per_call,
       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
INTO #Stats
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%';

SELECT LEFT(statement_text, 70) AS statement, execution_count,
       total_logical_reads, reads_per_call
FROM #Stats
ORDER BY total_logical_reads DESC, statement_text;

SELECT LEFT(statement_text, 70) AS statement, execution_count,
       total_logical_reads, reads_per_call
FROM #Stats
ORDER BY reads_per_call DESC, statement_text;

On my run, the first list puts the 20,000 lookups on top: 180,000 reads in total, but only 9 per call. The second list puts the customer procedure first: 20 calls averaging about 3,500 reads each. Same cache, same moment, different winner. Use the first list when the question is overall load. Use the second when one user complains about one slow page.

Read the minimum and maximum

An average is a blend. Look at the procedure that serves customers. It was compiled for an ordinary customer, so it plans an index seek with key lookups. That plan is perfect for 180 rows and painful for 10,000.

SELECT execution_count, min_logical_reads, max_logical_reads, reads_per_call
FROM #Stats
WHERE statement_text LIKE N'%@CustomerId%';

The procedure ran 20 times. Its cheapest call read about 950 pages and its worst read about 53,000, yet the average says about 3,500. Do the arithmetic: take the worst call out of the total and the other 19 average about 950 each. So one call, the one for the big customer, is the outlier. That is the call a user remembers.

Group by query hash the right way

The three report statements differ only in the customer number, so they are three cache entries with one query_hash. To get reads per call for the whole family, add up the reads, add up the executions, then divide. Do not average the averages. The second column below shows how far off that gets.

SELECT query_hash,
       COUNT(*) AS cache_entries,
       SUM(execution_count) AS executions,
       SUM(total_logical_reads) * 1.0 / SUM(execution_count) AS weighted_reads_per_call,
       AVG(reads_per_call) AS average_of_averages
FROM #Stats
WHERE statement_text LIKE N'SELECT COUNT(*)%'
GROUP BY query_hash;

Three cache entries, 11 executions. The weighted figure is 680 reads per call. The average of averages says 887, about a third too high, because customer 1’s single call gets the same vote as ten calls for the other customers.

What resets these numbers

The counters live and die with the cached plan. A restart, a recompile or memory pressure wipes them. So treat the list as a recent sample, not a history. When you act on a ranking, save the query hash, the statement, the counts and the date, then compare after the fix.

DROP TABLE IF EXISTS #Stats;
USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Next time you look at a top-reads list, ask how many times each query ran.

A reads per execution figure is not the whole workload, it is one view of repeated cost.

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