Database Internals


Checkpoint is an internal process that writes all dirty pages (modified pages) from Buffer Cache to Physical disk, apart from this it also writes the log records from log buffer to physical file. Writing of Dirty pages from buffer cache to data file is also known as Hardening of dirty pages.

It is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process for each Database individually.

Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crash\Failure.

In SQL Server 2012 there are four types of Checkpoints:

  1. Automatic: This is the most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval-Server Configuration Option.
  2. Indirect: This is new in SQL Server 2012. This also runs in the background but to meet a user-specified target recovery time for the specific Database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected this will override the Recovery Interval specified for the server and avoid Automatic Checkpoint on such DB.
  3. Manual: This one runs just like any other T-SQL statement, once you issue checkpoint command it will run to its completion. Manual Checkpoint runs for your current Database Only. You can also specify the Checkpoint_Duration which is optional, this duration specifies the time in which you want your checkpoint to complete.
  4. Internal: As a user you can’t control Internal Checkpoint. Issued on specific operations such as:
  • Shutdown initiates a Checkpoint operation on all databases except when Shutdown is not clean (Shutdown with nowait).
  • If the recovery model gets changed from Full\Bulk-logged to Simple.
  • While taking Backup of the Database.
  • If your DB is in Simple Recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.
  • Alter Database command to add or remove a data\log file also initiates a checkpoint.
  • Checkpoint also takes place when the recovery model of the DB is Bulk-Logged and a minimally logged operation is performed.
  • DB Snapshot creation.

Log Buffer:

Log buffer is a small contiguous memory structure, only 60k maximum, to host in memory transaction logs per database. When the transaction is committed, entire buffer will be written to transaction log file before commit finishes. While running a big transaction, the log buffer will be filled up immediately. As this happens, the buffer will be forced to be written to the disk and being cleaned up for rest of the transactions. In concurrent transaction within one database scenario, the log buffer contains mixed logs from all active transactions. Any one of the transaction gets committed or the 60k buffer gets full will cause entire buffer to be written.

SQL Server using write ahead mechanism to guarantee the logs to be written ahead of flushing data pages. So checkpoint process is also the process causing logs to be flushed because this process writes all the dirty pages to the disk!

Log Writer:

Log writer is the process to flush the log buffer to disk. Every instance of SQL Server has only one log writer. This process looks after all the log buffers for every database. It calls asynchronous IO to complete the IO operation. While committing a transaction, it will block the user process until the completion of asynchronous IO. While user process is blocked, log writer can still work for other database log buffers. In other cases, the log writer is working asynchronously with user sessions.

What is in the buffer cache?

Hard disks are slow; memory is fast. This is a fact of nature for anyone that works with computers. Even SSDs are slow when compared to high-performance memory. The way in which software deals with this problem is to write data from slow storage into fast memory. Once loaded, your favorite apps can perform very fast and only need to go back to disk when new data is needed. This fact of life in computing is also an important part of SQL Server architecture.

Whenever data is written to or read from a SQL Server database, it will be copied into memory by the buffer manager. The buffer cache (also known as the buffer pool) will use as much memory as is allocated to it in order to hold as many pages of data as possible. When the buffer cache fills up, older and less used data will be purged in order to make room for newer data.

Data is stored in 8k pages within the buffer cache and can be referred to as ‘clean’ or ‘dirty’ pages. A dirty page is one that has been changed since last being written to disk and is the result of a write operation against that index or table data. Clean pages are those that have not changed, and the data within them still matches what is on disk. Checkpoints are automatically issued in the background by SQL Server that will write dirty pages to disk in order to create a known good restore point in the event of a crash or other unfortunate server situation.

Buffer Cache:

Buffer cache is a memory pool in which data pages are read. It performance of the buffer cache is indicated as follows: 95% indicates that pages that were found in the memory are 95% of time. Another 5% is needed for physical disk access. If the value falls below 90%, it is the indication of more physical memory requirement on the server.

Log Caches:

Log cache is a memory pool used to read and write the log pages. A set of cache pages are available in each log cache. The synchronization is reduced between log and data buffers by managing log caches separately from the buffer cache.

Buffer Pool:

The bufferpool is portion of a main memory space which is allocated by the database manager. The purpose of bufferpools is to cache table and index data from disk. All databases have their own bufferpools. A default bufferpool is created at the time of creation of new database. It called as ‘IBMDEFAULTBP’. Depending on the user requirements, it is possible to create a number of bufferpools. In the bufferpool, the database manager places the table row data as a page. This page stays in the bufferpool until the database is shutdown or until the space is written with new data. The pages in the bufferpool, which are updated with data but are not written onto the disk, are called ‘Dirty’ pages. After the updated data pages in the bufferpool are written on the disk, the bufferpool is ready to take another data.

Dirty Pages:

A logical write occurs when data is modified in a page in the buffer cache. A physical write occurs when the page is written from the buffer cache to disk. SQL Server uses a technique known as write-ahead logging that prevents writing a dirty page before the associated log record is written to disk.

Dirty Reads:

A dirty read (aka uncommitted dependency) occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.

Lazy Writer:

Lazy writer can adjust the number of buffers in the buffer pool if dynamic memory management is enabled. SQL Server estimates the number of necessary Buffer Pool buffers based on system activity and based on the number of stalls. A stall occurs when a request for memory has to wait on a free buffer page.

Leave a Reply

Your email address will not be published. Required fields are marked *