Monitoring Databases  «Prev  Next»

Lesson 8 Maintaining database statistics
Objective Maintain statistics.

Maintain Database Index Statistics in SQL Server 2025

SQL Server keeps statistics on the data distribution of every index and, optionally, every column in a table. If you are retrieving customer rows where ZipCode = '80013', those statistics are what tell the query optimizer roughly how many rows to expect back, which in turn decides which index it reaches for and in what order it joins tables. Get that estimate badly wrong and the optimizer can choose a plan that looks reasonable on paper and runs terribly in practice, a scan where a seek belonged, or a join order that processes the largest table first instead of last.

SQL Server can maintain these statistics automatically, or they can be created, updated, and dropped by hand. This lesson covers all four of the commands that matter here, CREATE STATISTICS, ALTER DATABASE for auto-create, DROP STATISTICS, and UPDATE STATISTICS, along with how automatic updating actually decides when to act, a detail that has a real, often-missed version dependency, and how to check that decision directly with a dynamic management view rather than guessing at it.

This lesson ties directly back to two threads already running through this course: the query optimizer's job, which earlier lessons on monitoring tools like Activity Monitor and the execution-plan DMVs touched on from the outside, and indexing itself, since an index and its statistics are closely related but not the same thing. An index is a physical structure SQL Server navigates to find rows; its statistics are a separate, much smaller summary of what values live in that structure and how often. SQL Server can have excellent indexes and still produce a bad plan if the statistics describing those indexes are stale, which is exactly the scenario this lesson is about avoiding.

CREATE STATISTICS: Building the Histogram and Density Information by Hand

CREATE STATISTICS builds a statistics object on one or more columns, gathering two pieces of information the optimizer later reads: a histogram, a distribution of values across ranges, and density, a measure of how many distinct values exist in the column, where lower density means higher uniqueness.

CREATE STATISTICS statistics_name
ON table_name (column1 [ , ...n ])
    [ WHERE <filter_predicate> ]
    [ WITH
        [ FULLSCAN [ , PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS } [ , PERSIST_SAMPLE_PERCENT = { ON | OFF } ] ]
        [ , NORECOMPUTE ]
        [ , INCREMENTAL = { ON | OFF } ]
        [ , MAXDOP = max_degree_of_parallelism ]
        [ , AUTO_DROP = { ON | OFF } ]
    ]
  • statistics_name: a unique name identifying the statistics group.
  • table_name (column1 [, ...n]): the table and the column or columns to build statistics on, up to 64 columns. The first column drives the histogram; every column contributes to density information used for cross-column correlation.
  • WHERE <filter_predicate>: creates filtered statistics, scoped to a subset of rows matching a simple comparison. Useful on a table where one value dominates and the interesting rows are the minority.
  • FULLSCAN: scans every row; equivalent to SAMPLE 100 PERCENT.
  • SAMPLE number PERCENT | ROWS: scans an approximate percentage or row count instead of the whole table. Left out, the optimizer chooses a sample size on its own.
  • PERSIST_SAMPLE_PERCENT: when ON, a later update that does not specify a sample size reuses this creation sample percentage instead of letting the optimizer pick again. Default is OFF.
  • NORECOMPUTE: disables automatic updating for this specific statistics object. Microsoft's own guidance is to use this sparingly, since it can lead to stale statistics and suboptimal plans; reversing it means dropping and recreating the statistics without this option.
  • INCREMENTAL: ON builds per-partition statistics on a partitioned table instead of one combined set. Not supported for filtered statistics, indexed views, spatial or XML indexes, or a non partition-aligned index.
  • MAXDOP: caps parallelism for the operation itself, independent of the server's configured setting.
  • AUTO_DROP: lets a schema change that would otherwise be blocked by this statistics object proceed by dropping the statistics instead. Enabled by default on databases created in SQL Server 2022 and later.
CREATE STATISTICS requires ALTER TABLE permission on the table, table ownership, or membership in the db_ddladmin fixed database role.

It is a good practice to create statistics on columns that are not part of any index but appear in a WHERE clause or a JOIN condition, since the optimizer otherwise has no distribution information for them at all.

CREATE STATISTICS CustStat1 ON dbo.Customers (ZipCode);
Creating statistics on more than one column at once is also worth knowing about, because the information it produces is genuinely different from statistics on each column separately. The histogram still only describes the first column listed, but the density information describes how the columns correlate with each other, which helps the optimizer when a query filters or joins on both together.

CREATE STATISTICS CustStat2 ON dbo.Customers (State, ZipCode);
Without that combined statistics object, the optimizer has to assume State and ZipCode vary independently of each other, an assumption that is usually wrong since a given ZipCode almost always belongs to exactly one State, and an assumption that can lead it to underestimate or overestimate the rows a query filtering on both will actually return.

