Indirect Checkpoints in SQL Server 2012

By: Rajendra Gupta   |   Updated: 2012-06-21   |   Comments (1)   |   Related: > Recovery Models

Problem

SQL Server 2012 has many new features and the Indirect Checkpoints feature is an interesting option to allow you to control the recovery time for a database. In this tip we will explore the use of this new feature and the steps to configure this option.

Solution

Before we get into the details about indirect checkpoints, let me give a brief overview of the checkpoint process.

The SQL Server Database Engine automatically executes the CHECKPOINT command to write all dirty pages from the Buffer Cache of the current database to disk. Dirty pages are the data pages which were stored in the Buffer Cache and were modified, however they have not yet been written to disk. This process helps the SQL Server Database Engine save time during database recovery in the event of a sudden system crash or an unexpected SQL Server shutdown.

A DBA could change the recovery interval (minute) value at the instance level from its default value of '0' using SP_CONFIGURE, which sets the RTO (Recovery Time Objective) to 1 minute.  SQL Server will internally calculate the frequency of the checkpoints, so that the recovery should not take more than 1 minute to bring the database online.

SQL Server internally calculates the intervals in the transaction log at which the checkpoint happens, but suppose after a checkpoint we have some big transactions which create a large number of dirty buffers.  If a crash happens after this, recovery of all these dirty buffers needs to take place by SQL Server. So in this case, the recovery interval will be longer because it has to handle the large number of dirty pages.

So to assist in this process, we now have Indirect Checkpoints which is a new feature in SQL Server 2012.

Indirect Checkpoints

The Indirect Checkpoints feature helps to change the Target Recovery Time (seconds) parameter value from its default value of ZERO to a number which is appropriate for the database to recover quickly in the event of an unexpected shutdown or a crash. Changing the value to a higher value other than ZERO changes the CHECKPOINT behavior for that particular database from Automatic Checkpoint to Indirect Checkpoint.

When the Recovery Interval (minute) value is set to ZERO, which is the default value, then the SQL Server Instance will rely on Automatic Checkpoints.

Once we have set the target_recovery_time, SQL Server internally calculates the Target Dirty Buffer threshold. As the transactions are logged in the transaction log, a Dirty Page List keeps track of the LSNs and dirty buffers which are modified by the transaction. So for indirect checkpoints, the dirty buffers from each transaction along with the LSN are being tracked.

The Recovery Writer (a new background process in 2012) periodically polls the Dirty Page List and if it finds a number of Dirty Pages in the Buffer Pool greater than the Target Dirty Buffer threshold it flushes the Dirty Buffers and moves the minLSN forward.

With the new Indirect Checkpoint, SQL 2012 ensures at any given point in time there is not more than target dirty buffers in the Buffer Pool, which can be easily recovered within the target recovery time.

The default value of TARGET_RECOVERY_TIME can be checked using this query:

or by using SQL Server Management Studio as shown below.

Now, to set this as Indirect Checkpoint we need to change the value for TARGET_RECOVERY_TIME. To change this value, use this query:

ALTER DATABASE 'DATABASE NAME' 
SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES };

The following example sets the target recovery time of the AdventureWorks2008R2 database to 120 seconds

ALTER DATABASE AdventureWorks2008R2 
SET TARGET_RECOVERY_TIME = 120 SECONDS;

or this can be changed using SQL Server Management Studio as shown below:

To verify the change, run the below query:

Advantages of Indirect Checkpoints

• They can reduce the overall recovery time for a specific database.
• You can control the database recovery time by factoring in the I/O cost during the REDO phase.
• They also reduce potential I/O spiking issues during the checkpoint process by continually writing to disk.

Disadvantages of Indirect Checkpoints

• One thing to be aware of is that there could be a performance hit if you enable indirect checkpoints on a very busy OLTP database, because of the increased IO load by the background writer operation.  So make sure you test this in a Test environment before enabling in Production.

Next Steps

• If you have not had a chance to preview SQL Server 2012, download  a copy to get started today.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips