I must accept that I would not ask this question to anyone (particularly in the interview), however, I received this question as a follow up of my earlier blog post which I wrote about SQL SERVER – Impact of CHECKPOINT and DBCC DROPCLEANBUFFERS on Memory.
If you are not sure what is CHECKPOINT and DBCC DROPCLEANBUFFERS, here are two blog posts which I have written on this subject before and I suggest you read them before continuing this blog post.
- Dirty Pages – How to List Dirty Pages From Memory in SQL Server?
- What is Clean Buffer in DBCC DROPCLEANBUFFERS?
I also discuss this subject in detail at the Comprehensive Database Performance Health Check. Now, I assume that you have read above two blog posts so I will continue to answer the question which is the center of this blog post.
Brief Note about CHECKPOINT
SQL Server database engine often makes the changes in the database pages whenever there are various data modifications operations are performed. All the modifications are done in the memory buffer cache and those pages are called dirty pages. Every time if SQL Server Database engines go to write these dirty pages to the disk, it would be a big overhead to the engine. To avoid this burden SQL Server engine at certain interval writes the dirty pages to the transaction log and to the disk.
Challenge with Backups
CHECKPOINT itself is the reason why SQL Server Engine performs better and now the question is to even increase the speed of the CHECKPOINT. I asked a bit more in detail to the person who originally asked this question to me to understand what actually he means by increasing the speed of the checkpoint.
DBA explained to me that he knows that there is an automatic checkpoint operation but there are times when he has to take his database backup and it takes lots of time for him. He figured out that it is due to the checkpoint operation and whenever he ran the checkpoint first, he was able to take the database backup quickly. Everything was fine now but after a while, he realized that the manual checkpoints which he is running are taking lots of his time and he wants to run that even faster and hence the question.
Speed Up CHECKPOINT
Lots of people do not know that whenever you run Manual CHECKPOINT, you can also specify an additional parameter to instruct the operation to complete in the specific time period. The parameter which you pass to the operation is in seconds and once specified SQL Server will do its best to complete the operation in the time specified in the parameters.
As for my client, the entire operation was taking over 1 minute, I instructed him to specify the time duration to 10 seconds and it worked great for him. Here is the script to instruct the CHECKPOINT to complete in the specific time period.
CHECKPOINT durationinsecond
USE DATABASE; CHECKPOINT 10
He was pretty delighted with the time saved and he had plenty of available resources on the SQL Server. However, very next day he reached out to me again with a very different problem. Read about that in the next section.
Slow Down CHECKPOINT
After specifying the CHECKPOINT duration he was able to see a remarkable improvement in the databases where the entire operation was taking a very long time. This made him implement this option for other databases as well. There was one database for which the entire operation of CHECKPOINT was running in just 1 second now for that database the operation started to take longer; sometimes it took 5 seconds and sometimes even full 10 seconds. He was indeed surprised with the slowness of the operation as he expected that the new setting he is specifying will not affect any operation which was taking a lesser amount of the time but will only improve the performance of long-running processes.
He was absolutely wrong!
The way the time duration parameter works, if any CHECKPOINT process is running faster than the parameter specified, SQL Server will now start giving fewer resources to those processes and it will now try to run slower to match up to the parameter specified. This means his processes which were running in 1 second now trying to run in 10 seconds.
He learned his lessons and stop using the parameter where it was not needed.
Best Practices
In the real world, there should not be any need to change CHECKPOINT operations. You should not modify the process unless there is a specific requirement for it. However, if you still want to change the speed of your operations, you can always specify a lower number for to speed up and a higher number to slow down your CHECKPOINT operation. Remember the time duration you specify is in seconds!
If you are still not sure what to with your CHECKPOINT, let us connect, you can always reach out to me by opting for any of the options mentioned on my HIRE ME page.
Reference: Pinal Dave (https://blog.sqlauthority.com)
