question

Ok, first something on what kind of animals statistics are, and how SQL Server handles them. Statistics are objects that SQL Server optimizer uses to produce a good enough query plan for a query. The default setting in SQL Server is to automatically create statitics and to automatically update statistics. Statistics are created for either indexes or for non-indexed columns. When you look at an estimated query plan, you'll see lots of "Estimated number of rows". That estimation is based upon the statistics for the underlying columns involved in a query's WHERE-, JOIN- and other clauses. As data changes in a table, the statistics might become old. There are certain rules for when SQL Server considers statistics too old (for example 20% of the rows for a certain statistics have changed). If Auto Update Stats is set to on, SQL Server will automatically update the statistics when: - Statistics for an underlying column are old. - A query references a column for which the old statistics object is needed. The problem with this is that SQL Server will update the statistics when a query is entered and SQL Server tries to produce a query plan. For a large table, update statistics will take a while and for an online query, it's not OK to wait for several seconds. Statistics can be manually updated, using the UPDATE STATISTICS command. UPDATE STATISTICS can either sample a percentage of the rows or scan all rows. To scan all rows, use **UPDATE STATISTICS table WITH FULLSCAN**. If no sample ration is given, SQL Server will use whatever sample ratio it finds most suitable (which is not always optimal, but is in most cases good enough). And now on to sp\_updatestats: sp\_updatestats will update statistics for all tables in a database. If the parameter @resample has the value 'resample', it will use whatever sample ration was last used to update statistics. If that argument is not given, sp_updatestats will use whatever sample ratio SQL Server finds most suitable.

Here's a decent reference from Christian Bolton's session at SQLBits : http://sqlbits.com/Sessions/Event5/Vital_Statistics