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.

SQL SERVER - Total Data Pages In Memory Buffer Pool - sys.dm_os_buffer_descriptors total-data-pages-800x516

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:

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

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.

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

, , , ,
Previous Post
SQL SERVER – Fix Error: Currently This Report Does Not Have Any Data to Show, Because Default Trace Does Not Contain Relevant Information
Next Post
MongoDB Atlas Pricing

Related Posts

2 Comments. Leave new

  • Hi Pinal,

    I’m looking at your script and your output and in your output there’s a column with TotalPagesMB. In your script it’s missing. Would you mind adding that column?

    And thank you for the warning, i won’t run it on production (again…).

    Reply

Leave a Reply

Menu