| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_updatestats (Transact-SQL) |
Runs UPDATE STATISTICS against all user-defined and internal tables in the current database. |
markingmyname |
maghan |
wiassaf, randolphwest |
08/22/2024 |
sql |
system-objects |
reference |
|
|
|
=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current |
[!INCLUDE SQL Server SQL Database]
Runs UPDATE STATISTICS against all user-defined and internal tables in the current database.
For more information about UPDATE STATISTICS, see UPDATE STATISTICS. For more information about statistics, see Statistics.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_updatestats [ [ @resample = ] 'resample' ]
0 (success) or 1 (failure).
Specifies that sp_updatestats uses the RESAMPLE option of the UPDATE STATISTICS statement. If resample isn't specified, sp_updatestats updates statistics by using the default sampling. The resample argument 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 are updated for all tables.
sp_updatestats updates statistics on disabled nonclustered indexes and doesn't update statistics on disabled clustered indexes.
For disk-based tables, sp_updatestats updates statistics based on the modification_counter information in the sys.dm_db_stats_properties catalog view, updating statistics where at least one row is modified. Statistics on memory-optimized tables are always updated when executing sp_updatestats. Therefore don't 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.
sp_updatestats preserves the latest NORECOMPUTE option for specific statistics. For more information about disabling and re-enabling statistics updates, see Statistics.
When restoring a database to [!INCLUDE sssql22-md] from a previous version, you should execute sp_updatestats on the database. This is related to setting proper metadata for the statistics auto drop feature introduced in [!INCLUDE sssql22-md]. The auto drop feature is available in [!INCLUDE ssazure-sqldb], [!INCLUDE ssazuremi-md], and starting with [!INCLUDE ssSQL22].
For [!INCLUDE ssnoversion-md], you must be the owner of the database (dbo), or a member of the sysadmin fixed server role.
For [!INCLUDE ssazure-sqldb], you must be a member of the db_owner fixed database role.
The following example updates the statistics for all tables the database:
USE AdventureWorks2022;
GO
EXEC sp_updatestats;Use solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, among other parameters, and update statistics with a linear threshold.