SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default

Moved from: bobsql.com


There are two(2) distinct checkpoint paths provided starting with SQL Server 2014, referred to as Automatic and Indirect.


The vast majority of documentation today highlights the behavior of automatic (classic) checkpoint.  This post outlines some historical aspects of checkpoint and provides the recommendation to leverage Indirect Checkpoint.


Before SQL Server 7.0

The database pages in SQL Server 6.5 and prior versions where tracked on various linked lists and as log records were generated a fixed value is assigned as a recovery target. 


Formula:   (Log records * fixed time to recover) > configured recovery interval = Enqueue