Target Recovery Interval and Indirect Checkpoint – New Default of 60 Seconds in SQL Server 2016

checkpoint please

Update, 6/21/2016: Be careful using indirect checkpoint with failover clusters if your SQL Server 2014 instance is not fully patched. See KB 3166902. This bug was fixed in SQL Server 2016 prior to RTM.

SQL Server 2016 introduces big new features, but it also includes small improvements as well. Many of these features are described in the “It Just Runs Faster” series of blog posts by Bob Ward and Bob Dorr.

One article in this series explained that new databases created in SQL Server 2016 will use “Indirect Checkpoint” by default. Indirect checkpoint was added in SQL Server 2012, but has not previously been enabled by default for new databases. The article emphasizes this point:

Indirect checkpoint is the recommended configuration, especially on systems with large memory footprints and default for databases created in SQL Server 2016.

Head over and read the article to learn how indirect checkpoint works.

Indirect Checkpoint for new databases in SQL Server 2016 is set using the model database

When you create a new database in SQL Server 2016, if you use the GUI and click on the ‘Options’ tab, you can see the “Target Recovery Time (seconds)” in the Recovery section is set to 60.

target-recovery-interval-new-database

This value is inherited from the model database, so if you don’t choose to use this as the default for your new databases, you can change it there. You can also turn this on for individual databases in SQL Server 2012, 2014, and databases restored to 2016.

Query sys.databases to see your current recovery interval, and if you’re using indirect checkpoint

You can see the settings for existing databases with this query…

SELECT name, target_recovery_time_in_seconds
FROM sys.databases;

If the target recovery time is set to 0, that means the database uses automatic checkpoints (not the newer indirect feature).

Is 60 seconds a big change in recovery interval?

Nope. Not unless you’ve changed “recovery interval (min)” in your server configuration settings. Check your current setting with this query…

SELECT name, value, value_in_use
FROM sys.configurations 
WHERE name = 'recovery interval (min)';

If  your value of ‘recovery interval (min)’ is set to zero, that means automatic checkpoints are typically occurring every minute (source).

Setting Target Recovery Time (Seconds) to 60 at the database level maintains the same checkpoint interval, but uses the indirect checkpoint algorithm.

Are there risks to using indirect checkpoint?

Yes. Things can go wrong with any configuration, and every setting can have bugs.

If you’re using indirect checkpoint with a failover cluster on SQL Server 2014, make sure to test and apply recent cumulative updates. On June 21, 2016, Microsoft released KB 3166902.

This KB is a pretty serious one: “FIX: logs are missing after multiple failovers in a SQL Server 2014 failover cluster”.  When log records are missing, SQL Server can’t recover the database properly — read the error message carefully in the KB and note that it says:

Restore the database from a full backup, or repair the database.

I verified from the team at Microsoft that this bug was fixed in SQL Server 2016 prior to RTM, so no need to wait for a patch.

Extra: Trace Flag 3449 and Indirect Checkpoint

In June 2016, Microsoft released a series of Cumulative Updates for SQL Server 2012 and 2014 that recommend using Trace Flag 3449 and indirect checkpoint on servers with 2+ terabytes of memory to speed up creating new databases. See KB 3158396 for details.

What performance counters should I monitor?

Mike Ruthruff wrote an excellent blog on the SQL Server Customer Advisory Team blog  comparing performance with automatic checkpoint across several SQL Server versions with Indirect Checkpoint on SQL Server 2016.

He shows how average disk write latency was lower with indirect checkpoint on the system in question, and the meaning of the “Checkpoint Pages / sec” counter (automatic checkpoints) and “Background Writer Pages/sec” counter (indirect checkpoints). Read the post here.

Previous Post
Next Door to Derpton – When Your Fellow DBA is a Danger to Databases (Dear SQL DBA)
Next Post
Outside the Big SAN Box: Identifying Storage and SAN Latency in SQL Server (Dear SQL DBA)

Related Posts

6 Comments. Leave new

Hello Kendra,

I see you used Full recovery model. What about Simple?
In the referencing post “It just runs faster”, it says it is strongly recommended for larger memory footprints.
What about small databases each 3-10 GB; their number per instance is about 50, with a total storage of about 200GB?

Thanks

Reply

    Hi Igor!

    Great questions.

    Checkpoint is also needed in the SIMPLE recovery model. Checkpoint is the process of writing pages from memory to the data file, and that needs to happen in SIMPLE as well.

    The blog post does say that it will make more of a difference on instances with a large amount of memory. If you have a large number of active small databases that are cumulatively using a large amount of memory, that would fall under their recommendations for sure.

    Kendra

    Reply

      Thanks,
      Very good. I already made some testing for the CPU behaviour with the option changed, and noticing that its average value decreases for 2-3%.

      Reply

[…] Kendra Little notes that indirect checkpointing is now the default in SQL Server 2016: […]

Reply
Stephen Anslow
April 29, 2019 4:15 pm

Having set every DB to 60 seconds several weeks ago, I’m now querying those same databases, and the value is ZERO! What!? Ideas, anyone? Google search yields no clues… Did I just waste my time doing that for 30+ instances, none of which had a “New” databases, all of which were restored for 2014 or earlier vintage databases, to 2014, 2016 and 2017.

Hoping…

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu