SQL Server Memory Buffer Pools: Understand the Basics

How many times you have heard this question: “Why is SQL Server consuming so much memory?” If you ask a DBA who knows the functionality of SQL Server, the answer would be, “That is an expected behavior! Limit max server memory of SQL Server if you are concerned.”

One might then wonder why SQL Server doesn’t release the memory if there is no load on the server. After reading this article, you will have an answer.

SQL Server is a server-based application that is designed for high performance. In SQL Server, the data in table is stored in pages which has fixed size of 8 KB. Whenever there is a need of a page (for read or write) the page is first read from the disk and bought to memory location. This area in SQL Server Memory is called “Buffer Pool”.  Once the page is loaded in memory, all subsequent calls which need this page would be read from Buffer Pool – this would avoid disk IO and hence improve performance. This is the precise reason why memory usage in SQL Server is high even if no one is using SQL Server.

Once a page is modified in memory due to data modification (Insert/update/delete), it is called “dirty” page. On the other hand, if a page which is not modified is called “clean” page. Prior to SQL 2005, there was no easy way to find information about pages in buffer pool. From SQL Server 2005 onwards, each data page in buffer pool has one buffer descriptor. Buffer descriptors DMV can uniquely identify each data page that is currently cached in an instance of SQL Server under buffer pool. This DMV sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. This includes pages that are associated with the Resource database which is hidden system database.

Below query can be used to find out how many pages of type clean and dirty for each database.

SELECT (CASE 
           WHEN ( [database_id] = 32767 ) THEN 'Resource Database' 
           ELSE Db_name (database_id) 
         END )  AS 'Database Name', 
       Sum(CASE 
             WHEN ( [is_modified] = 1 ) THEN 0 
             ELSE 1 
           END) AS 'Clean Page Count',
		Sum(CASE 
             WHEN ( [is_modified] = 1 ) THEN 1 
             ELSE 0 
           END) AS 'Dirty Page Count'
FROM   sys.dm_os_buffer_descriptors 
GROUP  BY database_id 
ORDER  BY DB_NAME(database_id);

There is a DBCC command which can drop the clean buffers called as DBCC DROPCLEANBUFFERS. I have run the command two times – before and after DBCC DROPCLEANBUFFERS. Here is what we would see:

We generally say DROPCLEANBUFFERS as dirty command because we are not supposed to use this on a production server because it would increase disk IO. This is because all pages would be fetched from disk again as they are flushed away from the buffer. In summary, a clean page is one that has not been changed since it was last read from or written to disk. A dirty page has been changed and copy on disk and in memory is different.

Another way to get details about buffer pool is to use performance counters (Perfmon). There is an object called “Buffer Manager.”

How do dirty pages get flushed to disk? Well, that’s the job of checkpoint, which is a background process. The purpose of checkpoint is to flush dirty pages in order to keep the number of transactions that have to be rolled forward during a potential recovery to a minimum.

What if there is a memory pressure? Will clean pages go off the buffer pool automatically? Yes. Lazywriter (again a background process) does that for us. This process flushes out batches of aged buffers. The main purpose of the Lazywriter is to maintain the Free List.

I hope this article has given you a basic understanding of the various components of the buffer pool and the processes needed to maintain enough memory. So the next time you see high memory consumed by SQL, take a deep breath and say – “It’s expected!”