0

I would like to know when is the proper moment to append a manual CHECKPOINT in a stored procedures (or function)?

I didn't see anything different apparently after applying the CHECKPOINT to some of my code. Thus I can't illustrate if the CHECKPOINT command has been applied correctly.

Under what kind of situations should a manual CHECKPOINT be added (...to code)?

Please provide an example with reasons specifying why that a manual CHECKPOINT should be added to optimize the performance or explain why it is good practice to even do so.

3
  • I would say, there is never a reason to do that.
    – user1822
    Commented Mar 28, 2019 at 6:47
  • 1
    As pointed out in the opinion by @MartinCalmey there probably are not many reasons why you should be manually triggering a CHECKPOINT in your code. It puts load on the system which would otherwise perform automatic checkpoints itself. Could you explain how you came to the conclusion that you might require a manual CHECKPOINT or how you you would benefit from doing so? It will give us insight into your way of thinking and might lead to a correct answer.
    – John K. N.
    Commented Mar 28, 2019 at 7:05
  • i encountered the issue about transaction log became full, some articles mentioned CHECKPOINT somehow relate to transaction log issue, and i wonder if some types of queries can be improved by adding CHECKPOINT (for example bulk insert or deletion). but, as i said, i am unable to trace the difference, thus i want to ask the proper usage of CHECKPOINT (since it is provided, it must contain reasons to use it)
    – SKLTFZ
    Commented Mar 28, 2019 at 7:31

2 Answers 2

1

What are you hoping to achieve?

A CHECKPOINT flushes the dirty pages from memory to disk and records some internal information to know where it can then recover from in the event of crash recovery.

The process runs in the background periodically - so unless you have a real need to flush pages from cache then there usually is no need to issue a manual one.

However, as I don't know your specific circumstances, it is not possible to say if you have any need for this.

8
  • i am asking for the examples which checkpoint should be applied
    – SKLTFZ
    Commented Mar 28, 2019 at 4:21
  • It depends on what you want to achieve. There are no fixed rules to say issue a CHECKPOINT in these scenarios. Either you rely on the automatic checkpoint process to flush your pages to disk, or if you find that for whatever reason that you need to control this process more granularly then issue a CHECKPOINT as required. learn.microsoft.com/en-us/sql/relational-databases/logs/… Commented Mar 28, 2019 at 4:29
  • i am not asking the reason not adding CHECKPOINT. i am asking the situation which CHECKPOINT has to be appended and why it should be added
    – SKLTFZ
    Commented Mar 28, 2019 at 4:34
  • you don't need to know what i wanted (as i wanted nothing, i just want to know the usage of checkpoint). you can provide your example which applied checkpoint, and describe what you intended to achieve in your example
    – SKLTFZ
    Commented Mar 28, 2019 at 4:35
  • That wasn't a comment on when not to issue a CHECKPOINT, it was identifying what a CHECKPOINT does and therefore if you have a scenario that fits that then use a manual CHECKPOINT, otherwise there is unlikely to be any value to your database by using one. CHECKPOINT does not impact performance in any way - modifications are still made initially to the Buffer Pool and then later flushed to disk. You will not see a performance difference at all by using CHECKPOINT Commented Mar 28, 2019 at 4:37
0

Thus i need some examples of CHECKPOINT to illustrate when ppls applies CHECKPOINT and the reason of applying it.

You can schedule manual checkpoints to run every few seconds if I/O spikes caused by automatic checkpoints are a problem for your system.

It was the only way to mitigate I/O load caused by checkpoints in versions < 2012, whereas starting with SQL Server 2012 you can configure indirect checkpoints to do more frequent checkpoints wiyh lower I/O activity per checkpoint.

i encountered the issue about transaction log became full, some articles mentioned CHECKPOINT somehow relate to transaction log issue

I don't think checkpoints can solve full transaction log issue. Checkpoins just cannot clear transaction log under full recovery model (the only thing that can clear it is log backup), checkpoints clear tran log under simple recovery model but it happens without your manual intervention: every time that the log is 70% full automatic checkpoint is triggered.

The only thing I can think of when you can issue manual checkpoints is when you are under simple recovery model and your log could not be cleared on 70% reached because of long running transaction/broken replication, etc, so the log has grown much and once you found out the cause and fixed it you want to truncate the log to more reasonable size, but unfortunately your last VLF(s) are with status 2, so your VLF status chain looks like 000..002, the only way to truncate this log is to wait until the last VLF status becomes 0, and you can accelerate this by issuing manual checkpoints as they cause more log records to be flushed to disk.

4
  • do you means that checkpoint is only useful in simple recovery mode?
    – SKLTFZ
    Commented Mar 28, 2019 at 9:58
  • No, I gave you an example when adding scheduled checkpoints can reduce I/O spikes during regular checkpoints, and this does not depend on recovery model. Only when you speak about "log issue", yes, checkpoints make difference only in simple recovery model
    – sepupic
    Commented Mar 28, 2019 at 10:03
  • ok, is that means, i can insert CHECKPOINT between each line of a stored procedure (lets say 10 lines, and 10 lines are bulk insertion), by doing this, it can reduce the auto CHECKPOINT I/O spikes after query is finished (i actually doubt if auto CHECKPOINT will be triggered after the stored procedure is finished)
    – SKLTFZ
    Commented Mar 29, 2019 at 3:15
  • I doubt that I/O spikes caused by automatic checkpoints are your problem, when there is a problem you know about it. Besides, when you are doing bulk inserts, it's not a checkpoint but Eager Writer to flush dirty pages to disk during all the operation
    – sepupic
    Commented Mar 29, 2019 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.