Did You Know: Specifying Checkpoint Duration

Kalen Delaney — Sat, 30 Aug 2008 02:55:00 GMT

I realize probably no one reads Books Online cover to cover, so there are probably lots of little details that easily slip by you. I was introduced to one of the hidden 'features' just last week. Here it is:

CHECKPOINT can take a parameter!

It's right there in BOL:

CHECKPOINT [ checkpoint_duration ]

This parameter allows you to specify how much time (in seconds) SQL Server should take to complete the checkpoint. If you specify a smaller value, SQL Server will apply more resources to completing the checkpoint, and if you specify a larger number, SQL Server will apply fewer resources. At first glance, this seems like a very cool feature. But then I realized there was very little practical use for it, for these reasons at least:

1) There is no way to know how much time a checkpoint should normally take, so giving it a number to use more or fewer resources is just shooting in the dark.

2) This only applies to the manual checkpoint, not SQL Server's automatic checkpoint. So if you're experiencing a slight decline in throughput every minute or so when the automatic checkpoint starts running, you can't just ask SQL Server to slow down and release some of the resources.

Maybe I'm missing something, but this seems like one of those features that was added 'just because they could'. I'd be interested in hearing your ideas regarding the potential usefulness of this capability.

But really, this post was about the fact that I'd never looked at the page for the CHECKPOINT command, and never realized there were any options at all.

~Kalen

Kalen Delaney : checkpoint

Did You Know: Specifying Checkpoint Duration