How to refresh statistics in SQL Server?

Refreshing statistics in SQL Server is a critical maintenance task that helps the query optimizer generate efficient execution plans, ensuring your database queries run quickly. You can refresh statistics using T-SQL commands for precise control and automation, or through SQL Server Management Studio (SSMS) for a visual approach.

How to Refresh Statistics in SQL Server?

SQL Server relies on statistics to understand the data distribution within your tables. This information is crucial for the query optimizer to choose the most efficient way to retrieve data. Stale statistics can lead to poor query plans and slow performance. Regularly refreshing statistics, especially after significant data modifications, is essential for optimal database performance.

There are primarily two ways to refresh statistics in SQL Server: using Transact-SQL (T-SQL) commands or through SQL Server Management Studio (SSMS).

Method 1: Refreshing Statistics Using T-SQL Commands

T-SQL commands offer granular control and are ideal for scripting, automation, and scheduled maintenance tasks.

1. `UPDATE STATISTICS` Statement

The UPDATE STATISTICS statement allows you to update statistics for a specific table or indexed view.

Syntax:

UPDATE STATISTICS table_or_indexed_view_name
    [ { { index_or_statistics_name | ( statistics_name [ ,...n ] ) }
        | @statistics_variable } ]
    [ WITH
        [ { FULLSCAN | SAMPLE number { PERCENT | ROWS } | RESAMPLE }
            [ , NORECOMPUTE ]
            [ , INCREMENTAL = { ON | OFF } ]
        ]
        | [ { ALL | COLUMNS | INDEX }
            [ , NORECOMPUTE ]
            [ , INCREMENTAL = { ON | OFF } ]
        ]
    ];

Common Options:

FULLSCAN: Scans all rows in the table or indexed view to gather statistics. This provides the most accurate statistics but can be resource-intensive for very large tables.
SAMPLE number { PERCENT | ROWS }: Samples a specified percentage or number of rows to gather statistics. This is faster than FULLSCAN but may be less accurate.
- Example: SAMPLE 10 PERCENT or SAMPLE 10000 ROWS.
RESAMPLE: Updates statistics using the current sampling rate. If no sampling rate is specified, RESAMPLE uses full-scan by default for indexed statistics. For column statistics, it uses the default sampling.
NORECOMPUTE: Prevents automatic statistics recomputation by SQL Server for the specified statistics. This is useful when you want to manually manage statistics updates.
INCREMENTAL = ON | OFF: When set to ON, statistics are updated incrementally per partition. This is beneficial for large partitioned tables as only changed partitions are scanned.

Examples:

Update all statistics for a table using default sampling:
```
UPDATE STATISTICS dbo.YourTableName;
```
Update a specific statistic with a full scan:
```
UPDATE STATISTICS dbo.YourTableName YourIndexOrStatisticName WITH FULLSCAN;
```
- Note: Replace YourTableName and YourIndexOrStatisticName with your actual table and statistic names. You can find statistic names by expanding the Statistics folder under a table in SSMS or querying sys.stats.
Update all statistics for a table with 10% sampling and prevent auto-updates:
```
UPDATE STATISTICS dbo.YourTableName WITH SAMPLE 10 PERCENT, NORECOMPUTE;
```

Update all statistics in a partitioned table incrementally:

UPDATE STATISTICS dbo.YourPartitionedTable WITH INCREMENTAL = ON;

For more details on UPDATE STATISTICS, refer to the Microsoft Learn documentation.

2. `sp_updatestats` Stored Procedure

The sp_updatestats system stored procedure updates all statistics in the current database that need to be updated. It identifies statistics that have become stale due to data modifications and updates them using the default sampling method (which could be SAMPLE or FULLSCAN depending on the data modification and distribution).

Syntax:

EXEC sp_updatestats;

Usage Notes:

sp_updatestats is simpler to use as it updates all necessary statistics without requiring you to specify individual table or statistic names.
It typically runs faster than manually updating every statistic with FULLSCAN because it uses intelligent sampling and only targets stale statistics.
You can schedule sp_updatestats as part of your regular database maintenance plan.

For more information on sp_updatestats, visit the Microsoft Learn page.

Method 2: Refreshing Statistics Using SQL Server Management Studio (SSMS) GUI

For a visual, point-and-click approach, you can update individual statistics directly within SQL Server Management Studio. This method is useful for one-off updates or when you need to specifically examine statistics properties.

Steps to Update Specific Statistics in SSMS:

