Indirect Checkpoint – SQL Server 2012

I noticed an interesting feature that’s released with SQL Server 2012. In this version now you can set recovery time at database level. In other words now you can set when checkpoint should happen to a database which directly relates to the recovery time of the database. Before stepping into further I would like to explain you what’s recovery time and how does it matter in recovery situation.

Checkpoint is the process which will clear all the dirty pages and hardens the data. Check point can be triggered automatically, manually and also by internal operations like backup, ALTER database command and so on. This process actually moves all the modified data from log file to data file and this is known as recovery time. Based on the checkpoint interval recovery time varies so that during disaster recovery the time need to wait for recovering the database varies.

In the existing versions of SQL Server we can change the recovery time in server configuration (recovery interval (min)) which is effective to all the databases. This doesn’t helped much because if you change this value it’s for all the databases so the database which has less DML activities should also need to go through checkpoint process. Since checkpoint process is an IO intensive operation it’s not recommended to happen very frequently. In other words each database has it’s own DML activities so it’s better to have individual checkpoint interval. Considering this SQL Server 2012 has a new feature called indirect checkpoint which allows user to set individual recovery time.

Below table explains you how indirect checkpoint behaves

recovery interval (min)

Indirect Checkpoint

Database Recovery

0 (default value) 0 (default value) Checkpoint behavior is picked from Server configuration . Default value is 0 which means the checkpoint triggers based on number of outstanding writes
> 0 0 Checkpoint behavior is picked from Server configuration. Check point triggers based on the value you give for recovery interval. SQL Server ensures that recovery can be within that time frame.
NA >0 Checkpoint behavior is independent from server configuration. Checkpoint happens to that database only which ensures that recovery time is less than or equal to the value you specify.

Enabling Indirect Checkpoint

Let’s see how to enable indirect checkpoint. As you all know all the database configuration can be done through database properties in GUI or using ALTER DATABASE command. TARGET_RECOVERY_TIME is the new parameter that’s included in ALTER DATABASE command which is responsible for triggering indirect checkpoint and this behavior is only for that particular database.

Syntax

ALTER DATABASE DBName SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }

T-SQL

ALTER DATABASE AdventureWorks2012 SET TARGET_RECOVERY_TIME = 5 MINUTES

GUI

indirect_checkpoint_1

If you note from the image above you can notice that recovery time always in seconds. So if you try to change it in GUI you have to provide seconds value.

Does it work with all recovery model?

Yes off course it will work with all recovery models.

How can I see what are all the databases are enabled for indirect checkpoint?

New column is added to sys.databases catalog view. You can use the query below

SELECT name,target_recovery_time_in_seconds  FROM sys.databases
WHERE target_recovery_time_in_seconds <> 0

indirect_checkpoint_2

To conclude, this is an interesting feature where you have control on recovery time of individual database. One thing to keep tin mind is that setting frequent checkpoint burdens background IO writer thread but your recovery will be faster during crash. In the other side if you set longer checkpoint interval then your background IO writer will behave normally but there is a increase in your recovery time. So you have to validate the requirement and then balance in between.


Posted

in

by

Comments

2 responses to “Indirect Checkpoint – SQL Server 2012”

  1. RaviTeja Gullapalli avatar
    RaviTeja Gullapalli

    Nice one…

Leave a Reply

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