asked on

Checkpoint time and periodicity

Checkpoints on one of the DBs in our system happen about every 150 minutes and take up to 30 sec and plenty resources with them. Also the checkpoint time seem to grow with time. What can I do from the software perspective to make checkpoints faster and less resource-intensive (please - no hardware solutions, this is beyond my reach)? Any maintenance procedures like defragmenting DB or drives?
This is the transactional database where only "recent" (newer then several days) records are kept, the older records are deleted daily so I can imagine that the database itself may become pretty fragmented.

Generally - are there any benchmarks on how often the checkpoints should happen and how long they should take depending on the system load and hardware configuration?

arbert

If you go into Enterprise Manger, server properties, on the database settings tab the "Recovery Interval (min)" affects how often SQL Server takes a checkpoint--you can decrease this value. Don't make it too low, or your server will be busy all the time. Also, if it's just one particular database, you could also create a scheduled job that issues a CHECKPOINT every now and then.

You can defrag the drives, defrag the indexes if you have lots of updates, inserts, and deletes. make sure the log file is on a different set of drives than the data files....

Are you running any hardware RAID?

authentify

ASKER

The value is 0 currently which means "automatic" as far I can understand. Then - the dependancy between the "Recovery Interval (min)" and an actual time the checkpoint takes (and interval between checkpoints) is all hidden.

And yes - this is RAID 5 (I know, this is bad) and this is cluster environment so write buffering is off.

arbert

"Then - the dependancy between the "Recovery Interval (min)" and an actual time the checkpoint takes (and interval between checkpoints) is all hidden."

Right, the lower the "Minutes to recovery", the quicker each checkpoint will be....

What about the location of the log and data files? Are they on the saim raid group?

authentify

ASKER

No - they are separated. Does index defrag help in this situation? And - is there such a thing as data tables fragmentation and if so - how can I defrag them?

ASKER CERTIFIED SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial