Page Reads and Page Writes by Query

Overview

These are two reports with one implementation: Page Reads by Query and Page Writes by Query. They differ only in which column of sys.dm_exec_query_stats they sum and what the labels say.

Like CPU by Query, the chart is a scatter – executions against per-execution volume

  • because the question is not what is the biggest number but what kind of problem is it. A statement reading a great deal once is a scan; one reading a little ten thousand times is a chatty application. Those need different fixes.

Volume is shown in bytes

A logical read is an 8 KB page. The old grid printed the raw count, so a reader had to know that to work out that 1,204,338 logical reads is 9 GB of buffer pool traffic. The volume is converted now.

What this fixes

  • Both reports were a pie chart with one slice per row over a three-column grid of Row, Total, Query.
  • The grid gave no executions, so it could not distinguish a scan from a chatty application.
  • No unit, so the numbers meant nothing without knowing the page size.
  • The two reports were separate ninety-line classes differing only in the metric, which is how the writes report ended up filtering on total_logical_reads > 0 and quietly returning a handful of rows instead of two hundred.

That last one is worth dwelling on: the Page Writes report was filtering on reads, so it silently omitted most of its own answer. Everything except the metric now lives in one place and the two reports are a few properties each.

Where to find them

Database level reports. Expand the server → expand the database → Real Time → Page Reads by Query, or Page Writes by Query beside it.

The two are the same report reading a different column, which is why one page documents both.

The Page Reads by Query report
The whole report.
The executions versus per-execution volume scatter
A scan and a chatty application land in different places.

Reading the grid

The page reads by query grid
Volume is the number that means something.
Column What it is
Pattern The scatter quadrant – the same four-way classification the chart uses.
Total Pages Logical reads or writes, as a page count.
Volume The same in bytes. This is the number that means something.
Executions How many times it ran.
Per Execution Volume per run – the axis that separates a scan from a chatty call.
Last Execution When it last ran.
Object Object name where there is one.
Query The statement text.

The toolbar

Button What it does
Top 25 · Top 50 · Top 200 · Top 500 How many statements.
Refresh Reload now.

The plan cache caveat

These counters are cumulative for as long as each plan stays in cache, so the totals are since each plan compiled – not since any common point – and they reset on eviction or restart. Two rows in the same list may cover different windows.

How to read the report

  1. Look at Per Execution first. A high value is a scan; a low value with high executions is a chatty application.
  2. Read Volume, not Total Pages. Gigabytes are comparable to your buffer pool; page counts are not.
  3. Check Last Execution. A large total from something that stopped running is history.
  4. On the reads report, high volume usually means a missing index or a query that cannot seek.
  5. On the writes report, high volume usually means a bulk operation or an over-indexed table.

Common patterns

One statement reading gigabytes per execution. A scan. Check Missing Indexes for the table involved.

Modest per-execution volume, enormous execution count. A chatty application. Tuning the statement will not help much; calling it less will.

A write-heavy statement on a table with many indexes. Every index multiplies the write. Check Unused Indexes and Duplicate Indexes before tuning the statement.

Far more rows on the writes report than you remember. That is the bug fix – it used to filter on reads and return a fraction of its answer.

Report Why you would go there
CPU by Query The same statements, ranked by CPU rather than pages.
I/O by Database Where that traffic lands on storage.
Missing Indexes The usual fix for a heavy reader.
Unused Indexes The usual fix for a heavy writer.

Frequently asked questions

What is a logical read? An 8 KB page read from the buffer pool. It is not physical I/O – a page read many times may have reached disk once.

Why show bytes as well as pages? Because pages mean nothing without knowing the page size. Bytes are directly comparable to your buffer pool.

Why did Page Writes by Query used to show so few rows? It filtered on total_logical_reads > 0 – the wrong column. A write-only statement was excluded from the writes report.

Are these totals since the server started? No – since each plan was compiled.