How to Update Statistics in SQL Server

calendar Jun 17, 2026 / · 6 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

A query that ran in milliseconds last month now scans a million rows and spills to tempdb. Nothing changed in the code — but the data grew, and the statistics the optimizer relies on went stale, so its row estimates drifted far from reality and it chose a bad plan. Refreshing statistics is the fix, and knowing how and when to do it is core DBA work.

Purpose and Overview

SQL Server's query optimizer is cost-based: it estimates how many rows each operator will process and picks a plan accordingly. Those estimates come from statistics objects — histograms and density vectors that describe the distribution of values in a column or index. When data changes but statistics do not, estimates degrade, and the optimizer can choose nested loops where a hash join belongs, or under-size a memory grant.

SQL Server updates statistics automatically when AUTO_UPDATE_STATISTICS is on, but the automatic threshold is based on accumulated modifications and can lag behind on large or skewed tables. That is why DBAs run manual updates: UPDATE STATISTICS for targeted control over a table, index, or single statistics object, and sp_updatestats to sweep an entire database in one call.

The first step, though, is to find what is stale rather than blindly updating everything. The dynamic management function sys.dm_db_stats_properties exposes the last-updated timestamp, the row count at last update, the sampled rows, and a modification_counter of changes since. This guide pairs that diagnostic with the update commands. For a script that generates per-table update statements programmatically, see the dynamic update-statistics script.

Code Breakdown

Start by identifying statistics with pending modifications, ordered by how much has changed:

 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 right scan mode:

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 (optimizer picks the sample size)
6UPDATE STATISTICS Sales.SalesOrderHeader;
7
8-- Whole database, modification-aware
9EXEC sp_updatestats;

Finding stale statistics with sys.dm_db_stats_properties

The diagnostic query joins sys.stats (one row per statistics object) to sys.dm_db_stats_properties, a function called once per statistics object via CROSS APPLY. The modification_counter counts changes to the leading statistics column since the last update; a high value relative to rows is the signal that estimates may be drifting. last_updated shows age, and comparing rows_sampled to rows reveals how thorough the last update was.

UPDATE STATISTICS scan modes

UPDATE STATISTICS accepts three sampling options. FULLSCAN reads every row for the most accurate histogram, at the highest cost. SAMPLE n PERCENT (or ROWS) reads a subset — useful when the default plan is poor but a full scan is too expensive. RESAMPLE reuses each statistic's most recent sample rate, which keeps index statistics at their full-scan rate. With no option specified, the optimizer computes a default sample size automatically.

Sweeping a database with sp_updatestats

sp_updatestats runs UPDATE STATISTICS ... WITH ALL against every user and internal table, but it is modification-aware: for disk-based tables it only updates statistics where the modification_counter shows at least one changed row, so it does not waste work on static tables. Passing 'resample' makes it use the RESAMPLE option. It is the simplest whole-database refresh, though it offers no per-object control over scan rate.

Targeting with NORECOMPUTE and MAXDOP

For special cases, WITH NORECOMPUTE updates a statistic and then disables its automatic updates — use sparingly, as it can lead to stale estimates. WITH MAXDOP = n caps the parallelism of the update operation itself on supported versions.

Why the automatic threshold lags

AUTO_UPDATE_STATISTICS fires when accumulated modifications cross a threshold, tracked by the same modification_counter the diagnostic query reads. On modern compatibility levels the threshold scales roughly with the square root of the table's row count, so it triggers far sooner on large tables than the old flat "20% of rows" rule did. Even so, a very large or steadily growing table can drift between automatic refreshes, and an ascending key — such as an IDENTITY or date column where new rows always fall past the top of the histogram — is the classic case the histogram fails to capture until it is refreshed. Those are exactly the tables a scheduled manual update targets.

Persisting the sample rate

When a table needs a non-default sample to estimate well, WITH SAMPLE n PERCENT, PERSIST_SAMPLE_PERCENT = ON records the rate so that later automatic updates reuse it instead of reverting to the default. This avoids the trap where a carefully chosen sample is silently undone the next time AUTO_UPDATE_STATISTICS runs. DBCC SHOW_STATISTICS and sys.dm_db_stats_properties both expose the persisted percentage so you can confirm it took effect.

Key Benefits and Use Cases

  • Restores plan quality — fresh statistics give the optimizer accurate row estimates, fixing regressions caused by data growth.
  • Diagnose before acting — sys.dm_db_stats_properties shows exactly which statistics are stale, so you update only what matters.
  • Granular or broad — UPDATE STATISTICS for a single object, sp_updatestats for the whole database.
  • Controllable accuracy — FULLSCAN, SAMPLE, and RESAMPLE trade cost against histogram precision.
  • Complements index maintenance — an index rebuild updates that index's statistics with a full scan, but column statistics still need separate attention.

Performance Considerations

  • Updating statistics triggers recompiles: plans that depend on the refreshed statistics recompile on next execution, which is usually beneficial but adds transient CPU cost — avoid updating constantly.
  • FULLSCAN is I/O-heavy: on large tables it reads every row and can use tempdb for sorting. Reserve it for tables where sampled estimates are demonstrably wrong.
  • An index rebuild already refreshes index statistics: running UPDATE STATISTICS WITH FULLSCAN immediately after a rebuild duplicates work. Sequence maintenance so you do not update the same statistics twice.
  • Memory-optimized tables differ: sp_updatestats always updates statistics on memory-optimized tables, so do not call it more often than necessary.
  • Sampling can miss skew: default sampling is fine for most workloads, but heavily skewed data may need a larger sample or a full scan to estimate well.

Practical Tips

  • Schedule statistics maintenance with a modification-aware solution rather than a blanket nightly FULLSCAN; community maintenance tooling can update only statistics whose rows have changed.
  • Use the diagnostic query to set a threshold — for example, update when modification_counter exceeds a percentage of rows — instead of relying on age alone.
  • Keep AUTO_UPDATE_STATISTICS on as a safety net even if you also maintain statistics manually; the two are complementary.
  • Consider AUTO_UPDATE_STATISTICS_ASYNC for OLTP systems to avoid query stalls while statistics refresh, accepting that the triggering query uses the older statistics.
  • Use STATS_DATE(object_id, stats_id) for a quick last-updated check on a single statistic without the full DMV join.

Conclusion

Stale statistics are a common, quietly destructive cause of plan regressions, and the cure is straightforward once you know where to look. Use sys.dm_db_stats_properties to find what has drifted, then UPDATE STATISTICS with the appropriate scan mode for targeted fixes or sp_updatestats for a database-wide refresh. Pair manual maintenance with AUTO_UPDATE_STATISTICS and sensible thresholds, and the optimizer keeps getting the accurate row estimates it needs.

References

Posts in this series