SQL Server 2016 - It Just Runs Faster - Indirect Checkpoint

This article is based on the research we did during the process of application migration from SQL Server 2014 to SQL 2016.

Whenever you are migrating to SQL 2016 from previous versions of SQL Server, make sure you do performance testing of your application with and without Indirect Checkpoint option.

You can set the Indirect Checkpoint using the "Target_Recovery_Time" option available at the database level.

IMPORTANT: Whenever you restore previous version copy of the Database, you should make sure this option is set (of course after due performance testing). The catch is that this option is set by default for all the databases (new) and system databases (model,tempdb etc). However for the restored databases, it requires to be set manually.

The observation was that though this option was not set, there was a great improvement in the CPU (for 10K concurrency test for 7 scenarios, from Avg. 75% to 68%) for SQL Server 2016 (as compared to SQL Server 2014), which is good. While this is a great achievement without any modifications to the database or code (except the Compatibility level is changed to 130 though), we have observed that the response times of some of the scenarios got drastically impacted, and that the latencies of the tempdb were horrible.

This option was tested with a value of 120 seconds and that worked like a charm. With response times coming down, no latency in tempdb, no CPU spikes during the ramp-up time.

Thoroughly test this option and this would do miracles...!

All the best!