September 30, 2008 at 7:24 am
Hey guys
Each day round about the same time our production server, begins a CHECKPOINT transaction. My understanding of this is , its writing transactions from the log file to disc. The database runs in simple recovery mode, so either the log file is 70 % full or server resources have decided that now is a good time to do the CheckPiont. When the Checkpiont begins the disk time goes through the roof (Obviously), and brings the users connectd to the box to a grinding halt. Locks occur, production is affected badly.
Is there a way i can get the server to checkpoint after business hours? I have read that possibly increasing the Recovery interval, would manage this. But on the other hand if the log reaches 70% it will checkpoint anyway.
Regards.
September 30, 2008 at 7:34 am
Checkpoint doesn't happen once a day. Do you perhaps have an agent job that kicks off around the same time?
The frequency of the checkpoint is determined by the amount of data chances. SQL will schedule checkpoints to keep the estimated database recovery time low, and it's configured by the "recovery interval" option of sp_configure.
The checkpoint at 70% is only applicable for TempDB, as it has no need to recover after a shutdown.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2008 at 3:34 am
So if the recovery interval is increased, would that have a bad effect on the Db?
October 1, 2008 at 8:43 am
Mark Kinnear (10/1/2008)
So if the recovery interval is increased, would that have a bad effect on the Db?
It would mean that recovery would take longer, in the case where the server goes down and the DB is not closed cleanly. It would also mean that the checkpoints would do more work when they do run, increasing the impact they have on your system.
Unless you're doing very, very few modifications, it's unlikely that the checkpoint only runs once a day. I would suggest run profiler for a while and see if you can see anything taking lots of CPU and/or lots of IOs at the time the problem occurs. It's possible that something else, along with the checkpoint is causing the problems.
Also check your disk stats. From perfmon
physical disk:avg sec/read
physical disk:avg sec/write
physical disk:% idle time
Also, what version of 2005 are you on? I know at some point a throttle was added to the checkpoint, but I don't remember when.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply