What’s Checkpoint All About?
Checkpoints can be very important to the health and performance of your Microsoft SQL Server. In order to optimize performance, modifications made to your database are first performed in Cache, and later persisted to disk. When a checkpoint occurs, cache is flushed to disk. Checkpoints when they occur are autonomous to a single database.
For a database using the simple recovery technique, checkpoints are essential in controlling the size of your transaction log. Unless some operation is delaying the transaction log, when a checkpoint occurs on a simple recovery database then the transaction log truncates the unused section. The more often a checkpoint occurs, the smaller your transaction log becomes. Again, this behavior is only for databases using simple recover.
What makes a Checkpoint event occur?
Automatic checkpoints occur based on what the database determines the maximum amount of transaction work that may be recovered in the time allowed as configured for the database. A one minute recovery window will generate automatic checkpoints more often than a 5 minute recovery window because more transactions can be recovered in 5 minutes. The recovery interval occurs when the SQL server service is restarted for any reason. If you look at the SQL Server Logs you can see database recovery events at startup.
The checkpoint only impacts simple recovery mode databases. Other recovery models use the traditional Backup, Differential backup and Transaction Backup techniques to manage the transaction log truncation, etc.
There are three other types of checkpoints that SQL Server uses: 1) Indirect; 2) Manual; and 3) Internal. Indirect checkpoints are used when you specify a recovery time for a specific database. This overrides the setting for the Automatic Checkpoint setting for the entire server. Manual checkpoints are when you execute a Checkpoint Command, and only impacts the database of the current connection. Finally, the internal checkpoints are issued by SQL Server in conjunction with user actions such as a database snapshot, or some form of backup.
Here’s a good TechNet link for more details, http://technet.microsoft.com/en-us/library/ms189573.aspx.
How important is the checkpoint settings in your experience? Share your thoughts below, or drop an email to btaylor@sswug.org.
Cheers,
Ben