Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.
sp_updatestats [ [ @resample = ] 'resample']
Return Code Values
0 (success) or 1 (failure)
- [ **@resample** =] 'resample'
Specifies that sp_updatestats will use the RESAMPLE option of the UPDATE STATISTICS statement. If 'resample' is not specified, sp_updatestats updates statistics by using the default sampling. resample is varchar(8) with a default value of NO.
sp_updatestats executes UPDATE STATISTICS, by specifying the ALL keyword, on all user-defined and internal tables in the database. sp_updatestats displays messages that indicate its progress. When the update is completed, it reports that statistics have been updated for all tables.
sp_updatestats updates statistics on disabled nonclustered indexes and does not update statistics on disabled clustered indexes.
sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.
For databases with a compatibility level below 90, executing sp_updatestats does not preserve the latest NORECOMPUTE setting for specific statistics. For databases with a compatibility level of 90 or higher, sp_updatestats does preserves the latest NORECOMPUTE option for specific statistics. For more information about disabling and re-enabling statistics updates, see Using Statistics to Improve Query Performance.
Requires membership in the sysadmin fixed server role, or ownership of the database (dbo).
The following example updates the statistics for tables in the AdventureWorks database.
USE AdventureWorks; GO EXEC sp_updatestats
Revisions throughout the document to improve accuracy.
Added reference to new statistics content in the topic Using Statistics to Improve Query Performance.