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.
- Case-Sensitive Search – SQL in Sixty Seconds #158
- Wait Stats for Performance – SQL in Sixty Seconds #157
- Multiple Backup Copies Stripped – SQL in Sixty Seconds #156
- Forwarded Records and Performance – SQL in Sixty Seconds #155
- Hide Code in SSMS – SQL in Sixty Seconds #154
- Zoom in SSMS – SQL in Sixty Seconds #153
- Transfer Schema of Table – SQL in Sixty Seconds #152
- Find a Table in Execution Plan – SQL in Sixty Seconds #151
- Transactions and Variables – SQL in Sixty Seconds #150
- Count Table in Cache – SQL in Sixty Seconds #149
- List All Sessions – SQL in Sixty Seconds #148
- Line Numbers for SSMS Efficiency – SQL in Sixty Seconds #147
- Slow Running Query – SQL in Sixty Seconds #146
- Change Database and Table Collation – SQL in Sixty Seconds #145
- Infinite Loop – SQL in Sixty Seconds #144
- Efficiency Trick – Query Shortcut – SQL in Sixty Seconds #143
- SQL SERVER – 16 CPU vs 1 CPU : Performance Comparison – SQL in Sixty Seconds #142
- SQL SERVER – TOP and DISTINCT – Epic Confusion – SQL in Sixty Seconds #141
Reference: Pinal Dave (https://blog.sqlauthority.com)
