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.

SQL SERVER - Data Pages In Memory Buffer Pool - sys.dm_os_buffer_descriptors buffer-pool-800x445

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.

SQL SERVER - Data Pages In Memory Buffer Pool - sys.dm_os_buffer_descriptors datapagesinmemory

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.

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

, , , ,
Previous Post
SQL SERVER – List All Memory Optimized Tables in TempDB
Next Post
SQL SERVER – Statistics Modification Counter – sys.dm_db_stats_properties

Related Posts

Leave a Reply

Menu