How to Update Statistics in SQL Server

calendar Jun 17, 2026 / · 11 min read · sql server update statistics sp_updatestats sys.dm_db_stats_properties sys.stats sys.objects statistics query optimizer maintenance scripts performance cardinality estimation database maintenance scripts  ·
Share on: linkedin copy

Statistics are the optimizer's picture of the data, and that picture ages every time rows change. When a table grows faster than its statistics are refreshed, plans keep being built for the row counts it held weeks ago. Refreshing the statistics is often the fix, and the work is knowing which ones to refresh and with what scan mode.

Purpose and Overview

A nightly load job finishes, the morning report runs against yesterday's rows, and the plan that was fine at 10 million rows now chooses an index seek and a key lookup for a predicate that matches two million. The optimizer is not broken. It estimated from a histogram that predates the load, so it believed the predicate was selective. The Query Optimizer uses statistics to estimate the number of rows in a query result, and those cardinality estimates let it choose, for example, an index seek over a more expensive index scan.

Each statistics object is built on one or more columns and holds a histogram of the values in the first column. Statistics on several columns also store density information about how values correlate across the columns. When AUTO_UPDATE_STATISTICS is on, the Query Optimizer counts row modifications since the last update and compares that count to a threshold, then updates the statistics when a query needs them. The optimizer also checks for out-of-date statistics before compiling a query and before executing a cached plan. Manual updates fill the gaps: tables where the automatic threshold fires too late, tables just after a bulk load, and tables where a particular sample size matters.

The workflow has two halves. First, find the statistics that have changed, using sys.dm_db_stats_properties joined to sys.stats and sys.objects. Second, update them with UPDATE STATISTICS for one table, index, or statistics object, or sp_updatestats for the whole database. For a script that generates the update statements per table, see the dynamic update-statistics script.

Code Breakdown

Start with a query that lists user statistics with pending modifications, largest first:

 1SELECT
 2    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
 3    OBJECT_NAME(s.object_id)        AS table_name,
 4    s.name                          AS stats_name,
 5    sp.last_updated,
 6    sp.rows,
 7    sp.rows_sampled,
 8    sp.modification_counter
 9FROM sys.stats AS s
10INNER JOIN sys.objects AS o
11    ON s.object_id = o.object_id
12CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
13WHERE o.is_ms_shipped = 0
14    AND sp.modification_counter > 0
15ORDER BY sp.modification_counter DESC;

Then update what needs it, choosing the scan mode deliberately:

1-- Targeted: one statistics object with a full scan
2UPDATE STATISTICS Sales.SalesOrderDetail (IX_SalesOrderDetail_ProductID)
3    WITH FULLSCAN;
4
5-- One table, default sampling (the optimizer picks the sample size)
6UPDATE STATISTICS Sales.SalesOrderHeader;
7
8-- Whole database, modification-aware for disk-based tables
9EXEC sp_updatestats;

The object and index names in the update examples come from the AdventureWorks sample database. Substitute your own table and statistics names.

Finding stale statistics with sys.dm_db_stats_properties

sys.stats returns one row per statistics object. Every index has a matching row where stats_id equals index_id, but not every statistics row has an index behind it, because column statistics created automatically or with CREATE STATISTICS have no index. The diagnostic query calls sys.dm_db_stats_properties once per row through CROSS APPLY, which is the standard pattern for this DMV.

The columns that matter are these:

  • last_updated: when the statistics object was last updated. It is NULL when no data was read to build the statistics, such as a new empty table or a filtered statistic whose predicate matches no rows.
  • rows: the total number of rows in the table or indexed view when the statistics were last updated.
  • rows_sampled: the number of rows sampled. Comparing it to rows shows how thorough the last update was.
  • modification_counter: the number of modifications to the leading statistics column, the column the histogram is built on, since the last update.

The function returns an empty rowset instead of an error when the object is not found, is not a table or indexed view, or when the caller lacks permission to view the statistics. That is why it is safe inside CROSS APPLY: rows it cannot describe simply drop out. Permission to see a statistics object requires SELECT on the statistics columns, ownership of the table, or membership in sysadmin, db_owner, or db_ddladmin. If the result looks suspiciously short, check the permissions of the account running it.

