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