Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
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.
For disk-based tables, 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. Statistics on memory-optimized tables are always updated when executing sp_updatestats. Therefore do not execute sp_updatestats more than necessary.
sp_updatestats can trigger a recompile of stored procedures or other compiled code. However, sp_updatestats might not cause a recompile, if only one query plan is possible for the tables referenced and the indexes on them. A recompilation would be unnecessary in these cases even if statistics are updated.
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 preserve the latest NORECOMPUTE option for specific statistics. For more information about disabling and re-enabling statistics updates, see Statistics.
The following example updates the statistics for tables in the AdventureWorks2012 database.
USE AdventureWorks2012; GO EXEC sp_updatestats;