Update Statistics

Table statistics not being up-to-date can also hurt query performance. As such, SQL Server tries to keep them as up-to-date as much as possible but from time to time forcing SQL Server to regenerate statistics for a table is a good habit.

Statistics Application

Statistics are used by the query optimiser to construct the best execution path for each query that SQL Server is requested to execute.

When these statistics aren't in line with the current data in a table, the query optimiser will select a suboptimal execution path at best but that can have a significant performance impact. Although the query optimiser will update statistics when necessary during query execution, updating those statistics at a regular, e.g. weekly, basis with a separate task can improve performance much further.

For more information about table statistics, read this Microsoft article: Update Statistics

Update With SQL Server Management Studio

Updating statistics can be done very easily via SSMS.

Open up the statistics of the table:
Right-click on one of the statistics and select Properties. The following dialog opens:
In the Properties, the date the last time the statistics were updated is shown. In the example: September 12th, 2022 at 12:06:27.
For updating the statistics, select the checkbox "Update statistics for these columns" and click OK.
Re-open the Properties dialog, and the last update date is changed:

This works fine if the number of statistics to update is small, otherwise SQL statements can help to speed up regeneration for a larger group of tables.

With SQL Statements

Running SQL statements to update table statistics provides a more fine-grained control.

Entire Database

This first statement executes a stored procedure regenerating the statistics for the entire database on which the statement is executed.

exec sp_updatestats

Result:

Updating [dbo].[StateTaskMethodFieldLinks]
    [PK_StateTaskMethodFieldLinks], update is not necessary...
    0 index(es)/statistic(s) have been updated, 1 did not require update.

Updating [dbo].[LifecycleStates]
    [PK_LifecycleStates], update is not necessary...
    0 index(es)/statistic(s) have been updated, 1 did not require update.

Updating [dbo].[LifecycleStateSettings]
    [PK_LifecycleStateSettings], update is not necessary...
    [IX_LifecycleStateSettings_LifecycleID_LifecycleStateID], update is not necessary...
    0 index(es)/statistic(s) have been updated, 2 did not require update.
...
Updating [sys].[syscommittab]
    [ci_commit_ts], update is not necessary...
    [si_xdes_id], update is not necessary...
    [si_commit_time_ts], update is not necessary...
    0 index(es)/statistic(s) have been updated, 3 did not require update.

Updating [sys].[filetable_updates_2105058535]
    [FFtUpdateIdx], update is not necessary...
    0 index(es)/statistic(s) have been updated, 1 did not require update.

Updating [dbo].[LifecycleStateIcons]
    [PK_LifecycleStateIcons], update is not necessary...
    0 index(es)/statistic(s) have been updated, 1 did not require update.

Statistics for all tables have been updated.

Completion time: 2022-09-26T17:11:53.6421963+02:00

Note

Regenerating statistics for an entire database can take a long time. Please reconsider if the database contains large tables with lots of data and many indexes.

Single Tables

Use the following statement to regenerate statistics for a single table. Example:

UPDATE STATISTICS Sales.SalesOrderDetail;

With the statement above, the query optimiser samples the data in the table and determines the sample size to use for the statistics.

Another possibility is to use a full table scan. Not recommended if the table contains a huge amount of data:

UPDATE STATISTICS Sales.SalesOrderDetail WITH FULLSCAN

A better option would be with a sample size, in this example of 50%:

UPDATE STATISTICS Sales.SalesOrderDetail WITH SAMPLE 50 PERCENT;

Many other options exist. Please consult the Microsoft documentation: UPDATE STATISTICS for full information.

Next Steps

Just like with maintaining indexes on a more regular basis, it might be a good idea to create a maintenance plan to automate all these actions: Create Maintenance Plan.