I am trying to understand where to draw a fine line for Indirect Checkpoint that was introduced in SQL Server 2012.
Based on my understanding
SQL Server does checkpoint to flush dirty pages out to the disk, but frequency or time interval between checkpoints depends on many factors. It is controlled by server level configuration called recovery interval and default value for this settings is zero which means in a fairly busy system SQL server can perform checkpoint every minute and can result in database recovery time of less than a minute.
SQL Server 2012 introduced indirect checkpoint – which in turn can allow you to control recovery interval for individual databases.
At a high level this setting appears to a good thing, it allows to even out disk IO by performing more frequent checkpoints instead of doing a periodic checkpoint which can result in flooding the underlying IO subsystem
Now my questions are
a) Does indirect checkpoint takes into considerations number of dirty buffers instead of number of transaction which usually automatic checkpoint was using?
b) I keep finding in many blogs that indirect checkpoint setting is very dangerous as
It can keep your IO system very busy. Does below statement is true from above blog?
Checkpoint normally writes large blocks of data to disk optimally in single write operation, up to 256KB, depending on the number of contiguous dirty pages in cache that needs to be flushed to disk. Once you turn on the indirect checkpoint feature by setting target recovery time to a non-zero value, the checkpoint writes will turn into single page writes, 8KB writes.
c) Does indirect checkpoint setting is more suitable for Data Warehouse vs OLTP type workload? What scenarios you consider for SQL database in question before you start leveraging indirect checkpoint?
