Jeffrey Williams (5/23/2008)
Unless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will skip your just rebuilt indexes because they don't need to be updated.BTW - I am still trying to find out where the default sampling value is kept. When you run UPDATE STATISTICS with no qualifier (i.e. FULLSCAN, SAMPLE, etc...) - the statistics are supposed to be updated using the default sampling. I was under the impression that this would be what the statistics were built with the last time - but may not be?
From BOL - statistics on indexes are created using the FULLSCAN option. But if we update the statistics later it does not unless we specifically include the FULLSCAN option? That is really the question...
Jeff
Jeffrey Williams (5/23/2008)
Unless you are using 'sp_updatestats' or something similar that only updates statistics on those objects that need to be updated. Running that right after rebuilding indexes will skip your just rebuilt indexes because they don't need to be updated.BTW - I am still trying to find out where the default sampling value is kept. When you run UPDATE STATISTICS with no qualifier (i.e. FULLSCAN, SAMPLE, etc...) - the statistics are supposed to be updated using the default sampling. I was under the impression that this would be what the statistics were built with the last time - but may not be?
From BOL - statistics on indexes are created using the FULLSCAN option. But if we update the statistics later it does not unless we specifically include the FULLSCAN option? That is really the question...
Jeff
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:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service