SQL Server World

Buffer pages and checkpoints are one of the often written and talked about topics of SQL Server. Here I will provide my understanding and thoughts and research on these two topics.

Buffer cache is the the RAM allocated to store data pages requested by the system. This avoids making trip to the the disk subsystem and saves disk IO. Performance monitor , a windows tool to monitor server performance, has a counter call “Buffer Cache Hit Ratio” and it shows how SQL Server utilizes buffer cache. It gives the ratio of the data pages found and read from the SQL Server buffer cache and all data page requests. Ideally the value should be above 95 for OLTP systems. The pages that are not found in the buffer cache are read from the disk, which is significantly slower and affects performance. But according to Jonathan Kehiyas’s article https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/, BCHR is not a reliable counter. In various tests, even when sql server is under memory pressure, this counter remains above threshold. So relying solely on this counter could be misleading. It is recommended that a DBA should never look at one performance monitor counter to base his or her decision about the poor performance but should look at other counters such “Page life expectancy” or PLE and “Read-ahead pages” etc to get the full picture.

Whenever the data in sql server database changes (such as inserts, updates or deletes), it is written to a transaction log. As soon as these records are written out, the transaction can commit. This implies that the speed of SQL Server transactions depends on the speed of transaction log writing. Pages that were changed in memory are written to the storage subsystem through a “Checkpoint” process. By default the this process runs about every minute, and requests all dirty pages from the buffer manager. A dirty page is page that was changed in memory, but hasn’t yet been written to the storage. As soon as a dirty page is written out to the storage, the page is marked as a clean page.