Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-09-13 : 00:35:09
|
| writes "We have noticed severe performance degredation when the SQL Checkpoint operation kick's in on a SQL 2000 machine. Stored procedures that would normally take 20 ms, would now take 800 ms. On a transactional system that relies on high db operations, this can be a major headache.Being used to SQL 6.5 that checkpoints every 60 seconds, we noticed that on SQL 2000 (on a particular system) the checkpoint only occurs every 20 minutes (with severe consequences to any stored procs that might run at the same time). Our recovery period is set to 0 (default) and db operations happens every 1 or 2 seconds. SQL Server decided that every 20 minutes is a good time to checkpoint :-) As a solution we thought that by forcing the checkpoint to happen more often we would get smaller performance impacts (smaller log, smaller I/O).As a possible solution, we tried to set the recovery interval to 1 minute. No luck, checpoint still happens every 20 minutes.Next we forced explicit checkpoints every minute with a SQL Scheduled task. Although it occured once a minute the impact was still huge, way higher than expected. Even with almost no db operations in the period between checkpoints, the "checkpoint pages/sec" counter in perfmon is very high and stored procs still take 20 times longer than usual to execute.ANY ideas how to solve this problem ? How can we limit the performance impact of the checkpoint operation ?" |
|
|
|
|
|
Checkpoint have severe performance impact