Page Reads and Page Writes by Database

Overview

Page Reads by Database and Page Writes by Database are the same report against two different numbers. Each is a pie over a three column grid, splitting logical page traffic by the database the plan belonged to, and each drills into the matching database level report.

  • Page Reads by Database – logical page reads, the pages queries touched in the buffer pool.
  • Page Writes by Database – logical page writes, the pages queries dirtied.
Page reads split by database
Logical page traffic per database, from the heaviest cached plans.

These are logical pages, not disk IO. A logical read is a page the query touched, whether it came from memory or from disk. That makes these reports a measure of how much work the queries are doing, which is the number you can change by tuning a query, rather than a measure of what the storage is being asked for. For the disk side, use I/O by Database and I/O by Drive.

Where to find it

Route How
Server Overview page The Page Reads and Writes panel – click either half of the chart
Performance Dashboard The page reads and page writes panels

The page titles read Page Reads By Database and Page Writes By Database.

Requirements

  • A connection to the instance, and permission to read the plan cache statistics.
  • Nothing is installed on the monitored instance and nothing is stored.

Reading the report

Logical page traffic per database
One row per database, largest first.
Column What it is
Row Row number, largest first.
Database The database the cached plans belonged to.
Reads / Writes Total logical pages read or written by those plans since each was compiled.

Databases with a zero total are left out entirely rather than shown as an empty slice.

Gesture Result
Click a slice Opens Page Reads by Query or Page Writes by Query for that database
Double-click a grid row The same

The Top 1000 caveat

These totals come from the thousand heaviest cached plans, not from every plan in the cache. That is deliberate – it keeps the report fast on an instance with an enormous plan cache, and the tail below the top thousand is almost never what you are looking for. But it does mean:

  • A database whose work is spread very thinly across a great many cheap plans can be understated, because few of its plans reach the top thousand.
  • The totals are not a complete accounting of instance IO and should not be reconciled against one.

The report is for ranking databases against each other, which it does well, rather than for producing an absolute figure.

The plan cache caveat

Like every plan cache report, these totals are cumulative since each plan compiled and they reset when a plan is evicted or the service restarts. Shortly after a restart the numbers are small and the ranking is unreliable. On an instance under memory pressure, plans are evicted often enough that the effective history is much shorter than the uptime suggests.

How to read the report

  1. Read the two reports together. A database at the top of reads and nowhere on writes is a reporting workload; one at the top of both is a busy transactional database.
  2. Compare the ranking against database size. A small database doing the most reads on the instance is usually a missing index rather than a lot of data.
  3. Click through to Page Reads by Query for the top database, which is where the individual statements are.
  4. Cross-check against I/O by Database. High logical reads with low physical IO is a database that fits in memory and is simply doing too much work. High logical reads and high physical IO is a database that does not fit.

Common patterns

One database is nearly the whole reads pie. Normal for a single-application instance. Drill in and look at the top statements before assuming anything is wrong.

A small database at the top of reads. Almost always a table scan where a seek belongs. Missing Indexes and Table Use for that database are the next two stops.

High writes on a database that should be read only. Something is writing. Check for a job, an ETL process, or logging tables in a database nobody thinks of as transactional.

Everything small and evenly spread. Either a genuinely balanced instance, or the cache was recently emptied. Check the reports again after the instance has been up for a while.

Where the data comes from

The cached plan statistics, attributed to a database through the plan attributes, taking the thousand heaviest plans for the metric in question and summing them per database. Nothing is stored.

Report Why you would go there
Page Reads by Query / Page Writes by Query The drill down – the individual statements in one database.
I/O by Database The physical side: what the storage was actually asked for.
I/O by Drive Which volume carries it.
Stored Procs by Logical Writes The write side by module rather than by statement.
Buffer Pool by Object Which objects are occupying the memory the reads are hitting.
Missing Indexes The usual cause of a small database at the top of the reads list.

Frequently asked questions

Are these disk reads? No. They are logical pages, touched in the buffer pool, whether or not they came from disk. For disk, use I/O by Database.

Why do the totals not add up to my instance IO figures? Because they cover the thousand heaviest cached plans, and only since each was compiled. They rank databases; they do not account for everything.

Why did the numbers drop? Plans were evicted, or the service restarted. Plan cache totals reset with the plans they belong to.

Why is a database missing? Either its total was zero, or none of its plans reached the top thousand for that metric.