Break Down Buffer Pool Memory by Database

calendar Jul 12, 2026 / · 8 min read · sql server sys.dm_os_buffer_descriptors buffer pool data cache memory database memory dmv performance monitoring scripts  ·
Share on: linkedin copy

sys.dm_os_buffer_descriptors is the DMV that maps SQL Server's data cache down to the individual page level — one row per 8 KB page currently held in the buffer pool, tagged with the database_id and file_id it belongs to. Rolling those rows up with a simple GROUP BY database_id turns millions of individual page entries into a short, sortable list showing exactly how much of the instance's memory each database is holding onto right now.

Purpose and Overview

On a multi-database instance, the buffer pool is shared memory — every database competes for the same cache, and SQL Server does not partition it evenly. A reporting database with a large working set can crowd out a smaller OLTP database's hot pages, and the symptom is rarely obvious from the outside: queries against the starved database simply run slower because more of their reads turn into physical I/O instead of memory hits. sys.dm_os_buffer_descriptors is the only DMV that answers "which database owns the cache" directly, because it exposes the buffer pool's contents at the level of individual pages rather than aggregate counters.

Each row in the DMV represents one page currently resident in memory: its database_id, file_id, page_id, page_type, and a handful of internal flags such as is_modified for dirty pages awaiting a checkpoint write. There is no built-in aggregation — a busy instance with tens of gigabytes of buffer pool memory can return millions of rows from a bare SELECT * FROM sys.dm_os_buffer_descriptors. The value of the DMV comes from grouping it, not from reading it row by row, and database_id is the natural grouping key for a memory-ownership report.

The script below counts pages per database_id, converts the count to megabytes using the fixed 8 KB SQL Server page size, and resolves the numeric ID to a database name — with a special case for the internal Resource Database, which does not appear in sys.databases but does hold pages in the shared buffer pool.

Code Breakdown

The query groups buffer descriptor rows by database and converts the resulting page count into a memory footprint in megabytes, ordered from the largest cache consumer down.

 1SELECT
 2    CASE bd.database_id
 3        WHEN 32767 THEN 'Resource DB'
 4        ELSE DB_NAME(bd.database_id)
 5    END                                   AS database_name,
 6    bd.database_id,
 7    COUNT(*)                              AS buffer_pages,
 8    COUNT(*) * 8 / 1024                   AS buffer_mb,
 9    CAST(COUNT(*) * 8 / 1024.0 / 1024.0 AS DECIMAL(10,2))
10                                           AS buffer_gb,
11    CAST(100.0 * COUNT(*) /
12        SUM(COUNT(*)) OVER ()             AS DECIMAL(5,2))
13                                           AS pct_of_buffer_pool
14FROM sys.dm_os_buffer_descriptors AS bd
15GROUP BY bd.database_id
16ORDER BY buffer_pages DESC;

Rolling up with GROUP BY database_id

sys.dm_os_buffer_descriptors carries no memory-size column — each row is one page, and page size is fixed at 8 KB across every edition and configuration of SQL Server. COUNT(*) grouped by database_id gives the page count per database in a single aggregation pass; multiplying by 8 and dividing by 1024 converts that count to megabytes. Because the DMV can return millions of rows on an instance with a large buffer pool, this aggregation is the entire point of the query — reading the raw rows individually would be both slow and unreadable.

Handling the Resource Database special case

database_id 32767 belongs to the Resource Database, the hidden read-only system database that holds every system object definition and is mapped into every user database's sys schema at query time. It does not appear in sys.databases and DB_NAME(32767) returns NULL, so the CASE expression substitutes a literal label rather than leaving the row blank. The Resource Database typically holds a small, stable page count relative to user databases, but excluding it silently would misrepresent the total buffer pool footprint, so the script surfaces it explicitly instead of filtering it out.

Computing percentage of total buffer pool

SUM(COUNT(*)) OVER () is a window function applied over the already-grouped result set — it sums the per-database page counts across every group to get the instance-wide total, without a second query or a self-join back to the ungrouped DMV. Dividing each database's page count by that window total and multiplying by 100 produces a percentage-of-buffer-pool column, which is often more actionable than a raw megabyte figure: a database sitting at 60% of a shared cache is a different conversation than one sitting at 6%, even if the absolute megabyte numbers look similar across different instance memory configurations.

