Buffer Pool by Object
Overview
A low page life expectancy tells you memory is churning. It never tells you what is churning it.
The Memory report follows RAM down to the database and stops there. This page carries on to the object, and answers the question underneath every memory complaint: which table or index is actually occupying the pool, and how much of it is even resident.
Where to find it
In the tree, under a database, Real Time → Buffer Pool by Object.
The toolbar switches between This database and Whole instance, and the choice is remembered between sessions.
Requirements
| Requirement | Why |
|---|---|
VIEW SERVER STATE |
sys.dm_os_buffer_descriptors and the performance counters are server scoped. |
| An instance that has been up a while | A pool that has been warming for twenty minutes says nothing about who owns memory. |
The three numbers, and why there are three
One row per index or heap, carrying three readings that answer three different questions.
| Reading | The question it answers |
|---|---|
| Cached | Who is occupying the pool. |
| Clean against dirty | The checkpoint and recovery story. One object holding a large dirty share is why a checkpoint spikes the IO subsystem. |
| Resident | Cached pages as a share of the object’s own size. |
Residency is the interesting one, and it is the reason this report exists rather than being a column on an existing page. It separates two situations that are indistinguishable on a megabyte chart:
- A 2 GB lookup table at 100% resident, doing exactly what the buffer pool is for.
- A 400 GB fact table at 4% resident, being scanned and evicted nightly, taking everything else with it on the way out.
Both are large in the pool. Only one of them is a problem, and a chart that ranks by megabytes alone puts them side by side and says nothing.
Reading the chart
The long bar is cached bytes, on a shared scale across every row, split into clean and dirty. The gauge beside it is residency, on its own fixed 0 to 100% scale.
Two scales rather than one because the two numbers are not comparable. Drawing residency on the same axis as bytes would make a fully resident small table look like nothing at all, which is the reading this page is trying to make visible.
The verdicts
| Verdict | When | What it means |
|---|---|---|
| Churn | Under 25% resident | Large in the pool and barely any of it, so it is being read and evicted rather than kept. |
| Partial | 25% to 85% resident | A range scan reaching cold pages. A candidate for partitioning, or for a narrower index. |
| Healthy | Over 85% resident | Hot and resident. This is what the buffer pool is for. |
| LOB | Mostly off row content | Counted through the partition_id arm of the join, which is the arm most versions of this query miss. |
| Minor | Too small to matter | Its residency percentage is arithmetically true and says nothing useful. |
A Churn row on a heap says so explicitly, because on a heap every read is a scan and that is usually the whole finding.
Reading the grid
| Column | What it is |
|---|---|
| Verdict | The band, in the chart’s colour. |
| Object | Schema, table and index. Prefixed with the database in instance scope. |
| Type | Clustered, nonclustered, heap, columnstore. |
| Cached | Bytes of this object in the pool right now. |
| Object size | What the object occupies on disk. |
| Resident | Cached against object size. |
| Dirty | Share of the cached pages that are modified and not yet written. |
| Reading | The sentence that names the next move rather than the symptom. |
More memory is usually not the answer
The fix behind a Churn row is almost never more RAM. It is an index that turns a scan into a seek, a nightly report that should be running against a secondary, or a table that should be partitioned or archived.
The page validates the opposite conclusion just as well, which is the more useful half in a budget conversation: if the pool is full of the right objects at high residency and page life expectancy is still low, then more memory genuinely is the answer, and now it can be proved rather than asserted.
Four things this query gets wrong if it is written casually
All four are handled here. Three of them are stated on the page itself.
1. sys.allocation_units is database scoped. sys.dm_os_buffer_descriptors is instance wide. Joining them without switching database context silently attributes other databases’ pages to objects in this one. The instance scope therefore aggregates the pool once and then resolves names database by database, skipping any that is offline or unreachable rather than failing the whole page. The number skipped is stated in the banner.
2. The container join has two arms. IN_ROW_DATA and ROW_OVERFLOW_DATA join container_id to hobt_id; LOB_DATA joins it to partition_id. Writing only the first arm quietly drops every LOB page, which on a database with LOB columns is a large share of the pool. Both arms are here, and the footer says so.
3. Residency can legitimately exceed one hundred per cent. Pages left in the pool after rows were deleted push the raw ratio past 100. The denominator is widened to used_page_count, which covers the same allocation units the numerator counts, and what is left is capped and footnoted rather than shipped as a raw 143%.
4. Cost scales with pool size. One descriptor row per 8 KB page means a 256 GB pool is around 32 million rows. Everything is aggregated server side, no page row ever reaches the client, and the page states the pool size it is about to scan before it scans it.
Right-click actions in the grid
| Action | What it does |
|---|---|
| Go to Memory | The instance level pool total this page divides up. |
| Go to LOB Columns | On a LOB row, where the off row content actually is. |
| Copy object name | Fully qualified, ready to paste into a query. |
| Copy the query behind this report | The whole batch, to run and adapt yourself. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.dm_os_buffer_descriptors |
One row per cached page, with its database, allocation unit and dirty flag. |
sys.allocation_units, sys.partitions |
The two arm join from a cached page back to an index. |
sys.indexes, sys.objects, sys.schemas |
Names. |
sys.dm_db_partition_stats |
The object’s own size, which is the residency denominator. |
sys.dm_os_performance_counters |
Total pages and page life expectancy. |
sys.dm_os_sys_info |
Instance start time, so a warming pool can be flagged as one. |
System objects are excluded, and so are master, model, msdb and tempdb.
Messages you may see
The instance started N hours ago, so the pool is still warming. Under four hours of uptime, none of these figures mean much yet. This is the reason the instance start time is read at all.
The pool holds N page descriptors. On a large pool, every reading on this page costs one pass over all of them. The pass is done on the server, but it is not free, and the page says so before you refresh it again.
N databases could not be read and are not in these totals. Instance scope only. A database that goes offline or that this login cannot reach is skipped rather than failing the page, and the count is the hole in the coverage.
The buffer pool counters could not be read. Usually a permissions problem rather than an empty pool.
No object in this database has pages in the buffer pool. Nothing has read from it since the instance started.
Related reports
| Report | Why you would go there |
|---|---|
| Memory | The pool total this page divides up, and where a low page life expectancy is first seen. |
| Index Usage | The usual cause of a Churn row is a scan that should have been a seek. |
| Missing Indexes | The index that would turn that scan into a seek. |
| Table Size | What the object is on disk, which is the residency denominator. |
| Page Reads | The reads that are pulling those pages in. |
Frequently asked questions
Why is residency capped at 100%? Because the raw ratio can exceed it. Pages belonging to rows that were deleted stay in the pool until they are evicted, so the numerator can outrun a denominator taken from current row counts. The denominator is already widened to used_page_count to cover the same allocation units; what is left over is capped and footnoted rather than presented as a real reading.
Why does the instance scope take longer than the database scope? Because it resolves names database by database, in database context, which is the only way to avoid attributing one database’s pages to another’s objects. The pool itself is still scanned only once.
An object I know is large is not on the list. Why? It has no pages in the pool, or it fell below the cut. The footer says how many objects are shown and what share of the in scope pages they account for.
Is running this safe on a busy production instance? It is one aggregating pass over the buffer descriptors. On a very large pool that pass is not free, which is why the descriptor count is stated in the banner rather than left for you to discover.
Why are tempdb and the system databases missing? They are excluded deliberately. tempdb in particular is always near the top of an unfiltered version of this list and never tells you anything actionable about your own schema.