Memory Used by Each Table in the SQL Server Buffer Pool

Memory used by each table is the number of its pages in the buffer pool, multiplied by 8 KB. One query over sys.dm_os_buffer_descriptors adds it up for every table in a database. One more join shows how much of each table is cached.

What the Query Adds Up

The buffer pool holds copies of data pages, and every page is 8 KB. So 128 pages make 1 MB. The view sys.dm_os_buffer_descriptors lists the pages by allocation unit, not by table. The query joins each page to its partition and then to the table. The join has one arm for row data and one for large values. The post Buffer Pool Pages for One Table in SQL Server explains that join.

The query also reads sys.dm_db_partition_stats. That view knows how many pages each table owns on disk. Dividing the cached pages by the owned pages shows how much of the table is in memory right now. That share is the memory used by each table, set against its size.

Build Three Tables of Different Sizes

The demo creates a database named BufferTotalsDemo with three tables. The tea list has 40 rows. The order table has 20,000 rows. The archive has 80,000 wider rows. Automatic statistics are off, because compiling a query can read a whole table to build them. That would fill the pool before the workload starts.

IF DB_ID(N'BufferTotalsDemo') IS NULL CREATE DATABASE BufferTotalsDemo;
GO
ALTER DATABASE BufferTotalsDemo SET AUTO_CREATE_STATISTICS OFF;
ALTER DATABASE BufferTotalsDemo SET AUTO_UPDATE_STATISTICS OFF;
GO
USE BufferTotalsDemo;
GO
SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.Teas, dbo.TeaOrders, dbo.TeaArchive;
CREATE TABLE dbo.Teas (TeaID int NOT NULL CONSTRAINT PK_Teas PRIMARY KEY, TeaName varchar(50) NOT NULL);
CREATE TABLE dbo.TeaOrders (OrderID int NOT NULL CONSTRAINT PK_TeaOrders PRIMARY KEY, TeaID int NOT NULL, Padding char(100) NOT NULL DEFAULT 'x');
CREATE TABLE dbo.TeaArchive (OrderID int NOT NULL CONSTRAINT PK_TeaArchive PRIMARY KEY, TeaID int NOT NULL, Padding char(200) NOT NULL DEFAULT 'x');
INSERT INTO dbo.Teas (TeaID, TeaName)
SELECT TOP (40) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), CONCAT('Tea ', ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
FROM sys.all_objects;
DECLARE @batch int = 0;
WHILE @batch < 200
BEGIN
    INSERT INTO dbo.TeaOrders (OrderID, TeaID)
    SELECT @batch * 100 + n, n % 40 + 1 FROM (SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects) AS x;
    SET @batch += 1;
END;
SET @batch = 0;
WHILE @batch < 800
BEGIN
    INSERT INTO dbo.TeaArchive (OrderID, TeaID)
    SELECT @batch * 100 + n, n % 40 + 1 FROM (SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects) AS x;
    SET @batch += 1;
END;

Now start cold. Taking the database offline and back online removes its pages from the pool. Then run a small workload. It reads the tea names that start with Tea 1, every order, and the older half of the archive. The offline step disconnects every session in the database, so use it only on a database you own.

USE master;
GO
ALTER DATABASE BufferTotalsDemo SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE BufferTotalsDemo SET ONLINE;
GO
USE BufferTotalsDemo;
GO
SELECT COUNT(*) AS TeaCount FROM dbo.Teas WHERE TeaName LIKE 'Tea 1%';
SELECT COUNT(*) AS Orders FROM dbo.TeaOrders WHERE Padding = 'x';
SELECT COUNT(*) AS OldOrders FROM dbo.TeaArchive WHERE OrderID <= 40000 AND Padding = 'x';

Add Up the Memory Used by Each Table

The query counts all the pages of a table, including its indexes and its large values. The first part counts cached pages for every user table. The second part counts the pages each table owns. The last query joins them and ranks the tables by cached pages.

WITH InMemory AS (
    SELECT p.object_id, COUNT(*) AS PagesInMemory
    FROM sys.dm_os_buffer_descriptors AS bd
    JOIN sys.allocation_units AS au ON au.allocation_unit_id = bd.allocation_unit_id
    JOIN sys.partitions AS p
      ON (au.type IN (1, 3) AND au.container_id = p.hobt_id)
      OR (au.type = 2 AND au.container_id = p.partition_id)
    WHERE bd.database_id = DB_ID() AND OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1
    GROUP BY p.object_id
), OnDisk AS (
    SELECT object_id, SUM(used_page_count) AS UsedPages
    FROM sys.dm_db_partition_stats
    GROUP BY object_id
)
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(m.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(m.object_id)) AS TableName,
       m.PagesInMemory,
       m.PagesInMemory / 128 AS WholeMB,
       CONVERT(decimal(9,2), m.PagesInMemory / 128.0) AS MemoryMB,
       d.UsedPages,
       CONVERT(decimal(5,1), 100.0 * m.PagesInMemory / d.UsedPages) AS PercentCached
FROM InMemory AS m
JOIN OnDisk AS d ON d.object_id = m.object_id
ORDER BY m.PagesInMemory DESC;

This is one run. Your page counts can differ by a few pages. The archive holds the most memory, 8.67 MB. Only 51.1 percent of it is cached, because the workload read the older half. The order table is almost fully cached. The tea list holds one page, which is 0.01 MB.

The query skips system tables on purpose. Remove the OBJECTPROPERTY filter to see them.

Why the Query Divides by 128.0

Compare the WholeMB column with MemoryMB. Dividing an integer by 128 drops the fraction. The archive shows 8 MB instead of 8.67 MB. The tea list shows 0 MB although it holds a page. A report that adds those column values for hundreds of small tables loses a lot. Divide by 128.0 and the fraction stays.

The same care applies to row counts. The row_count column of the view counts the rows on that page, so index pages carry their own counts. Summing it over every page does not give the rows of the table.

Totals by Database

To see which database holds the most pages, group the same view by database. Database 32767 is the hidden Resource database, so the query names it. The view has one row for every cached page. On a large server, run it once, outside the busiest hour.

SELECT CASE database_id WHEN 32767 THEN N'Resource database' ELSE DB_NAME(database_id) END AS DatabaseName,
       COUNT(*) AS PagesInMemory,
       CONVERT(decimal(12,2), COUNT(*) / 128.0) AS MemoryMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY PagesInMemory DESC;

The result depends on your server, so the demo prints none. Reading it needs the VIEW SERVER STATE permission, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

Size Is Not Memory

You could argue that table size is enough, and that sys.dm_db_partition_stats gives it without a view that changes every second. Size tells you what a table costs on disk. Only the pool tells you what it costs in memory right now. A table with 1,110 of its 2,172 pages cached behaves differently from a fully cached table.

A large share for one table is not a fault. It shows that queries read that table. A large table with a small share shows that readers touch only part of it. Read the numbers several times during a busy hour before you decide anything.

What to Remember

Add the memory used by each table, divide the pages by 128.0 and compare with the pages the table owns. Treat the result as a snapshot, because the pool changes with every query.

When you finish the demo, remove the database.

USE master;
GO
IF DB_ID(N'BufferTotalsDemo') IS NOT NULL
BEGIN
    ALTER DATABASE BufferTotalsDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE BufferTotalsDemo;
END;

A table size is not its memory use, it is the most memory the table could ever use.

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