The WHERE o.is_ms_shipped = 0 filter removes objects created by internal SQL Server components. sp.modification_counter > 0 removes statistics nothing has touched.

UPDATE STATISTICS scan modes

UPDATE STATISTICS takes three sampling choices, and they are mutually exclusive:

  • FULLSCAN reads every row. FULLSCAN and SAMPLE 100 PERCENT have the same results.
  • SAMPLE n PERCENT or SAMPLE n ROWS asks for an approximate share of the rows. The actual number can differ, because the optimizer scans whole data pages. Avoid 0 PERCENT or 0 ROWS, since the statistics object is then updated but contains no statistics data.
  • RESAMPLE updates each statistic using its most recent sample rate. This can result in a full-table scan, for example for index statistics that were built with a full scan.

With none of the three specified, the optimizer samples and computes the sample size itself. For most workloads a full scan is not required and default sampling is adequate. Workloads sensitive to widely varying data distributions may need a larger sample or a full scan, though complex plans may not benefit much even then.

The statement also accepts ALL, COLUMNS, or INDEX to limit the update to all existing statistics, only column statistics, or only index statistics. With none of them, it updates all statistics on the table or indexed view.

Sweeping a database with sp_updatestats

sp_updatestats runs UPDATE STATISTICS with the ALL keyword against all user-defined and internal tables in the current database. For disk-based tables it reads modification_counter from sys.dm_db_stats_properties and updates statistics where at least one row has been modified. For memory-optimized tables it updates all statistics unconditionally. Passing 'resample' makes it use the RESAMPLE option; otherwise it uses default sampling.

Other behaviour: it updates statistics on disabled nonclustered indexes but not on disabled clustered indexes, it preserves the latest NORECOMPUTE setting of each statistic, and it can trigger recompiles of stored procedures and other compiled code. Permissions are ownership of the database (dbo) or sysadmin membership on SQL Server. After restoring a database to SQL Server 2022 from an earlier version, run sp_updatestats on it to set the right metadata for the statistics auto-drop feature.

NORECOMPUTE, MAXDOP and incremental options

WITH NORECOMPUTE updates the statistic and then disables AUTO_UPDATE_STATISTICS for that statistic. It can produce suboptimal query plans, so use it sparingly and only by a qualified system administrator. Running UPDATE STATISTICS again without NORECOMPUTE, or running sp_autostats, re-enables the behaviour.

WITH MAXDOP = n overrides the server's max degree of parallelism for the duration of the statistics operation. It applies to SQL Server starting with 2016 SP2 and 2017 CU3, and is limited by the Resource Governor workload group MAX_DOP setting if one is in use. INCREMENTAL = ON and RESAMPLE ON PARTITIONS apply to partitioned tables on SQL Server 2014 and later, and some statistics types cannot be incremental, including statistics on views and on read-only databases.

Why the automatic threshold lags

The automatic-update thresholds explain why a large table can drift between automatic updates. Up to SQL Server 2014, or under database compatibility level 120 and lower, a permanent table with more than 500 rows is refreshed after 500 + (0.20 * n) modifications. At 20,000 rows that is 4,500 modifications. Starting with SQL Server 2016 at compatibility level 130, the threshold is MIN(500 + (0.20 * n), SQRT(1,000 * n)) for tables over 500 rows. At 2 million rows the old formula gives 400,500, the new one 44,721, so the update fires at 44,721 modifications.

That is far sooner than the old rule, yet a table that takes a steady trickle of inserts at the top of its key range can still sit below the threshold while its newest rows are the ones queries ask about. Statistics on ascending or descending key columns, such as IDENTITY or real-time timestamp columns, might need more frequent updates than the optimizer performs, because the number of rows added can be too small to trigger an update and queries that select the most recently added rows have no histogram coverage for them. Those are the tables a scheduled manual update targets.

Persisting the sample rate

