sys.dm_os_buffer_descriptors – Day 28 – One DMV a Day

Hello Geeks and welcome to the Day 28 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

I have been talking about the memory related DMVs over a week now. It’s time to talk about one most important DMV related to memory. The most important component on SQL Server memory is Buffer Pool. Whenever there is a question on who is using up my buffer pool sys.dm_os_buffer_descriptors is your answer.

There are many articles around sys.dm_os_buffer_descriptors. I cannot cover this DMV in any other way possible as everything is covered in one or other resource on the web. So let me just walk you through this DMV and two important use cases.

Sys.dm_os_buffer_descriptors has a row for each data page which is read into memory and cached. So the use case is straight forward. You will be able to know what pages are in my buffer. Now you just need to join with few other system views to get the right sense of data in the buffer pool.

Let us run the below queries to get buffer distribution at database level, object level and clean and dirty pages for each database. Please note that sys.dm_os_buffer_descriptors also includes the entries for Buffer Pool Extensions in SQL Server 2014. The space taken up by BPE can be seen in sys.dm_os_performance_counters using below query.

SELECT * FROM sys.dm_os_performance_counters
WHERE RTRIM(object_name) LIKE '%Buffer Manager'
AND counter_name LIKE 'Extension%'

  Get buffer pool utilization by each database:

SELECT DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB' 
				ELSE DB_NAME(database_id) END,
	Size_MB = COUNT(1)/128
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC

  Get buffer pool utilization by each object in a database:

SELECT DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB' 
				ELSE DB_NAME(database_id) END,
	ObjName = o.name,
	Size_MB = COUNT(1)/128.0
FROM sys.dm_os_buffer_descriptors obd
INNER JOIN sys.allocation_units au
	ON obd.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions p
	ON au.container_id = p.hobt_id
INNER JOIN sys.objects o
	ON p.object_id = o.object_id
WHERE obd.database_id = DB_ID()
AND o.type != 'S'
GROUP BY obd.database_id, o.name
ORDER BY 3 DESC

Get clean and dirty pages count in each database:

SELECT Page_Status = CASE WHEN is_modified = 1 THEN 'Dirty' 
				ELSE 'Clean' END,
	DBName = CASE WHEN database_id = 32767 THEN 'RESOURCEDB' 
				ELSE DB_NAME(database_id) END,
	Pages = COUNT(1)
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id, is_modified
ORDER BY 2

Sys.dm_os_buffer_desciptors is used mostly in the above mentioned scenarios. The output can help in scenarios like database scale-out, data compression planning, etc. And of course, during troubleshooting PLE drop scenarios. 🙂

Tomorrow I will be covering another DMV. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook