Update Database Statistics

Statistics are used by the Query Optimizer to create execution plans based on statistical distribution of required values, the information is stored in BLOBs (binary large objects). Outdated or non-existent statistics can lead to poor performing queries or sub-optimal execution plans.

This runs the UPDATE STATISTICS command against all user defined tables in the current database.

You can also specify the RESAMPLE parameter as well. This is used to update each statistic using its most recent sample rate. Allowed values for this parameter are NO and RESAMPLE, with NO as the default. Using RESAMPLE may cause full table scans for indexes, while using NO will use the most recent sample rate.

If ‘resample’ is not specified, sp_updatestats updates statistics by using the default sampling. If you’re “resampling”, you’re choosing the same sample set (data pages) as the previous sampling, and simply collecting up to date statistics from that sample.

USE Datebase01
GO
EXEC sp_updatestats 'Resample'
GO

Sample result…

Updating [ABC].[tblTable01]
    [IX_Col03] has been updated...
    [_WA_Sys_00000065_0EA330E9], update is not necessary...
    [_WA_Sys_00000001_0EA330E9], update is not necessary...
    [_WA_Sys_00000003_0EA330E9], update is not necessary...
    [IX_Col01], update is not necessary...
    [_WA_Sys_0000006B_0EA330E9], update is not necessary...
    [_WA_Sys_00000006_0EA330E9], update is not necessary...
    [_WA_Sys_00000069_0EA330E9] has been updated...
    [IX_Col02], update is not necessary...
    2 index(es)/statistic(s) have been updated, 11 did not require update.

Updating [sys].[filetable_updates]
    [IX_Col01], update is not necessary...
    0 index(es)/statistic(s) have been updated, 1 did not require update.
 
Statistics for all tables have been updated.

Note:

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 for all tables will not be updated, only the statistics that have had one row or more modified will be updated.

Sources:

https://sqlperformance.com/2013/07/sql-statistics/statistics-updates

https://www.mssqltips.com/sqlservertip/5855/update-statistics-for-all-tables-and-databases-in-a-sql-server-instance/