Hi there.
I was interested to know if anybody in their performance tuning work has come across SQL checkpoints causing issues, such as excessive disk I/O, and how they handled it.
I've been doing some investigations on our system and it seems that the majoring of blocks (i.e. table blocked by another user) occur at the same time a checkpoint is occuring. For example, in the last week, 90% of blocks occured at the time a checkpoint was happening. I should note that checkpoints occured at other times where blocking did not (what I'm saying here is that not every checkpoint generates a block).
I know there is a hotfix from microsoft where you can limit the MB per second of a checkpoint process, but I would be interested to know if others have come across this, and if so how they handled it.
cheers, Mark
Hi Mark,
well, I remember such a case - it occurred on a customers site some while ago - but we weren't really able to fix this ...
I'm curious abot this hotfix you mentioned! Got any link to KB?
Thanks a lot!
Regards,
Jörg
Here is the link to the KB article.
http://support.microsoft.com/kb/929240
Basically it allows you to specify a startup parameter that limits the amount of I/O per second - i.e. set MB per second for checkpoint process.
My concern is if it is set low enough to avoid an IO bottleneck, will it cause some sort of page lock if a page is being written to disk and a user needs data from that page. I guess given user requests 'hang' during this period right now, it probably wouldn't be a problem.
I have been running performance monitor for a while now to get an idea on what it should be set to. The real answer to this problem is faster disk setup, however this is not likely to happen, as its only the checkpointing that causes high levels of disk queue length.
I believe there are other settings where you can specify the length of time it takes for the checkpoint to complete, but from what I have read on BOL they are more like 'guidelines' rather than hard settings.