Explanation of the Checkpoint:
For performance reasons, the database engine modifies the database pages in memory (buffer cache), but does not write these pages to disk after each change. Instead, the database engine periodically issues checkpoint commands for each database. Checkpoints writes modified pages in the current memory (called dirty pages) and transaction log information from memory to disk and logs information about the transaction log. The database engine supports several types of checkpoints: automatic, indirect, manual, and internal. The following table summarizes the checkpoint types. 1: Automatic
The DB instance level checkpoint is set to 0 to identify that the checkpoint will occur automatically 2: It happens indirectly after you manually set it.
Setting the checkpoint to 2 minutes occurs once, and two minutes will automatically perform the checkpoint. 3: Manual
The next unit is seconds, and in how many seconds the checkpoint is automatically attempted. 4: internal: Is emitted by various server operations, such as backup and database snapshot creation, to ensure that the disk image matches the current state of the log. A: The database file has been added or deleted using ALTER database. A database backup was made. B: A database snapshot is created, regardless of whether DBCC CHECK is performed explicitly or internally. C: An activity was performed that required the database to be closed. For example, Auto_Close is set to ON and the last user connection to the database is closed, or the database option change that requires a restart of the database is executed. D: stopped the SQL Server instance by stopping the SQL Server (MSSQLSERVER) service. Either operation will generate a checkpoint in each database in the instance of SQL Server. E: Take the SQL Server failover cluster instance (FCI) offline when the above occurs, the database checkpoint occurs automatically. We can imagine that when checkpoints occur frequently, it causes IO to read and write frequently, not only to improve efficiency, but also to degrade, but also to affect the life of the disk. So the checkpoint mechanism is still very useful. When the checkpoint occurs, He will process all the dirty pages of SQL Server's buffer, write to the disk file, of course there are some modified things are not commit, but will still be written to the data file, the IO write, so this mechanism is actually to write dirty data to disk, Regardless of whether your data is not submitted. Uncommitted data in the late will do undo to roll things back,
Checkpoint (SQL Server database checkpoint)
