Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Indirect Checkpoints in SQL Server 2012


By:   |   Last Updated: 2012-06-21   |   Comments (1)   |   Related Tips: More > 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.

TARGET_RECOVERY_TIME and 'recovery interval' Options
TARGET_RECOVERY_TIME
Recovery Interval
Checkpoint Used
0
0
Automatic checkpoints whose target recovery interval is 1 minute.
0
> 0
Automatic checkpoints whose target recovery interval is specified by the user defined setting of the sp_configure recovery interval option.
> 0
NA
Indirect checkpoints whose target recovery time is determined by the TARGET_RECOVERY_TIME setting, expressed in seconds.

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

default value of TARGET_RECOVERY_TIME

or by using SQL Server Management Studio as shown below.

using Managment Studio

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;

sets the target recovery time of the AdventureWorks2008R2 database to 120 seconds

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

Advantages of Indirect CheckPoint

To verify the change, run the below query:

Disadvantages of Indirect CheckPoint

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


Last Updated: 2012-06-21


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, June 26, 2012 - 4:49:06 AM - Srinath Back To Top

A very nice article..Thanks for ur effort!!!!!!!!!


Learn more about SQL Server tools