Export (0) Print
Expand All
Expand Minimize
2 out of 10 rated this helpful - Rate this topic

sp_updatestats (Transact-SQL)

Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.

Topic link icon Transact-SQL Syntax Conventions

sp_updatestats [ [ @resample = ] 'resample']
[ @resample =] 'resample'

Specifies that sp_updatestats will use the RESAMPLE option of the UPDATE STATISTICS statement. New statistics will inherit the sampling ratio from the old statistics. If 'resample' is not specified, sp_updatestats updates statistics by using the default sampling. This parameter is varchar(8) with a default value of NO.

0 (success) or 1 (failure)

sp_updatestats effectively executes UPDATE STATISTICS, by specifying the ALL keyword, against 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. Statistics on disabled nonclustered indexes are also updated by sp_updatestats. sp_updatestats ignores tables with a disabled clustered index.

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.

For databases with a compatibility level below 90, executing sp_updatestats resets the automatic UPDATE STATISTICS setting for all indexes and statistics on every table in the current database. For more information, see sp_autostats (Transact-SQL). For databases with a compatibility level of 90 or higher, sp_updatestats preserves the automatic UPDATE STATISTICS setting for any particular index or statistics.

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;
EXEC sp_updatestats 
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2014 Microsoft. All rights reserved.