SQL Server 2016 – SQL Server checkpoint

Hi Friends,  In today’s blog post we will learn about the importance of SQL Server checkpoint and changes in SQL Server 2016. I’ll start with some basics.

How SQL Server works?

To understand the checkpoint, it’s important to understand the working of SQL Server. Let’s say, I want to update a few records of a table in SQL Server. I’ll submit the TSQL command to update those records. SQL Server will read the data pages related to those records in the buffer pool (if not already exist in buffer pool) to perform the update operation. Before updating the actual records, log records related to those changes will be written to log buffer. Then actual changes will be performed on the data pages. After this, the transaction will be committed and commit record will be written to log buffer. Finally, log buffer will be flushed to the transaction log file.

What is a checkpoint?

In the above section, If you have noticed that log records related to the changes have been flushed to the transaction log file while modified data pages are still there in the buffer pool. SQL Server does not write the modified data pages from the buffer pool to data files after every change because it will be the costly operation in terms of IO. So, rather than writing these modified data pages after every change, SQL Server uses checkpoint process to write these modified data pages (also known as Dirty Pages) periodically. Keep in mind that the checkpoints process also writes the information from the log buffer to the transaction log file.

What is the Importance of Checkpoint?

SQL Server also writes the information about the last successful checkpoint into the database boot page. So that it knows, till which point of transaction log file, data files are in sync.

For Example: Let’s say the last successful checkpoint happen at 01:00:00 AM. My system went down due to some issue (like power failure) at 01:00:59 AM. In these 59 seconds, there may be some transactions which have been written to the transaction log file, but the data pages related to these changes were not written to the data file. Whenever, my system will come up, SQL Server will Analyze all the transactions log records after the last successful checkpoint and will also perform the Redo/Undo operations based on the state of those logged transactions. In this way, SQL Server maintains the consistency of the databases. Time taken during these operations is known as the recovery time.

Here, we can say, the SQL Server checkpoint process has a direct impact on database recovery time and also try to minimize the impact of IO by writing dirty pages periodically. Definition on msdn is mentioned below:

“A Checkpoint creates a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.”

Types of Checkpoints:

The Checkpoint will trigger at what time, it depends on it’s type. There are four types of it in SQL Server from 2012 onward. Before SQL Server 2012, there were only 3 types excluding Indirect.

1. Automatic Checkpoints
2. Indirect Checkpoints
3. Manual Checkpoints
4. Internal Checkpoints

Automatic Checkpoint:
This is the default checkpoint prior to SQL Server 2016. The Frequency of this checkpoint depends on the calculation of how much recovery time will be required for generating log records. There is a default value 0 that is configured at Instance level. This generally trigger the automatic checkpoint after every minute or 60 seconds. It also depends on the workload, for a database, generating huge amount of log records can trigger the automatic checkpoint much frequently as compared to the database which is generating a low number of log records. Because the recovery time will be calculated based on the number of transaction log records and to maintain the recovery time limit specified at Instance level.

Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 20 milliseconds. You can check this value in the Database Settings section of the SQL Instance Properties:

If you want to change it, then you can change it using SSMS as shown above or you can use TSQL (Keep in mind that changing these things requires lots of testing otherwise it will impact the performance):

USE [master];  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'recovery interval', #;  --Specify value in place of # in minutes
GO  
RECONFIGURE;  
GO

Indirect Checkpoint:

This checkpoint introduced in SQL Server 2012. We know that automatic checkpoint is for Instance level. To change the recovery time at database level for specific database(s), we can use the Indirect checkpoint.  Indirect checkpoint provides more accurate recovery time as compare to automatic checkpoint because of its algorithm. It also considers the cost of Random IO during Redo.

You can check the Indirect checkpoint configuration using SSMS as shown above or below TSQL statement:

Select name,target_recovery_time_in_seconds from sys.databases

You can also change this value for specific database using SSMS as shown above or using TSQl as mention below:

ALTER DATABASE DBNAME SET TARGET_RECOVERY_TIME = 75 SECONDS;

PS: Indirect checkpoint is the default checkpoint for databases created in SQL Server 2016 and default value specified is 60 seconds. Database (Created on SQL Server 2014/2012/2008..) restored on SQL Server 2016 will not use Indirect checkpoint automatically. You have to change that manually.

Manual Checkpoint:

You can also run the checkpoint manually on the selected database using TSQL as mention below:

CHECKPOINT [checkpoint_duration]

Here checkpoint_duration is of INT data type and optional.

Internal Checkpoint:

SQL Server can also trigger the checkpoint(s) internally based on specific events. Some of them are listed below:

1. In the Simple Recovery model, if the transaction log is 70% full.
2. Adding or Removing database files.
3. Database Snapshot creation during DBCC CHECKDB.
4. SQL Server clean shutdown.
5. Database Backup etc.

PS: Long-running uncommitted transactions increase recovery time for all types of checkpoints.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook