Monday, 5 November 2012

Indirect Checkpoint feature in SQLServer 2012

What is Indirect Checkpoint Feature in SQL Server 2012?

The Indirect Checkpoints feature in SQL Server 2012 provides facility to change the Target Recovery Time (Seconds) parameter values from its default value of ZERO to a number which is suitable for the database to recover quickly in the event of an unexpected shutdown or Crash.

What is the use of changing Target Recovery Time (Seconds) Parameter value?

Changing the value of Target Recovery Time (Seconds) parameter higher than ZERO changes the CHECKPOINT behavior of that particular database from Automatic Checkpoint to Indirect Checkpoint.

When the Recovery Interval (MIN) value is set to ZERO which is the default value then SQL Server Instance will rely on Automatic Checkpoints and when Recovery Interval (MIN) value is higher than ZERO SQL Server Database Engine relies on Indirect Checkpoint for that particular database. Automatic Checkpoints occur for the database approximately once a minute for all the active databases and the recovery time for the database will be typical less than a minute.

Indirect Checkpoint helps to reduce the overall database recovery time. It also provides the predictable recovery time than in the automatic check points.

We may also experience performance degradation, if we enable indirect checkpoint feature on an OLTP environment which experience heavy workload. Because the background writer used by indirect checkpoint might increases the total write load for a server instance.

This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.

It is recommended that we keep the recovery interval at 0, unless we experience performance problems. If we decide to increase the recovery-interval setting, it is recommended that increasing it gradually by small increments and evaluating the effect of each incremental increase on recovery performance.

What is the use of Checkpoint in SQL Server?

For performance reasons, the Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. And the Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.

In the earlier versions of SQL Server a DBA can change the recovery interval (min) value at the instance level from its default value of ZERO using SP_CONFIGURE System Stored Procedure or using SQL Server Management Studio. There was no possibility to change the recovery interval for a particular database. 

Configuration Steps

The following example sets the target recovery time of the Test database to 30 seconds.

USE [master]

We can also configure through SQL Server Management Studio by right click on the specific database, select properties, go to options and in the Recovery panel, under the Target Recovery Time (Seconds) field