To check the buffer pool/ buffer cache of the workload in SQL Server database.

An SQL Server buffer pool, also called an SQL Server buffer cache, is a place in system memory that is used for caching table and index data pages as they are modified or read from disk. 

The primary purpose of the SQL buffer pool is to reduce database file I/O and improve the response time for data retrieval.

To check the buffer pool / buffer cache usage by the table in SQL Server database.

SQL Server retrieves data from two areas; memory and disk. As disk operations are more expensive in terms of IO which means they are much slower SQL stores and retrieves data pages from an area known as the Buffer Pool where operations are much faster.

In order to understand how the Buffer Pool works and how it benefits our query processing we need to see it in action. Fortunately SQL Server gives us several management views and built in functionality to see exactly how the Buffer Pool is being used and how, or more importantly if, our queries are utilizing it efficiently.

Firstly, we need to ensure we have a cold cache to work with; that is a Buffer Pool that is not populated with any pages. We can do this without restarting SQL Server by issuing a DBCC, or Database Console Command entitled DROPCLEANBUFFERS. Prior to doing this we need to issue a CHECKPOINT command, this ensures that any dirty pages are wrote to disk cleaning the buffers, for reference a buffer is a 8 kilobyte page residing in memory.

CHECKPOINT -- writes dirty pages to disk, cleans the buffers

DBCC DROPCLEANBUFFERS -- removes all buffers

we can see how the Buffer Pool is allocated by using the sys.dm_os_memory_clerks Dynamic Management View:

---- // Check MEMORYCLERK_SQLBUFFERPOOL allocation 

SELECT TOP 10 [type], SUM(pages_kb) / 1024 AS SizeMb

FROM sys.dm_os_memory_clerks

GROUP BY [type]

ORDER BY SUM(pages_kb) / 1024 DESC

----  // To check the Logical read and Physical read of a query we need to SET STATISTICS IO ON

SET STATISTICS IO ON

GO

use <<DBName>>

GO

SELECT COUNT(*) FROM <<TableName>>

GO

----  // To check the buffer pool of all database...

SELECT * FROM sys.dm_os_buffer_descriptors

GO

---- // To check the buffer pool usage by the whole database.

SELECT COUNT(*) * 8192 / 1024 / 1024 AS 'MB'FROM sys.dm_os_buffer_descriptors 

GO

----  //  To get all Database with thier buffer pool usage.

SELECT DB_NAME(database_id) AS 'DatabaseName'

,COUNT(*) * 8192 / 1024 / 1024 AS 'MB'

FROM sys.dm_os_buffer_descriptors 

WHERE database_id <> 32767

GROUP BY database_id

ORDER BY COUNT(*) DESC

GO

----  // To get buffer pool for a particular database.

SELECT * FROM sys.dm_os_buffer_descriptors 

WHERE database_id = DB_ID('PRSDBAK') 

GO

----  //  To get the Table name consumes larger buffer pool 

SELECT OBJECT_NAME(p.object_id) AS 'TableName'

,COUNT(*) * 8192 / 1024 AS 'KB' 

FROM sys.dm_os_buffer_descriptors bd

INNER JOIN sys.allocation_units au ON au.allocation_unit_id = bd.allocation_unit_id

INNER JOIN sys.partitions p ON p.partition_id = au.container_id

INNER JOIN sys.tables t ON t.object_id = p.object_id

WHERE database_id = DB_ID('PRSDBAK') 

AND t.is_ms_shipped = 0 -- exclude system tables

GROUP BY p.object_id

ORDER BY COUNT(*) DESC

GO