GilaMonster (6/29/2012) opc.three (6/29/2012)
You may have something very granular or customizable planned in terms of your scripting effort so this may not be applicable, or these options may not satisfy your requirements, but I figured I would throw them out there since they were not mentioned and they may save you some time:
1. sp_updatestats (SQL Server 2005)
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.
Well, BoL's not entirely accurate there. The 'threshold' that sp_updatestats uses is 1 row.
Parsing a bit, but unchanged
seems to be an accurate characterization. Misleading may be a fair assessment of that slice of the doc though. Ola's code does the same, checking this when @OnlyModifiedStatistics = 'Y':
sysindexes.[rowmodctr] <> 0
That's why I hedged and said something very granular or customizable planned in terms of your scripting effort
. If one wanted to check rowmodctr manually and compare that to the number of rows in the stat it could help get closer to smart stats updates. I thought rowmodctr might become skewed since the column tracks updates but the engine seems to be aware when the same index entry is updated, meaning rowmodctr could be of service. At any rate, I read that since SQL 2005 the real story (the one used by auto update stats algorithms) is not available in any catalog view, only system tables that require we use DAC to see.
__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato