March 17, 2008 at 9:03 am
I posted under sql 2000 but i guess many people don't read there any more! By default the recovery interval is set by SQL Server to a value it feels is best, however on a very busy server ( cpu>80% ) it can sometimes be more efficient to set this value to one of your own, say 10 mins - this gives a more precise control over when dirty pages are flushed - I've got high performance disk subsystem so disk usage barely hits 5% on a checkpoint .. I was just hoping to gather anyone else's experience of setting this manually and did it help a stressed server?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 17, 2008 at 1:15 pm
*If your checkpoint is barely 5%, it won't help to set it longer. If you set it longer, it will take longer to bring your database online.
*My primary reason of high cpu usage are Table scans since sql server has to process too many data.
*Second are programs stuck in a very fast loop.
Try to profile your queries to see which ones are the most consuming in terms of cpu / io
*You can change the recovery interval (checkpoint guide) from the books online:
How to set the recovery interval (Enterprise Manager)
To set the recovery interval
Expand a server group.
Right-click a server, and then click Properties.
Click the Database Settings tab.
Under Recovery, in the Recovery interval (min) box, type or select a value from 0 through 32767 to set the maximum amount of time, in minutes, that Microsoft® SQL Server™ should spend recovering each database at startup.
The default value is 0 minutes, indicating automatic configuration.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply