sp_updatestats (Transact-SQL)
Applies to:
SQL Server
Azure 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 (Transact-SQL). For more information about statistics, see Statistics.
Transact-SQL syntax conventions
Syntax
sp_updatestats [ [ @resample = ] 'resample']
Return Code Values
0 (success) or 1 (failure)
Arguments
[ @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. The resample argument is varchar(8) with a default value of NO
.
Remarks
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 statistics based on the modification_counter information in the sys.dm_db_stats_properties catalog view, updating statistics where at least one row has been modified. 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.
When restoring a database to SQL Server 2022 (16.x) from a previous version, it is recommended to execute sp_updatestats
on the database. This is related to setting proper metadata for the statistics auto drop feature introduced in SQL Server 2022 (16.x). The auto drop feature is available in Azure SQL Database, Azure SQL Managed Instance, and starting with SQL Server 2022 (16.x).
Permissions
To run sp_updatestats
, the user must be the owner of the database (the dbo
, not just member of the role db_owner
) or to be member of the sysadmin fixed server role.
Examples
The following example updates the statistics for all tables the database:
USE AdventureWorks2012;
GO
EXEC sp_updatestats;
Automatic index and statistics management
Leverage 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, amongst other parameters, and update statistics with a linear threshold.