Ordering and filtering

ORDER BY buffer_pages DESC surfaces the largest cache consumer first, which is the usual starting point for a memory-contention investigation. On an instance hosting many small databases alongside one or two large ones, adding WHERE bd.database_id NOT IN (1, 2, 3, 4) before the GROUP BY removes the four system databases (master, tempdb, model, msdb) to focus the report on user databases only — useful when the system databases' cache footprint is not part of the question being asked.

Key Benefits and Use Cases

  • Direct memory-ownership answer — no other DMV shows which database is holding how much of the shared buffer pool at the page level.
  • Contention diagnosis — when one database's queries slow down after another database's workload grows, this report confirms or rules out buffer pool crowding as the cause.
  • Capacity planning input — the percentage-of-buffer-pool column supports memory-sizing conversations when consolidating databases onto a shared instance or splitting them apart.
  • Consolidation risk assessment — before merging databases onto one instance, running this query on each source instance shows the combined cache demand they will place on the target.
  • Post-restart cache-warm tracking — comparing this report shortly after a restart against a report taken hours later shows which databases repopulate the cache fastest under production load.
  • No configuration required — the DMV is available on every edition without trace flags or special setup, just the standard VIEW SERVER STATE permission.

Performance Considerations

  • Memory-only, resets on restart: buffer descriptors reflect the current in-memory state only. A service restart, failover, or DBCC DROPCLEANBUFFERS empties the buffer pool, and the report returns near-zero counts until the cache repopulates under workload.
  • Requires VIEW SERVER STATE permission: querying sys.dm_os_buffer_descriptors requires VIEW SERVER STATE at minimum; on Azure SQL Database the equivalent scoped permission is VIEW DATABASE STATE, and the DMV's scope is limited to the current database context there rather than the full instance.
  • Row count scales with buffer pool size: on an instance configured with a large max server memory value, the DMV can return tens of millions of rows before aggregation. The GROUP BY query itself completes quickly because the aggregation happens server-side, but ad hoc unaggregated queries against this DMV on a large instance should be avoided.
  • Columnstore and in-memory OLTP pages behave differently: columnstore segments and memory-optimized tables do not use the buffer pool the same way traditional row-store pages do, so a database leaning heavily on either feature may show a smaller buffer pool footprint than its actual memory consumption would suggest — cross-check with sys.dm_os_memory_clerks for a complete memory picture.
  • NUMA-aware instances split the buffer pool internally: on multi-NUMA-node hardware, buffer pages are distributed across nodes; the query above reports the instance-wide total per database, not the per-node distribution, which matters when diagnosing NUMA-local memory pressure specifically.

Practical Tips

  • Schedule the query as a SQL Server Agent job writing to a history table at regular intervals to build a trend line of per-database buffer pool share over time, rather than relying on a single point-in-time snapshot.
  • Pair this report with sys.dm_os_memory_clerks, filtered to the MEMORYCLERK_SQLBUFFERPOOL clerk, to cross-check the buffer pool's reported total memory against the sum of pages this query returns — the two should track closely.
  • Run the query immediately before and after a suspected memory-hungry batch job or reporting query to see which database's cache share grew and which shrank, confirming whether that job is the source of cache pressure on other workloads.
  • On instances nearing max server memory, watch the percentage-of-buffer-pool column for any single database consistently holding an outsized share — that is a candidate for its own dedicated instance or a Resource Governor memory-limited resource pool.
  • After a planned memory configuration change — raising max server memory, adding physical RAM, or moving a database to faster storage — re-run the report a few hours into normal workload to confirm cache distribution settled the way expected rather than one database still starving the others.

Conclusion

sys.dm_os_buffer_descriptors grouped by database_id turns an otherwise unreadable page-level DMV into a short, ranked report of exactly which databases are consuming the shared buffer pool and by how much. It is the direct diagnostic for cache-contention questions on multi-database instances, and a useful capacity-planning input before consolidating or splitting workloads. Kept in a DBA's toolkit alongside memory clerk and wait-statistics queries, it closes the gap between "the instance feels memory-constrained" and knowing precisely which database to address first.

References

Posts in this series