SQL Server Memory Usage – Buffer

What is the SQL Server Buffer Pool?

I am purposely keeping this post short because I simply want to offer up my script to show the current amount buffered. My version is very similar to that found on the main sys.dm_os_buffer_descriptors page, but I prefer MiB rather than pages.

--http://jongurgul.com/blog/sql-server-memory-usage-buffer/
SELECT
 CASE dobd.[database_id] WHEN 32767 THEN 'ResourceDB' ELSE DB_NAME(dobd.[database_id]) END AS [DatabaseName]
,COUNT_BIG(*) [PagesBuffered]
,CONVERT(DECIMAL (15,3),COUNT_BIG(*)*0.0078125) [Buffered_MiB]
FROM sys.dm_os_buffer_descriptors dobd
--WHERE dobd.[database_id] = DB_ID()
GROUP BY dobd.[database_id],DB_NAME(dobd.[database_id])

This next script will break down the buffer by page_type which will probably be overkill for most peoples needs.

--http://jongurgul.com/blog/sql-server-memory-usage-buffer/
SELECT
 CASE dobd.[database_id] WHEN 32767 THEN 'ResourceDB' ELSE DB_NAME(dobd.[database_id]) END AS [DatabaseName]
,OBJECT_NAME(p.[object_id]) [ObjectName]
,p.[index_id] [IndexID]
,dobd.[page_type] [PageType]
,COUNT_BIG(*) [PagesBuffered]
,CONVERT(DECIMAL (15,3),COUNT_BIG(*)*0.0078125) [Buffered_MiB]
FROM sys.dm_os_buffer_descriptors AS dobd
LEFT OUTER JOIN sys.allocation_units AS au ON au.[allocation_unit_id] = dobd.[allocation_unit_id]
LEFT OUTER JOIN sys.partitions AS p ON au.[container_id] = p.[partition_id]
WHERE dobd.[database_id] = DB_ID()
GROUP BY dobd.[database_id],DB_NAME(dobd.[database_id]),p.[object_id],p.[index_id],dobd.[page_type]

Leave a Reply