1) Are you sure that sp_updatestats only updates stats that need it? a) if autoupdate is on this would be pointless since they would already be done presumably and b) I looked at the code for sp_updatestats and it seems to do everything. There was use of stats_ver_current(@table_id, @ind_id), but I can't find code for that anywhere. Perhaps that is what you were referring to?
NOTE: per BOL here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b86a88ba-4f7c-4e19-9fbd-2f8bcd3be14a.htm, "If you prefer not to update all statistics by running sp_updatestats". That implies that sp_updatestats does all stats.
and yet here: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/919158f2-38d0-4f68-82ab-e1633bd0d308.htm, " This updates statistics only when they are required. " Not very clear!! 🙂
2) I am sure there is an algorythm somewhere that details out the logic on what percentage of rows/data is sampled for update stats call, but I can't find it at the moment.
Oh, while checking around for some clarification for this topic I stumbled across this: http://blogs.msdn.com/queryoptteam/archive/2006/07/21/674350.aspx. Quite amazing that you can 'falsify' the stats stuff and thus force the optimizer into a particular type of plan!! Gosh can I think of some uses for that. :w00t:
Yep, I am with you - it is a bit confusing. When I reviewed 'sp_updatestats' I found that it checks whether or not there have been rows modified (rowmodctr). If so, it updates the statistics for that table, if not - it gets skipped. I tested this and it does indeed skip tables that have not had any activity.
Now - from everything I have been able to find, the only way to insure that statistics are rebuilt using a full scan is to issue an UPDATE STATISTICS WITH FULLSCAN (or rebuild the index) and turn off auto update stats. If auto update stats kicks in - it rebuilds the statistics using the default sampling rate (which I still don't know what that value is).
But, if you have not hit an auto update stats situation for a table - and you run sp_updatestats with the RESAMPLE option or UPDATE STATISTICS WITH RESAMPLE, then those statistics will be rebuilt with the previous sampling rate. That means that statistics built when an index was created/rebuilt will be updated with a full scan - and any statistics that were created manually with a full sampling rate would also be built with a full scan.
Basically - it does not look like you can insure that stats are updated with a full scan all the time.
Jeff
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