View Event: What the heck is a checkpoint, and why should I care?
An 8K page is the fundamental unit of data storage in SQL Server. For performance reasons, SQL Server performs every data modification operation in memory (buffer pool) and does not write it back to disk immediately.
This is where checkpoint comes into play. The Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. It also records this information in the transaction log.
This session will explain why you should care and know about the checkpoint process and the different checkpoints that SQL Server does. I will show you exactly what happens during a checkpoint, how you can influence the interval of checkpoints, changes made with checkpoint settings in SQL 2012 and SQL 2016+.
Leave a Reply