February 5, 2008 at 4:50 pm
I'm looking for some guidance on how often I should be running and if there are any particular options to avoid:
* Check Database Integrity (I know to avoid the auto-repair because it will put the DB in single use rmode)
* Reorganize Index (I have seen the guidance to reorganize when fragmentation is between 5 and 30%, how often should I check?)
* Shrink Database
* Update Statistics
I'm in a production web environment hosting around one hundred web sites with commerce activity.
Thanks!
February 5, 2008 at 4:56 pm
1 - as often as you can, NEVER auto repair.
2 - I'd set up a job to check fragmentation and automatically fix those tables when they get too high. High is relative, but depending on how busy the tables are, I might look at anything above 20%
3 - NEVER do this
4 - Do this if needed or strange plans appear. Auto-update should be checked, or at least I've always checked it.
February 5, 2008 at 4:57 pm
DEPENDS. We never shrink the databases, just let that happen with the backups. We only reindex heavily used databases and then only once a week. Statistics and CHECKDB are run once a week also.
It really depends on your environment and what you can afford to do/not do. Reindexing can make a database unusable until the reindex is finished. Can your users afford that?
You could manually check your databases once a week and see how fragmented they are, etc. Then determine what you need to run and how often based on what you find.
-SQLBill
February 5, 2008 at 5:04 pm
Thanks gentlemen! It sounds like check integrity on a daily basis, run an index fragmentation report weekly and act from there, never shrink the database, and update statistics weekly as a first approach. We do full backups daily and transaction logs every three hours already. With our a recent upgrade in our environment we've been taking a closer look at these to ensure we are doing the right thing until we grow to the point of being able to get a full-time DBA on staff.
Cheers,
Colin
February 5, 2008 at 6:29 pm
Check these links it also indicates regarding the order of maintenance tasks
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2744477&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2742440&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2736186&SiteID=1
- Deepak
[font="Verdana"]- Deepak[/font]
February 5, 2008 at 9:31 pm
Just one other note: In SQL Server 2005 the system stored procedure 'sp_updatestats' has been improved to only update statistics on tables that have been modified.
If you use the maintenance task plug-ins in an SSMS maintenance plan - this plug-in generates UPDATE STATISTICS statements for every table in the database. Change this to use the Execute SQL Task and use the following:
Use ; Execute sp_updatestats;
Use ; Execute sp_updatestats;
...
Doing this on one of my larger systems reduced that step from 4.5 hours to ~30 minutes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 5, 2008 at 9:32 pm
Sorry - the command should be:
Use [db1]; Execute sp_updatestats;
Use [db2]; Execute sp_updatestats;
...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 5, 2008 at 10:24 pm
jeff.williams3188 (2/5/2008)
Just one other note: In SQL Server 2005 the system stored procedure 'sp_updatestats' has been improved to only update statistics on tables that have been modified.
Do you have any links to explain this behaviour? Presumably you mean that it acts differently to the default auto-stats updates.
February 5, 2008 at 11:37 pm
matt stockham (2/5/2008)
jeff.williams3188 (2/5/2008)
Just one other note: In SQL Server 2005 the system stored procedure 'sp_updatestats' has been improved to only update statistics on tables that have been modified.Do you have any links to explain this behaviour? Presumably you mean that it acts differently to the default auto-stats updates.
It acts the same way - only updating stats that actually need to be updated based on the rowmodctr rather than blindly updating everything. This is stated in 2005 BOL for sp_updatestats in the Remarks section.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 6, 2008 at 8:40 am
Great notes folks! One more question - scope. What should you not run on the system databases?
Thanks,
Colin
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply