inHouseDBA (6/4/2012)

---

Greeting All:
Using SQL Server 2005 (9.00.5000.00), but moving to SQL Server 2008 in 4 months.
I am in the process of updating my Weekend Maint scripts..
Currently I Reindex Clustered and Non-Clustered Indexes when avg_page_space_used_in_percent < 75 and avg_fragmentation_in_percent > 10 and page_count > 100.
Of those selected, if Fragmentation <=30, than I reorganize those Indexes. If Fragmentation > 30, than I rebuild those Indexes.
So at the end of the Maint I have Non-Clustered and Clustered Indexes either Rebuilt or Reorganized.
Also I have Clustered and Non-Clustered Indexes which were not selected that weekend to be reindexed and heap tables.
After that step I usually run sp_updatestats command for the entirely database, assuming that BOL cannot lie: sp_updatestats updates only the statistics that require updating..., so, for example, Indexes, which were just rebuild in the previous step, will be skipped.
But as my database gets bigger and I see more and more discussions contradicting the above extract from BOL, can someone please outline update statistics steps that needs to be done after Weekend Reindexing.
Thank you,
B

In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.

USE dbname;
EXEC sys.sp_MSforeachtable 
    @command1 = N'UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;';

inHouseDBA (6/6/2012)

---

Thank you,
I guess my stopping point is the following:
When you rebuild the index - you rebuild it with 100% sample size, but when you run sp_updatestats a default sampling is being used with a following formula:
Rowmodctr 500 + 20% of total rows (rowcnt).
So by running sp_updatestats after Index Rebuild you are already not improving those indexes, but making it even worse?!

inHouseDBA (6/7/2012)

---

Thanks, so by running ...UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;.. it will also update Columns in heap tables as well, right?