SQL SERVER – Flush Data from Memory to Disk

One of my clients asked me how to Flush Data from Memory to Disk during a recent consulting engagement Comprehensive Database Performance Health Check. Well, the answer is pretty simple in the following script.

CHECKPOINT

However, the real question is why would we need to do that, when SQL Server can do that automatically. I have been doing SQL Server Performance Tuning for many years and I can confidently say that in my career, I have not needed this feature more than a couple of times.

There have been situations once where the server was heavily crunched for the IO resources and I had given a manual checkpoint with the time parameter so it would take more time to complete but that was a very rare situation. For most cases, the default value and frequency should just work fine.

Do you ever use this command manually? If yes, please leave a command, I would like to know business reason behind it.

Here are my few recent videos and I would like to know what is your feedback about them. You can subscribe to my youtube channel here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Menu
Exit mobile version