December 10, 2010 at 10:22 am
We have a daily job to cleanup old data (50-100 thousand records from various tables). The auto update statistics option is set to true.
I wonder do we need to force update statistics in this case? If yes, what would be the better way to do it:
1. EXEC sp_updatestats
2. EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'
3. Something better?
Thanks.
December 10, 2010 at 11:08 am
Number 2 is you have time for maintenance at night. No blocking, but it is resource intensive.
We have a job the loops thru a table with the day of the month to Reindex or Rebuild with Update Stat or just Update stats based on the amount of fragmentation.
The source table with the indexes has the table/index looked at 2 times a month.
God Bless,
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
December 11, 2010 at 4:26 pm
Create a maintenance plan with Update statistics task and schedule it to run daily.
Thank You,
Best Regards,
SQLBuddy
December 12, 2010 at 12:38 am
3)
Write a job step that runs at the end of that job and updates stats (with full scan) on the tables touched by that cleanup. There's no point in wasting time updating stats on tables that don't need it. If you have other tables that you know are prone to poor stats (because they often get bad plans), manually update those too.
Nightly sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN' or update stats maint plans are fine on the 500MB database. They are far from fine on the 500GB database. On larger databases you need to get smart with maintenance and not try to take the simple shortcuts, they take too long.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply