The Buffer Pool is the largest memory consumer inside SQL Server. This memory is used to host the data and index pages. Each buffer is 8KB in size and the content of the data/index page is loaded in the buffer space. The buffer pool is mostly managed automatically and SQL Server does a great job there. You do not have too many knobs/switches to control the behavior other than setting the MIN and MAX memory at the instance level. Ok, I am excluding Resource Governor from the discussion here. The buffer pool expands as needed and thousands of pages keep moving in and out of the pool as the workload demands. You can track each buffer item using the DMV sys.dm_os_buffer_descriptors. Just like other DMVs, the output is quite overwhelming. If you look at the data items, you can figure out that using this DMV output you can group by the database and the object. In other words, you can figure out how much memory is consumed by a database and/or by the objects in that database. You can also figure out whether the page is clean (content unchanged) or dirty (content changed). You can track the total size of the buffer pool using the perf mon counter Total Server Memory under the Memory Manager object of the instance. With some applied mathematics, you can extract more memory numbers from the DMV sys.dm_os_process_memory. You can also track the buffer pool consumption using the memory clerk MEMORYCLERK_SQLBUFFERPOOL. To know more, watch the video below.
Get new content directly in your inbox – Click here
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Hope the video was worth your time.
Happy Learning.
Leave A Comment