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
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 () 
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.


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

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

Buffer Pool, SQL DMV, SQL Index, SQL Memory, SQL Server
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

1 Comment. Leave new

  • Hello Sir,
    I have 2 nodes in cluster both are primary to each other, when I ran the below query
    select db_name(database_id) as DBName, (count(1)/128)/1024 as DBMemoryConsumed_GB from sys.dm_os_buffer_descriptors group by database_id
    It exhibits the in memory consumed by all the databases including the database exist on the secondary node.
    For example If I have 10 client configured in primary and another 10 on secondary node
    When I ran the above query on primary node , it shows the memory consumed by the databases from the secondary node as well.
    How is it possible?
    why secondary node databases objects are stored in primary node memory?


Leave a ReplyCancel reply

Exit mobile version