Auto Create Statistics

Data distribution statistics are central to how the query optimizer builds execution plans, which is why AUTO_CREATE_STATISTICS is a database level setting, on by default, that lets SQL Server create column level statistics automatically whenever the optimizer determines they would help answer a query.

ALTER DATABASE SalesDB
SET AUTO_CREATE_STATISTICS ON;
Turning this off is rarely the right call outside of a specific, measured scenario; it leaves the optimizer working from gaps in its own distribution information on any column that was never explicitly given statistics by hand, a self-inflicted blind spot with no corresponding benefit for most workloads.

DROP STATISTICS: Removing a Statistics Object

DROP STATISTICS removes a statistics object created by CREATE STATISTICS or by automatic creation.

DROP STATISTICS table_name.statistics_name [ , ...n ];
DROP STATISTICS dbo.Customers.CustStat1;
DROP STATISTICS requires ALTER permission on the table. One limitation worth knowing before reaching for this command: it cannot drop the statistics SQL Server automatically maintains on an index. Index based statistics exist for as long as the index itself exists; removing them means dropping the index, not calling DROP STATISTICS against it.

UPDATE STATISTICS: Refreshing Existing Statistics

UPDATE STATISTICS table_name
    [ ( statistics_name [ , ...n ] ) | index_name ]
    [ WITH
        [ FULLSCAN [ , PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS } [ , PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | RESAMPLE [ ON PARTITIONS ( partition_number [ , ...n ] ) ] ]
        [ , { ALL | COLUMNS | INDEX } ]
        [ , NORECOMPUTE ]
        [ , INCREMENTAL = { ON | OFF } ]
        [ , MAXDOP = max_degree_of_parallelism ]
        [ , AUTO_DROP = { ON | OFF } ]
    ]
Leaving out a specific statistics name or index name updates every statistics object on the table. RESAMPLE reuses each statistic's own most recently used sample rate rather than letting the optimizer choose again, which can trigger a full scan if that rate happened to be high. ALL, COLUMNS, and INDEX scope an update to every statistics object, only column level ones, or only index level ones, respectively.

UPDATE STATISTICS requires ALTER permission on the table or view, not sysadmin or db_owner as some older material assumes; statistics commands sit on a lighter permission tier than the DBCC commands covered earlier in this module.

-- Update every statistics object on the table
UPDATE STATISTICS dbo.Customers;

-- Full scan, the most accurate option
UPDATE STATISTICS dbo.Customers WITH FULLSCAN;

-- A faster, sampled update
UPDATE STATISTICS dbo.Customers WITH SAMPLE 30 PERCENT;

-- Every statistics object in the current database
EXEC sp_updatestats;
A related point worth making explicit, since it trips people up when planning an index maintenance window: rebuilding an index updates that index's statistics with a full scan as a side effect, while reorganizing an index does not touch statistics at all.

ALTER INDEX IX_Customers_ZipCode ON dbo.Customers REBUILD;

How Automatic Updating Actually Decides When to Run

AUTO_UPDATE_STATISTICS, on by default, is what lets the query optimizer detect stale statistics and refresh them before compiling a query against them. What counts as "stale" has changed over time, and the older rule is still what actually applies to a given database unless a specific setting says otherwise, a detail worth getting right rather than assuming.

The original, long standing rule: statistics are considered stale once roughly 500 rows plus 20 percent of the table's rows have changed. For a very large table, that is a genuinely large number of changes, millions on a hundred million row table, which can leave statistics meaningfully out of date for a long stretch between updates.

SQL Server 2016 introduced a more responsive rule, commonly described as the lower of that old threshold and a formula based on the square root of the table's row count: MIN(500 + 0.20 x rows, SQRT(1000 x rows)). For a 100 million row table, that drops the effective threshold from roughly 20 million changed rows under the old rule to roughly 316,000 under the new one, triggering updates far more often on large tables specifically, exactly the tables where stale statistics do the most damage to a plan.

Here is the detail worth being explicit about, since it is easy to assume incorrectly: the newer threshold is not automatic just because the server itself is running a current version. It applies by default only when the database's compatibility level is 130 or higher. A database that has been running on current SQL Server for years but was never actually switched to a higher compatibility level after an in-place upgrade keeps using the original 500-row-plus-20-percent rule, regardless of which SQL Server version is installed underneath it, unless trace flag 2371 is explicitly turned on to force the newer behavior at a lower compatibility level anyway.

SELECT name, compatibility_level
FROM sys.databases
WHERE name = 'SalesDB';
Checking this one setting is worth doing before assuming automatic statistics updates are behaving the way current documentation describes, especially on a database with a long upgrade history rather than one created fresh on the current version.

Raising the compatibility level is a one-line command, but it is not a change to make casually just to pick up the newer statistics threshold on its own:

ALTER DATABASE SalesDB SET COMPATIBILITY_LEVEL = 170;
Compatibility level controls far more than statistics behavior; it switches the entire query optimizer's behavior to match that version, which can change the actual execution plans chosen for existing queries, for better or for worse. Testing a compatibility level change against a representative workload before applying it to a production database is the standard, cautious approach, the same caution this course has applied to every other change with a broad blast radius. Trace flag 2371, mentioned above, exists specifically for a team that wants the newer statistics threshold without taking on everything else a full compatibility level change brings with it.

Monitoring Statistics Directly: sys.dm_db_stats_properties

Rather than guessing at whether a given table's statistics are stale, sys.dm_db_stats_properties reports the actual state of a specific statistics object: when it was last updated, how many rows were sampled, and critically, modification_counter, the number of changes to the leading statistics column since that last update, the same number the thresholds above are measured against.

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS SchemaName,
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatisticsName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECT_NAME(s.object_id) = 'Customers';
This requires SELECT permission on the table, table ownership, or membership in sysadmin, db_owner, or db_ddladmin. Checking modification_counter against the actual threshold formula above, rather than assuming a nightly job is keeping up, is the more reliable way to know whether a table's statistics genuinely need attention right now.

Putting It Together: A Short Diagnostic Walkthrough

Seeing these pieces used in the order an actual investigation would use them makes the lesson more concrete than treating each command as a separate fact to memorize.

  1. A query against dbo.Customers suddenly runs slower than usual, and a quick look at its execution plan shows an estimated row count far smaller than the actual row count returned, a classic sign of stale or missing statistics.
  2. Check the statistics directly with sys.dm_db_stats_properties rather than guessing. The query shown earlier in this lesson shows CustStat1 was last updated three weeks ago, with a modification_counter in the hundreds of thousands against a table of two million rows.
  3. Check the compatibility level of SalesDB with the query shown above. It comes back at 120, left over from an in-place upgrade years ago, which means the newer, more responsive threshold has never actually been active on this database, and the old 500-row-plus-20-percent rule has been quietly governing automatic updates the entire time.
  4. Update the statistics by hand rather than waiting on an automatic update that, at this compatibility level, needs a much larger number of changed rows to trigger. UPDATE STATISTICS dbo.Customers WITH FULLSCAN; resolves the immediate problem.
  5. Decide on the underlying fix. Raising the compatibility level, after testing, or enabling trace flag 2371 as a narrower alternative, addresses the root cause rather than just this one table, so the same investigation does not repeat itself on the next table that drifts out of date.
Five steps, each answering one specific question before moving to the next, is the same discipline this course's earlier lesson on the transaction log used for its own walkthrough; the tools differ, but the sequencing, diagnose first, then fix, then address the cause, does not.

Best Practices and Third-Party Tooling

A short list of practical guidance worth carrying forward from this lesson: prefer targeted updates on tables that actually need them over blanket nightly updates on everything, since an unnecessary UPDATE STATISTICS forces query recompilation the next time each affected query runs, a real cost on a busy server with no corresponding benefit. Use FULLSCAN for a critical, predictable report where accuracy matters more than speed, and a SAMPLE for a large table where speed matters more than squeezing out the last bit of precision. Avoid NORECOMPUTE without a specific, measured reason; the automatic updating this lesson just covered, once the compatibility level question above is settled, is generally the better default to lean on.

Two community maintained tools are worth knowing by name, since they come up often in real maintenance routines, though neither is a built-in SQL Server command: Ola Hallengren's free IndexOptimize script and Microsoft's own Adaptive Index Defrag script, both of which schedule index and statistics maintenance intelligently based on measured fragmentation and modification counts rather than a blind nightly rebuild of everything on the server. Both require downloading and installing separately; neither ships with SQL Server itself, so they are worth recognizing by name in an existing maintenance plan rather than assuming they are native T-SQL.

One last distinction worth keeping straight, since this lesson and the earlier one on fragmentation can otherwise blur together: an index rebuild or reorganize addresses physical fragmentation, how scattered an index's pages are on disk, while statistics maintenance addresses a completely separate question, whether the optimizer's summary of the data's values is still accurate. A perfectly defragmented index can still sit behind badly stale statistics, and a heavily fragmented index can sit behind statistics that are perfectly current. Treating the two as one maintenance task, rather than two related but independent ones, is a common and avoidable source of confusion when something still runs slowly after one of them was addressed but not the other.

What's Ahead

The next lesson covers how to view the statistics information this lesson created and maintained, reading the actual histogram and density values back out of a statistics object rather than only creating or refreshing them.

SEMrush Software 8 SEMrush Banner 8