As a DBA it’s important to understand what the buffer pool is doing and which databases are using it the most.
Data in SQL Server is stored on disk in 8k pages. The buffer pool (Aka “buffer cache”) is a chunk of memory allocated to SQL Server. It’s size is determined by the minimum and maximum memory settings in SQL Server memory options:
sp_configure 'min server memory (MB)' go sp_configure 'max server memory (MB)';
name minimum maximum config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- min server memory (MB) 0 2147483647 0 16 name minimum maximum config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- max server memory (MB) 128 2147483647 2147483647 2147483647
Data is processed via the buffer pool. When an application requests some data for reading and those pages are not in the buffer pool, the server has to read them from disk. These are known as physical reads. If those pages have been read already into the buffer pool, these are known as logical reads and will typically return to the application more quickly than the physical reads – memory is faster than disk.
The buffer pool is also where modifications are made to pages (dirty pages) before those changes are persisted to storage.
If sufficient memory is not available, pages are aged out of the buffer and space is made available for the pages being read from disk when they are requested. Excessive “churn” of pages in the buffer pool could indicate that it is sized too small. The Page Life Expectancy counter can be used to help diagnose if this is a problem on your server.
The buffer pool is a critical area of the system that must be adequately sized for optimal performance.
SQL Server can tell you how many of those pages reside in the buffer pool. It can also tell you which databases those pages belong to. We can use sys.dm_os_buffer_descriptors to provide this information as it returns a row for each page found in the buffer pool at a database level.
SELECT CASE WHEN database_id = 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END AS DatabaseName, COUNT(*) AS cached_pages, (COUNT(*) * 8.0) / 1024 AS MBsInBufferPool FROM sys.dm_os_buffer_descriptors GROUP BY database_id ORDER BY MBsInBufferPool DESC GO
DatabaseName cached_pages MBsInBufferPool ------------------------------ ------------ --------------------------------------- ResourceDB 2968 23.187500 AdventureWorks2012 2614 20.421875 msdb 438 3.421875 master 390 3.046875 ReportServer 332 2.593750 AdventureWorksDW2012 245 1.914062 tempdb 225 1.757812 PerformanceDW 205 1.601562 ReportServerTempDB 172 1.343750 Test 155 1.210937 model 66 0.515625 (17 row(s) affected)
The data tells us what is in the buffer pool at that moment in time. These results can change quickly depending on the activity of the server. Tracking this data over the course of a day for example would require some kind of job to periodically run this query and capture the output to file or logging table.
The data can be reset if the buffer pool is emptied either upon a SQL Server restart or by executing DBCC DROPCLEANBUFFERS which will clear out the unmodified (clean) pages from the buffer pool.
Emptying the buffer pool of clean pages by running DBCC DROPCLEANBUFFERS will increase load on the disks and decrease application performance until the cache fills again.
