February 17, 2009 at 12:28 am
We have a 200gb database running on MSSQL 2005 normal edition.
There are several big tables (200.000.000 rows) and every night we import around 2.000.000 rows of data in this database. The importing of this data goes well, but it also has to be processed and lately this processing fails on a regular base.
Now i was looking what's running at night and i found a maintainance plan that starts at 18:00 whichs runs for 6 - 10 hours!! This tasks does the folowing things every day:
-Rebuild all indexes (2 hours)
-Update Statistics (5 - 7 hours!!!)
-Backup database (30 min).
The backup also fails half the times because the rebuild indexes is chosen a deadlock victim. The guy who made this maintenance plan is not here for the next few weeks but i'm pretty sure he didn't know exactly if he realy had to do all these steps since he isn't a real DBA (no-one is at our company 🙁 ).
The real question here: Is this update statistics necessary? "Auto update statistics" is set to True, so i recon this does the work already for me right? In that case i can delete the update statistics task and the maintenance plan "only" lasts 2-3 hours.
February 17, 2009 at 12:37 am
By updating stats after rebuilding indexes, you've left the statistics less accurate than they would have been. Index rebuild updates all stats with fullscan. If you then do a sampled update, you're decreasing the accuracy of the statistics.
The only time you need to update stats manually is when you know that you've got a problem with stale statistics with some queries.
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
February 17, 2009 at 12:40 am
Hi,
After rebuild indexes you don't need to update statistics.
Also you don't need to rebuild index every night, read in BOL about fillfactor.
Best regards,
February 17, 2009 at 12:44 am
GilaMonster (2/17/2009)
By updating stats after rebuilding indexes, you've left the statistics less accurate than they would have been. Index rebuild updates all stats with fullscan. If you then do a sampled update, you're decreasing the accuracy of the statistics.The only time you need to update stats manually is when you know that you've got a problem with stale statistics with some queries.
The update stats is with fullscan, that's why it takes so long!!!
But thnx for your answer, i will delete the rebuild stats task directly.
February 17, 2009 at 4:52 am
Getting the statistics update away from the index rebuilds is great, but dropping the statistics maintenance entirely might not be the best idea. You should monitor your queries to see if you get bad plans, usually evident from poor performance. A statistics problem will sometimes show itself as a wide disparity between estimated and actual rows (although other things, such as multi-statement UDF's cause this as well). Statistics maintenance should still be a part of your toolbox, just appropriately placed & sized, that's all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply