View Event: What the heck is a checkpoint, and why should I care?
In SQL Server, a checkpoint is an internal process that writes dirty pages and transaction log records from memory to disk and marks a point in the transaction log. An 8K page is the fundamental data storage unit in SQL Server.
SQL Server performs every data modification operation in memory (buffer pool) for performance reasons and does not immediately write it back to disk.
This is where checkpoints come into play. There are four types of checkpoints, automatic, indirect, manual, and internal. The Database Engine periodically issues a checkpoint on each database based on the current setting to help reduce the recovery time of a given database from unexpected shutdown to system failure.
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, and changes made with checkpoint settings in SQL 2014 and SQL 2016+.
Leave a Reply