Indirect Checkpoints Feature in SQL Server 2012

By: Editor
April 24, 2011

Introduction

Indirect Checkpoints feature is an interesting feature which is available in SQL Server 2012. Using this feature a Database Administrator can manually change the target recovery time in seconds’ parameter for a particular user database from its default value of ZERO.

What Checkpoint command does in SQL Server?

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

In the previous versions of SQL Server a Database Administrator can go ahead and 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. However, there was no possibility to change the recovery interval for a particular database.

How Indirect Checkpoints feature works in SQL Server 2012

The Indirect Checkpoints feature of SQL Server 2012 helps a Database Administrator to go ahead and change the Target Recovery Time (Seconds) parameter value from its default value of ZERO to a number which they think is appropriate for the database to recover quickly in the event of an unexpected shutdown or a crash. 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.



How to configure Indirect Checkpoints on a database Using TSQL

Execute the below ALTER Database statement to configure Indirect Checkpoints on AdventureWorks2008R2 sample database which is running in SQL Server 2012 Compatibility Mode.

USE [master]
GO

ALTER DATABASE [AdventureWorks2008R2] SET TARGET_RECOVERY_TIME = 5 SECONDS WITH NO_WAIT
GO

How to configure Indirect Checkpoints on a database Using SQL Server Management Studio

1. In Object Explorer, expand Database Node and then right click the Database and select the Properties option from the drop down menu.

2. In the Database Properties, Select Options Page in the left panel and scroll down to Recovery option as highlighted in the below image.

3. In the Recovery panel, under the Target Recovery Time (Seconds) field as highlighted you can specify the number of seconds that you want as the upper-bound for the recovery time of the database. Once the changes are done click OK to save the changes.

How to configure Indirect Checkpoints on a database Using SQL Server Management Studio

Important Note

Before implementing Indirect Checkpoints feature in Production a DBA must simulate the workload in QA or Development environment to understand how your system behaves in response to the changes. This will help one to understand very clearly how the database behaves once Indirect Checkpoints feature is enabled on a Production Database.

Advantages of Indirect Checkpoints

1. When you use Indirect Checkpoint it will help reduce the overall database recovery time.
2. You will see reduced Checkpoint-related I/O as SQL Server continually will write dirty pages to the disk in the background.
3. Indirect Checkpoint provides more predictable recovery time than automatic checkpoints.

Disadvantages of Indirect Checkpoints

1. As per MSDN, You may experience performance degradation if you enable indirect checkpoint feature on an OLTP database which experience heavy workload. This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.

Conclusion

In this article you have seen how to enable and configure Indirect Checkpoints Feature in SQL Server 2012.

Reference: Database Checkpoint (SQL Server)


Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…




Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."