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

Today’s blog post is actually a follow-up blog post of the previous blog post which I have written here SQL SERVER – Data Pages In Memory Buffer Pool – sys.dm_os_buffer_descriptors. Today we will be discussing how to list Total Data Pages In Memory Buffer Pool with the help of sys.dm_os_buffer_descriptors. This question was asked was during the recent Comprehensive Database Performance Health Check, hence, I decided to blog about it.

As mentioned earlier blog post, 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.

Let us see the script which will list Total Data Pages In Memory Buffer Pool.

SELECT  COUNT(buf.page_id) TotalPages, COUNT(buf.row_count) RowCounts,
COUNT(buf.row_count)/128 TotalPagesMB,
OBJECT_SCHEMA_NAME(i.object_id) SchemaName,
       OBJECT_NAME(i.object_id) TableName
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'
GROUP BY OBJECT_SCHEMA_NAME(i.object_id), OBJECT_NAME(i.object_id)
ORDER BY TotalPages DESC

The script above will list the following output:

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.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Note: It is not recommended to run the above 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.

• 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)