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.

sys.dm_os_buffer_descriptors

  Get buffer pool utilization by each database:

sys.dm_os_buffer_descriptors

  Get buffer pool utilization by each object in a database:

sys.dm_os_buffer_descriptors

Get clean and dirty pages count in each database:

sys.dm_os_buffer_descriptors

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