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?
UPDATE (April 14 2016):
From SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default on the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support:
Indirect checkpoint is the recommended configuration, especially on systems with large memory footprints and default for databases created in SQL Server 2016.
Does indirect checkpoint takes into considerations number of dirty buffers instead of number of transaction which usually automatic checkpoint was using?
Once we have set the target_recovery_time, SQL Server internally calculates the Target Dirty Buffer threshold. As the transactions are logged in the transaction log, a Dirty Page List keeps track of the LSNs and dirty buffers which are modified by the transaction. So for indirect checkpoints, the dirty buffers from each transaction along with the LSN are being tracked.
The Recovery Writer (a new background process in 2012) periodically polls the Dirty Page List and if it finds a number of Dirty Pages in the Buffer Pool greater than the Target Dirty Buffer threshold it flushes the Dirty Buffers and moves the minLSN forward.
Does indirect checkpoint setting is more suitable for Data Warehouse vs OLTP type workload?
From BOL, an online transactional workload on a database that is configured for indirect checkpoints could experience performance degradation. This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.
What scenarios you consider for SQL database in question before you start leveraging indirect checkpoint?
This depends on many factors as each environment is different interms of hardware, memory, transactions occuring, etc . I would recommend you to TEST and double TEST this particuliar feature as it can be helpful to you or else can cause detrimental effect on your workload and possibly jeopardise your databases as well. By default, target Recovery time is set to ZERO meaning, change it if you have tested it thorougly and its behaviour is acceptable to you …
It may improve database recovery time
It may reduce checkpoint I/O as it writes continuously pages to the disk in the background
In OLTP workload it can increase overall writes on server by writing continuously pages to the disk in the background which may reduce the performance.