PERSIST_SAMPLE_PERCENT = ON makes a statistic retain its sampling percentage for later updates that do not specify one, including automatic updates. With OFF, the default, the next update without an explicit percentage resets to default sampling. It applies to SQL Server 2016 SP1 CU4, 2017 CU1 and later. sys.dm_db_stats_properties exposes the stored value as persisted_sample_percent, where zero means none is set, and DBCC SHOW_STATISTICS exposes it as well.

It resets when the table is truncated or when statistics are updated on an object with no rows. Rebuilding an index reset the persisted percentage on older builds; SQL Server 2016 SP2 CU17, 2017 CU26 and 2019 CU10 changed that so the value is kept.

Key Benefits and Use Cases

  • Restores plan quality: fresh statistics give the optimizer accurate row estimates, which corrects regressions caused by data growth or a bulk load.
  • Diagnose before acting: sys.dm_db_stats_properties shows which statistics have accumulated modifications, so you update those and leave static tables alone.
  • Granular or broad: UPDATE STATISTICS for a single statistics object, sp_updatestats for the whole database.
  • Controllable accuracy: FULLSCAN, SAMPLE, and RESAMPLE trade update cost against histogram precision.
  • After bulk operations: consider an update after operations that change the data distribution, such as truncating a table or bulk inserting a large percentage of its rows.
  • Ascending keys: a scheduled update of the statistics on identity and timestamp columns covers the gap the automatic threshold leaves.

Performance Considerations

  • Updating statistics can recompile plans: an update by any process can cause query plans to recompile, so weigh better plans against the time spent recompiling and do not update more often than the data changes.
  • tempdb use: UPDATE STATISTICS can use tempdb to sort the sample of rows. A FULLSCAN on a large table reads every row, so schedule it outside peak hours.
  • Rebuilds already update index statistics: the optimizer updates statistics when you run ALTER INDEX REBUILD, as a byproduct of re-creating the index, and does not update them after ALTER INDEX REORGANIZE. Rebuilding or reorganizing does not change the data distribution, so there is no need to update statistics after those operations. For partitioned indexes on SQL Server 2014 and later, the rebuild uses default sampling instead of a full scan.
  • Memory-optimized tables: sp_updatestats updates all of their statistics unconditionally, and AUTO_UPDATE_STATISTICS is always off for them, so the call has a cost every time.
  • Columnstore limits: updating the statistics created automatically on a columnstore index is not supported and raises error 35337. Statistics on individual columns of a columnstore index can be updated.
  • Sampling and parallelism history: at database compatibility level 130 on SQL Server 2016, statistics sampling ran in parallel. From SQL Server 2017, regardless of compatibility level, it went back to a serial scan to avoid excessive latch waits, though the rest of the plan can still run in parallel.

Practical Tips

  • Use a modification-aware job: a nightly blanket FULLSCAN wastes I/O on static tables. sp_updatestats skips unmodified disk-based tables, and the IndexOptimize procedure in the Maintenance Solution has an OnlyModifiedStatistics option that checks modification_counter in sys.dm_db_stats_properties.
  • Set a ratio, not just a count: compare modification_counter to rows from the diagnostic query and update when the ratio passes a limit you pick per table, rather than relying on age alone.
  • Leave AUTO_UPDATE_STATISTICS on: it is the safety net. With it off, statistics only change through manually scheduled processes such as maintenance plans, and plans keep using out-of-date statistics.
  • Consider asynchronous updates for latency-sensitive systems: with AUTO_UPDATE_STATISTICS_ASYNC, queries compile with the existing statistics instead of waiting, and the optimizer may pick a suboptimal plan until the update finishes. The default is synchronous.
  • Check one statistic quickly: STATS_DATE(object_id, stats_id) returns the last update time without the full join.
  • Set the sample once: if a table needs a sample larger than the default, use PERSIST_SAMPLE_PERCENT = ON so later automatic updates keep it.

Conclusion

Stale statistics cause plan regressions that look like code problems. Query sys.dm_db_stats_properties to see which statistics have drifted, update them with UPDATE STATISTICS and the scan mode that fits the table, and use sp_updatestats for a database-wide pass. Keep AUTO_UPDATE_STATISTICS on, and add manual updates for large tables, bulk loads, and ascending keys.

References

Posts in this series