SQL SERVER – Data Pages In Memory Buffer Pool – sys.dm_os_buffer_descriptors

Recently I was working with a client on Comprehensive Database Performance Health Check and we discovered that they are struggling with Memory issues. During the health check, we reached the point where we needed a investigate the data in memory for a single database. Here is the script which I used to list all the database pages in SQL Server Buffer Pool.

In SQL Server when a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. The DMV sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. As of SQL Server 2019, this DMV also provides information about the database pages in the buffer pool extension file.

Here is the script which we used to investigate the data pages in Memory Buffer Pool.

SELECT	buf.page_id PageID, buf.page_level PageLevel, 
		buf.page_type PageType, buf.row_count RowCounts,
		au.type_desc AS AllocationUnitID, 
		OBJECT_SCHEMA_NAME(i.object_id) SchemaName, 
		OBJECT_NAME(i.object_id) TableName,
		i.name IndexName, i.type_desc IndexType
FROM 
sys.dm_os_buffer_descriptors buf
INNER JOIN sys.allocation_units AS au
    ON au.[allocation_unit_id] = buf.[allocation_unit_id]
INNER JOIN sys.partitions AS p
    ON au.[container_id] = p.[partition_id]
INNER JOIN sys.indexes AS i
    ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE [database_id] = DB_ID () 
AND OBJECT_NAME(i.object_id)='YOURTABLENAME' 
ORDER BY IndexType, IndexName, PageLevel DESC, PageID

I have run the above script for the orders table in the sample database wideworldimporters. Here is the output of my script.

I hope the above script helps you to investigate how much data of your table is stored in SQL Server Memory. If you want to clear the memory, you can run the following command.

Note: It is not recommended to run the following command on the production system as it will remove the cache of your entire system.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Here are a few additional related blog posts, which you may find interesting.

• SQL SERVER – List All Memory Optimized Tables in TempDB
• SQL SERVER 2019 – Memory-Optimized TempDB and RESOURCE_SEMAPHORE Wait Type
• SQL SERVER – Unable to Allocate Enough Memory to Start ‘SQL OS Boot’. Reduce Non-essential Memory Load or Increase System Memory
• SQL SERVER – Number of Tables Memory Optimized in TempDB in SQL Server 2019
• SQL SERVER 2019 – How to Enable Lock Pages in Memory LPIM?
• SQL SERVER – List Number Queries Waiting for Memory Grant Pending
• SQL SERVER – Row Mode and Memory Grant Feedback

Reference: Pinal Dave (https://blog.sqlauthority.com)