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