SQL Server – Scheduling a Daily CHECKPOINT

sql serversql-server-2016

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?

Best Answer

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:

Related Question