Overview of Checkpoint
A checkpoint is a procedure of writing all stained datafile pages out of the disk and also the procedure writes the dirty pages from the buffer cache to the physical disk and maintains the log records by writing log records from the buffer to a physical file. This procedure is also called as Dirty page hardening. The checkpoints are also helpful at the time of unexpected system shutdown because they reduce the recovery time for SQL Server. Checkpoints in SQL Server categorized into four parts i.e Automatic, Indirect, Manual, Internal. Which you can learn from here . In this article we will learn how checkpoint running long transaction and the impact of I/O subsystems on it.Effect of Long running transaction on checkpoints
A long running transaction may affect to the working procedure of the checkpoint. Because an active log consists of all uncommitted transactions and these uncommitted transactions intercepts SQL Server to move forwards to the MinLSN.This can generate two problems: If too many uncommitted transactions are performed and the system shuts down after that, the recovery phase takes too much time as the recovery interval option specified. The transaction logs may increase in a large amount, because they cannot be truncated in past MinLSN. This occurs, if you are using the simple recovery model and the transaction log truncated on every automatic checkpoint. When the checkpoints are triggered?
The SQL Server runs checkpoints automatically at the specific time intervals and also this process runs individually for each database.If the database is in autotruncate mode and the log is full, the checkpoint will trigger to free up some space by truncating transaction logs. Nevertheless, if no log space available for new transactions, there may be the long running transaction and no checkpoints triggered. If you are the owner of your database, then you can distinctly issue a checkpoint command to trigger a checkpoint in your database. SQL Server 2008, provides the feature to run checkpoints in multiple databases through checkpoint command. Checkpoint processes
If you are shutting down your SQL Server without using NOWAIT command, then the checkpoint runs in each database on your SQL Server. An instructable shutdown of SQL Server arises, when you distinctly shutdown your SQL Server until you use SHUTDOWN WITH NOWAIT command. Even it also arises if your SQL Server services are stopped through the Service Control Manager.
The sp_configure Recovery Interval option is used to control the checkpoint frequency. Also, you can use sqlserver.checkpoint_begin and sqlserver.checkpoint_end events to monitor the activities of a checkpoint. This will tell you when was the checkpoint occurred.
When the checkpoint process runs, it runs through buffer pool and nonsequentially scans all the pages. If it detects any dirty page, it looks if other physically contiguous pages are also dirty, then it will do a large block write. The process continues until it scans the last page in the buffer pool. Sometimes, an already written pages may also be dirty again and they might to be checked again in a second time.Impact of I/O subsystems on checkpoints
Sometimes, checkpoints may points a major amount of I/O, due to I/O subsystem to get overloaded with writing requests which can influence reading performance. On the other side you can make use of short I/O activities. In SQL Server 2008, you can use command-line option to throte I/O checkpoints.Conclusion
Checkpoints in SQL Server completes their processes whenever they triggered and they return immediate results whenever they complete. Sometimes, due to I/O subsystem, checkpoints running long transactions or they complete before the specified duration.