Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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 ...

Advantages :

It may improve database recovery time It may reduce checkpoint I/O as it writes continuously pages to the disk in the background

Disadvantages :

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.

Ref:

Setting indirect checkpoints by database in SQL Server 2012

How do checkpoints work and what gets logged ?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.