Recently I was going over few advanced options of SQL Server 2012 in database properties and I found a new option in the property screen.
Properties screen of SQL Server 2008 R2
Properties screen of SQL Server 2012
I got little curious and decided to learn what does this new feature indicates. When I started to learn more about this subject, I had excellent learning experience.
The default value of this option is 0. This value is directly related to Checkpoint. When it is set to greater than 0 (zero) it uses indirect-checkpoints and establishes an upper-bound on recovery time for this database.
NOTE: Do not attempt this right away on your production database. Make sure that you try this out on your development server and validate before making it any changes on your production server. Make sure that you do not change this option unless you are 100% sure about what you are doing and understand the risk as well implications.
Indirect Checkpoints are alternative to automatic checkpoints (which should be good for most of the system and should be left alone if you are not sure about implications). If system crashes accidentally, indirect checkpoints provides potentially faster recovery time than automatic checkpoints.
Reasons for enabling indirect checkpoint
- It may improve database recovery time
- It may reduce checkpoint I/O as it writes continuously pages to the disk in the background
Reasons for NOT enabling indirect checkpoint
- 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.
I have now learned the pros and cons of this feature and I am going to test this feature out on various workload oriented servers and see what is the effect of this feature. You can read further on MSDN about this feature.
Reference : Pinal Dave (https://blog.sqlauthority.com)
