Is it a good idea to schedule a daily CHECKPOINT?

Checking our production logs from yesterday, we discovered a period of about 5 minutes when a whole bunch of really simple queries were timing out. Further investigation on the server logs showed a huge spike in disk activity, which led me to the conclusion that an automatic CHECKPOINT was being run on the DB at that time.

That's something I really don't want to happen during peak hours. So I was thinking of scheduling a daily CHECKPOINT every day during off-peak hours.

Is that a good idea? Bad idea? Waste of time? If not that, then what?

It's hard to know this for sure given the limited information here, but I don't think you're quite down to root cause yet.

• Why did a huge spike in disk activity make you think the root cause was a checkpoint?

• Why would the best fix be running a checkpoint manually?

• Have you checked into which queries were running at the time, and what their wait stats were?

Checkpoints already happen much more frequently than daily - the default in 2016 is every minute.

So to answer the question:

• Adding a daily checkpoint would be a bad idea and ineffective

• Consider doing more troubleshooting like checking the server's wait stats

• For a followup, include server config and wait stats info in your question (disclaimer: that's my blog post)

StackExchange Database Administrators Q#199705, answer score: 12