Connect to your SQL Server instance in SQL Server Management Studio.
Navigate to your database in the Object Explorer.
Expand the "Tables" folder and then locate the specific table for which you want to update statistics.
Expand the "Statistics" folder under your chosen table. (You will see a plus sign next to the folder; click it to expand.)
Right-click the specific statistics object you wish to update.
Select "Properties" from the context menu.
In the "Statistics Properties - statistics_name" dialog box, you will see options for the statistics. To update them, select the "Update statistics for these columns" check box.
You can also optionally choose to update with a FULLSCAN by selecting the "Full scan" radio button if available, otherwise it performs a default sample-based update.
Select "OK" to apply the update.

This process updates the selected statistics for the specified columns, improving the optimizer's knowledge of the data distribution.

Why Refresh Statistics?

Understanding why you refresh statistics is as important as how.

Improved Query Plans: The SQL Server query optimizer uses statistics to estimate the cardinality (number of rows) that will be returned by an operation. Accurate estimates lead to better choices for join types, join order, and index usage.
Faster Query Performance: When the optimizer has precise information, it generates efficient execution plans, resulting in faster query execution times.
Adaptation to Data Changes: As data in your tables changes (inserts, updates, deletes), the existing statistics become outdated. Refreshing them ensures the optimizer uses the most current data distribution.

When to Refresh Statistics

Consider refreshing statistics in the following scenarios:

Significant Data Modifications: After a large number of rows have been inserted, updated, or deleted in a table.
After Index Rebuilds or Reorganizations: While an index rebuild updates the statistics for that specific index, it might not update other column statistics. Reorganizations typically do not update statistics.
Poor Query Performance: If a previously fast query suddenly becomes slow, stale statistics could be a culprit.
Regular Maintenance: Implement a schedule for updating statistics, either nightly or weekly, depending on the database's activity level.
After Schema Changes: Adding or removing columns, or changing data types, can affect statistics.

Automatic Statistics Update

SQL Server has built-in mechanisms for automatic statistics updates:

AUTO_UPDATE_STATISTICS: This database option, which is ON by default, tells SQL Server to automatically update statistics when the query optimizer determines they are stale. This happens synchronously, meaning the query waits for the statistics to be updated.
AUTO_UPDATE_STATISTICS_ASYNC: When this option is ON (also ON by default in new databases since SQL Server 2016), queries that encounter stale statistics will proceed using the existing (stale) statistics, while new statistics are computed in the background. This avoids query delays but means the query might run with suboptimal plans until the new statistics are ready.

While automatic updates are helpful, they might not always be sufficient for highly active databases or complex workloads, making manual or scheduled updates necessary.

Best Practices for Statistics Management

Monitor Statistics Freshness: Use Dynamic Management Views (DMVs) like sys.dm_db_stats_properties to monitor when statistics were last updated and the number of modifications.
Schedule Regular Updates: Incorporate sp_updatestats or UPDATE STATISTICS commands into your SQL Server Agent jobs or maintenance plans.
Consider FULLSCAN for Critical Tables: For smaller, highly critical tables, or when data distribution is very uneven, FULLSCAN can provide superior accuracy.
Test and Benchmark: Always test the impact of statistics updates on query performance in a non-production environment before applying them to production.
Identify Missing Statistics: The query optimizer might sometimes need statistics on columns that don't have indexes. Monitor for "Missing Index" suggestions, as these often imply missing statistics.

Comparison: T-SQL vs. SSMS GUI

Feature	T-SQL Commands (`UPDATE STATISTICS`, `sp_updatestats`)	SQL Server Management Studio (SSMS) GUI
Control & Flexibility	High (granular options like `FULLSCAN`, `SAMPLE`, `NORECOMPUTE`, `INCREMENTAL`)	Limited (basic update, often uses default sampling, `FULLSCAN` might be an option)
Automation	Excellent (scripting, SQL Server Agent jobs)	Poor (manual, click-based)
Scalability	Excellent (can target all databases, tables, or specific statistics)	Good for individual statistics
Learning Curve	Moderate (requires understanding syntax and options)	Low (intuitive, visual)
Use Cases	Scheduled maintenance, large-scale updates, specific sampling requirements, advanced scenarios	One-off updates, quick checks, beginners

By understanding and implementing these methods for refreshing statistics, you can ensure your SQL Server database maintains optimal query performance and responds efficiently to data changes.

How to refresh statistics in SQL Server?