Checkpoints are essential in SQL Server to help with the durability and reliability of data persisted in the database. When done right, you barely even notice the checkpoint and performance is groovy. Sadly, checkpoints can be a symptom of a problem and poor performance.
Checkpointing in SQL Server can have an impact on performance. Most of the time, DBAs don’t bother to think about checkpoints or their behavior. This isn’t necessarily a bad thing. It’s just a fact of life.
Much like the DBA, most of the time, checkpoints have no reason to be in the foreground of thought. This just simply means that things are running smoothly and we can occupy ourselves with other tasks.
When odd behaviors start to pop up such as the dreaded checkpoint io storm or the Flushcache (here or here) error, you need to have some tools to help you try and troubleshoot the issue. This article will help you add at least one more tool to your SuperStar DBA Toolbox.
Let’s start with a bit of background on what a checkpoint is.
Checkpoint Primer
A checkpoint is basically the last known good mark (or point) from which SQL Server can start applying changes registered in the transaction log during recovery after an unexpected shutdown or crash.
What is it that the checkpoint does? The checkpoint process is the means by which the dirty pages (modified data pages in memory) are written and persisted to disk. This process also takes the transaction log information and persists that to disk, while also adding a note to the transaction log of what was done.
Continue reading on jasonbrimhall.info.
