The ticket says the report ran in four seconds on Friday and eleven minutes on Monday. Nobody deployed code. Nobody changed an index. The execution plan looks different, and the difference is a nested loop join where there used to be a hash join. The optimizer picked the loop because it thought the outer input would return 1 row. It returned 184,000.
That estimate came from a statistics object whose histogram stopped describing the table three days ago. Nothing ran UPDATE STATISTICS against it over the weekend, and the auto-update threshold is still a long way off. Cardinality estimates drive join type, join order, memory grant, seek versus scan, and parallelism. Estimates come from statistics. When statistics drift, everything downstream drifts with them, and the symptom looks like a query that broke on its own.
Find the stale statistics first
sys.dm_db_stats_properties takes an object_id and a stats_id and gives you the four numbers that matter: when the statistics object was last updated, how many rows the table had at that moment, how many of those rows the engine read, and how many modifications have hit the leading column since. It shipped in SQL Server 2008 R2 SP2 and SQL Server 2012 SP1, so it exists on anything you are still running.
SELECT
sch.name AS schema_name,
o.name AS object_name,
st.name AS stats_name,
st.auto_created,
st.has_filter,
sp.last_updated,
sp.[rows],
sp.rows_sampled,
CAST(100.0 * sp.rows_sampled / NULLIF(sp.[rows], 0) AS DECIMAL(6,3)) AS sample_pct,
sp.steps,
sp.modification_counter,
CAST(100.0 * sp.modification_counter / NULLIF(sp.[rows], 0) AS DECIMAL(10,2)) AS pct_modified
FROM sys.stats AS st
JOIN sys.objects AS o ON o.object_id = st.object_id
JOIN sys.schemas AS sch ON sch.schema_id = o.schema_id
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) AS sp
WHERE o.is_ms_shipped = 0
AND sp.[rows] > 10000
ORDER BY sp.modification_counter DESC;
Raw modification counts rank the wrong things. Ten thousand changes against a 400 million row fact table are noise; ten thousand against a 60,000 row dimension table have rewritten the histogram. Rank against the threshold the engine itself uses.
WITH stats_state AS (
SELECT
sch.name AS schema_name,
o.name AS object_name,
st.name AS stats_name,
sp.last_updated,
sp.[rows],
sp.rows_sampled,
sp.modification_counter,
CASE WHEN sp.[rows] <= 500 THEN 500.0
ELSE 500.0 + (0.20 * sp.[rows])
END AS legacy_threshold,
CASE WHEN sp.[rows] <= 500 THEN 500.0
WHEN SQRT(1000.0 * sp.[rows]) < 500.0 + (0.20 * sp.[rows])
THEN SQRT(1000.0 * sp.[rows])
ELSE 500.0 + (0.20 * sp.[rows])
END AS dynamic_threshold
FROM sys.stats AS st
JOIN sys.objects AS o ON o.object_id = st.object_id
JOIN sys.schemas AS sch ON sch.schema_id = o.schema_id
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) AS sp
WHERE o.is_ms_shipped = 0
AND sp.[rows] > 500
)
SELECT
schema_name, object_name, stats_name, last_updated,
[rows], rows_sampled, modification_counter,
CAST(legacy_threshold AS BIGINT) AS legacy_threshold,
CAST(dynamic_threshold AS BIGINT) AS dynamic_threshold,
CAST(100.0 * modification_counter / dynamic_threshold AS DECIMAL(10,1)) AS pct_of_dynamic,
CAST(100.0 * rows_sampled / NULLIF([rows], 0) AS DECIMAL(6,3)) AS sample_pct
FROM stats_state
ORDER BY pct_of_dynamic DESC;
Anything sitting between 60 and 95 percent of its threshold is worth a look. A statistics object that close to the line has not tripped auto-update yet, and whether it matters depends on whether the modifications skewed the distribution or repeated it.
Two things this query will not show you. It ranks candidates, not damage, so pair it with catching the plan that changed when the histogram went stale before you act on any row. And it lists only objects that have statistics at all, which leaves out the object that gets no histogram at all: a table variable carries no statistics, so no amount of maintenance will improve its estimates.
The threshold, old and new
Through SQL Server 2014, a permanent table over 500 rows needed 500 + (0.20 * n) modifications before the optimizer invalidated its statistics at compile time. Tables at or below 500 rows needed 500. That 20 percent rule was written for tables measured in hundreds of thousands of rows.
Starting with SQL Server 2016 and database compatibility level 130, the engine uses a decreasing dynamic threshold: MIN(500 + (0.20 * n), SQRT(1000 * n)). Run the arithmetic and the difference stops being abstract.
| Rows | 20% rule | SQRT(1000 * n) | Threshold in effect |
|---|---|---|---|
| 10,000 | 2,500 | 3,162 | 2,500 |
| 100,000 | 20,500 | 10,000 | 10,000 |
| 2,000,000 | 400,500 | 44,721 | 44,721 |
| 100,000,000 | 20,000,500 | 316,228 | 316,228 |
The two formulas cross just under 19,700 rows, at 19,682. Below that, the 20 percent rule is already the smaller number and nothing changes. Above it, the square root term takes over and the ratio between the two widens without limit, though the square root threshold itself keeps climbing. On that 100 million row table, compatibility level 130 asks for 316,228 modifications where compatibility level 120 asks for 20 million. A DBA who upgraded the instance to 2019 and left the database at compatibility level 110 is running 2019 with the threshold SQL Server has used since 2005.
For instances on SQL Server 2008 R2 through 2014, or on 2016 and later with a database at compatibility level 120 or lower, trace flag 2371 turns on the same decreasing threshold. It is global scope only. Under compatibility level 130 or above, the Database Engine controls this and trace flag 2371 has no effect.
Check where you stand:
SELECT
name,
compatibility_level,
is_auto_create_stats_on,
is_auto_create_stats_incremental_on, -- SQL Server 2014+
is_auto_update_stats_on,
is_auto_update_stats_async_on
FROM sys.databases
WHERE database_id > 4;
Sample rate is the number people skip
The threshold governs when statistics refresh. The sample rate governs whether the refreshed histogram means anything.
UPDATE STATISTICS without FULLSCAN or SAMPLE lets the optimizer pick a sample size, and that size does not scale with the table. On small tables it reads everything. On a few hundred million rows it reads a sliver. Representative output from a production order system, the shape you should expect rather than a measurement to cite:
object_name stats_name rows rows_sampled sample_pct steps
----------- ------------------------ ----------- ------------ ---------- -----
OrderLine IX_OrderLine_OrderID 412,900,000 3,240,000 0.785 187
Under one percent of the table produced a 187-step histogram describing 412 million rows. If the sampled pages happen to over-represent one customer, that customer's row count is now the model for every customer. The result is an estimate wrong by an order of magnitude in either direction, which becomes a nested loop that should have been a hash join, or a memory grant that spills to tempdb, or a grant so large the query waits on RESOURCE_SEMAPHORE behind its own greed.
Three ways to control it:
-- Read every row. Expensive, accurate.
UPDATE STATISTICS dbo.OrderLine IX_OrderLine_OrderID WITH FULLSCAN;
-- Read a fixed proportion.
UPDATE STATISTICS dbo.OrderLine IX_OrderLine_OrderID WITH SAMPLE 25 PERCENT;
-- Read 25 percent now and keep using 25 percent on every later update
-- that does not name a rate, including auto-update.
-- SQL Server 2016 SP1 CU4 and SQL Server 2017 CU1 and later.
UPDATE STATISTICS dbo.OrderLine IX_OrderLine_OrderID
WITH SAMPLE 25 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
-- Reuse whatever rate was used last time.
UPDATE STATISTICS dbo.OrderLine WITH RESAMPLE;
PERSIST_SAMPLE_PERCENT closes a hole that bites people who do not know it exists. You run a FULLSCAN on Saturday, the histogram is perfect, and on Tuesday auto-update fires and replaces it with a 0.8 percent sample. With persistence on, the rate survives. sys.stats.has_persisted_sample (SQL Server 2019 and later) tells you which statistics carry it, and sys.dm_db_stats_properties.persisted_sample_percent gives the value.
UPDATE STATISTICS also accepts MAXDOP starting in SQL Server 2016 SP2 and SQL Server 2017 CU3, which matters when a FULLSCAN on a large table would otherwise take every scheduler you have.
The ascending key problem
A histogram has an upper bound. Every row inserted above that bound lives in a region the histogram does not describe.
Take a 240 million row Orders table with a clustered index on OrderDate. Statistics updated at 11 PM Saturday, so the top histogram step is Saturday's last order. By Monday afternoon 180,000 new rows have landed above it. The dynamic threshold for 240 million rows is SQRT(1000 * 240,000,000), which is 489,898. Auto-update will not fire for another two days.
Now a query runs WHERE OrderDate >= '2026-09-15'. Under the legacy cardinality estimator, a predicate above the histogram's highest value produces an estimate of 1 row. Microsoft's SAP engineering team put it this way in a post still hosted in Microsoft's blog archive: the old CE "is usually assuming then that there is no value existing. As a result we will calculate with one potential row that could return." The optimizer builds a nested loop with a key lookup, sized for 1 row. It gets 180,000, and does 180,000 key lookups.
Two mitigations predate 2014. Trace flag 2389 amends the histogram at compile time when the leading statistics column is branded ascending. Trace flag 2390 does the same when the column is branded ascending or unknown. Both flags accept global, session, or query scope through QUERYTRACEON, and Microsoft's documentation says to test before production. Enable 2390 alongside 2389 and never on its own: Ian Jose, the Microsoft engineer who wrote up the mechanism, warns "never use 2390 alone since this would mean that this logic would be disabled as soon as the ascending nature of the column was known."
Where the brand comes from matters, because that same post is the only Microsoft statement of it and it lives in the blog archive rather than on any current Learn page: "when the statistics are seen to increase three times the column is branded ascending." Confirm it on your own instance before you build a policy on it.
The brand has a failure mode, and KB2952101 documents that one: when fewer than 90 percent of inserted rows are above the highest RANGE_HI_KEY, the column is branded stationary rather than ascending, and 2389 and 2390 stop working. Trace flag 4139 amends the histogram regardless of the brand. From SQL Server 2016 SP1 you can do this per query with OPTION (USE HINT ('ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS')) instead of turning on a flag instance-wide.
Version scope is the part people get wrong. Trace flags 2389 and 2390 do not apply to cardinality estimator version 120 or above. Trace flag 4139 does not apply to CE version 70. Use one pair or the other, matched to the CE your database is running.
The cardinality estimator introduced in SQL Server 2014 handles the out-of-range case itself, by assuming that a column with ascending data may hold values above the recorded maximum. That same SAP engineering post ran the test: on a million-row table with 100 distinct values in the column, a value added above the statistics range with around 10,000 rows behind it drew an estimate of 1 row from the old CE and an estimate of 1,000 from the new one. Both figures are estimates. The actual was ten times the better of them.
Erin Stellato tested the ascending key scenario on compatibility level 130 and got an estimate of 4,922 against the 70,000-plus rows she had inserted above the histogram, with trace flag 2389 barely moving the number: 4,922 with the flag on, 4,930 with it off. Less wrong is still wrong, and the real answer on a hot ascending key is to update the statistics on a schedule the data volume justifies.
Any surrogate key generated in order produces this shape, which makes identity keys the classic ascending-key case alongside datetime columns. A busy IDENTITY clustered index moves its own histogram boundary every second of the working day.
Look at the boundary yourself. sys.dm_db_stats_histogram arrived in SQL Server 2016 SP1 CU2:
SELECT TOP (5) step_number, range_high_key, range_rows, equal_rows, average_range_rows
FROM sys.dm_db_stats_histogram(OBJECT_ID('dbo.Orders'), 1)
ORDER BY step_number DESC;
-- Or on any version:
DBCC SHOW_STATISTICS ('dbo.Orders', 'PK_Orders') WITH STAT_HEADER, HISTOGRAM;
Compare range_high_key on the last step against MAX(OrderDate). The distance between them is the blind spot.
Which cardinality estimator you are on
Compatibility level 120 and above use the CE introduced in SQL Server 2014. Compatibility level 110 and below use CE 70, the model that dates to SQL Server 7.0. You can decouple the two: LEGACY_CARDINALITY_ESTIMATION (SQL Server 2016 and later) forces the old model while leaving the compatibility level modern, so you keep the newer optimizer features and the newer statistics threshold.
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name IN ('LEGACY_CARDINALITY_ESTIMATION', 'CE_FEEDBACK',
'ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY');
Per query, OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')) from SQL Server 2016 SP1. Trace flag 9481 does the same at global, session, or query scope. Reach for the database scoped configuration over the trace flag, because it is visible in a catalog view instead of a startup parameter nobody remembers setting.
SQL Server 2022 added cardinality estimation feedback at compatibility level 160, on by default, requiring Query Store in READ_WRITE. It watches repeating queries, tests alternate CE model assumptions (correlation, join containment, row goal) against actual row counts, and persists what works as a Query Store hint. Check the edition before you plan around it: CE feedback is Enterprise only in SQL Server 2022, sitting in the same row of the editions matrix as automatic tuning and its FORCE_LAST_GOOD_PLAN. Standard gets neither. Azure SQL Database gets both. CE feedback also stands down when a plan is forced or a hard-coded hint is present.
SELECT qsq.query_id, qsqt.query_sql_text, qspf.feature_desc,
qspf.state_desc, qspf.feedback_data
FROM sys.query_store_query AS qsq
JOIN sys.query_store_query_text AS qsqt ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan AS qsp ON qsp.query_id = qsq.query_id
JOIN sys.query_store_plan_feedback AS qspf ON qspf.plan_id = qsp.plan_id;
Nothing in CE feedback repairs a histogram. It compensates for model assumptions, not for a sample that read 0.8 percent of the table.
Async updates, and when they backfire
AUTO_UPDATE_STATISTICS_ASYNC is OFF by default. With it off, a query that trips the threshold waits for the statistics update before it compiles. With it on, the query compiles against the stale histogram and the update runs in the background.
ALTER DATABASE Sales SET AUTO_UPDATE_STATISTICS_ASYNC ON;
Setting it ON has no effect unless AUTO_UPDATE_STATISTICS is ON.
It helps when the same query shape runs thousands of times an hour and clients time out waiting on a synchronous update against a large table. It hurts when the query whose statistics just went stale is the one query that needed them, because it compiles wrong and caches that wrong plan for everything behind it. High compilation rates plus frequent statistics updates also raise lock blocking on the statistics update itself. SQL Server 2022 added ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY, which puts the background request's Sch-M lock on a low priority queue so other compilations proceed.
Async is a latency trade, not a correctness improvement. Turn it on because you measured compile-time stalls, not because it sounds faster.
Filtered statistics
When most of a table sits outside the slice you query, the global histogram describes the bulk and misestimates the slice. A Product table where most rows fall outside the handful of subcategories your reports touch buries the subset you care about.
CREATE STATISTICS BikeWeights
ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1, 2, 3);
That is Microsoft's own AdventureWorks example, and bikes are the minority of the table. The optimizer uses filtered statistics when the query predicate falls inside the statistics filter. They earn their keep on soft-delete columns, multi-tenant tables with one skewed tenant, and status columns where the rare value is the one you query. Cost: each one is a separate object needing its own maintenance, and incremental statistics are not supported on filtered indexes, so a filtered object is not a candidate for incremental either. Create them for named, measured problems.
"The rebuild fixed it" is a statistics story
This is the part that sends people down the wrong path for years, so it is worth stating precisely.
When a rowstore index is created or rebuilt, Microsoft documents that statistics on the index are created or updated by scanning all rows in the index, which is equivalent to using the FULLSCAN clause. Statistics are not updated when an index is reorganized. Two operations sold as siblings, and one of them is a FULLSCAN statistics update wearing a costume.
So a rebuild that makes a slow query fast leaves you with two candidate explanations, and the fragmentation one is the weaker. You handed that index a perfect histogram built from every row. Microsoft says this outright on its own index maintenance page: customers often observe performance improvements after rebuilding indexes, in many cases unrelated to reducing fragmentation or increasing page density, and they incorrectly attribute the improvement to the rebuild itself.
Separating the two takes one test. Run the statistics update on its own, with no rebuild, and re-run the query.
-- Before state for the index you are about to touch.
SELECT sp.last_updated, sp.[rows], sp.rows_sampled, sp.modification_counter
FROM sys.stats AS st
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) AS sp
WHERE st.object_id = OBJECT_ID('dbo.OrderLine')
AND st.name = N'IX_OrderLine_OrderID';
-- The cheap half of a rebuild, without the rebuild.
UPDATE STATISTICS dbo.OrderLine IX_OrderLine_OrderID WITH FULLSCAN;
-- Re-run the slow query with SET STATISTICS IO, TIME ON and compare
-- the actual plan against the one you captured before.
You do not have to clear the plan cache to see the difference. Microsoft documents that updating statistics through any process can cause query plans to recompile on their own, so the next execution of the affected query compiles against the new histogram.
If the query recovers, fragmentation was never the problem and the rebuild was the most expensive way you could have refreshed a histogram. If it does not recover, you have ruled out the statistics and can go look at the rebuild you did not need to run on its own merits, including what page density and read-ahead are doing to that index.
Three caveats that decide whether the free FULLSCAN shows up at all:
- The statistics update skips the full scan when a partitioned index is created or rebuilt, and when the rebuild operation is resumable. On a partitioned fact table, the rebuild you ran for its statistics side effect did not give you one.
- The rebuild updates statistics on the key columns of that index. Auto-created single-column statistics, the
_WA_Sys_objects the optimizer built for yourWHEREclause predicates, are separate objects and stay stale. REORGANIZEgives you nothing here. A maintenance plan that reorganizes below 30 percent and rebuilds above it is running a statistics update on an arbitrary subset of your indexes, chosen by a number that has nothing to do with histograms.
The cost side is one-sided. A rebuild on a large index writes the whole index to the transaction log under the full recovery model, and holds a Sch-M lock for the duration unless you are on Enterprise and specify ONLINE = ON. UPDATE STATISTICS ... WITH FULLSCAN reads the index and writes a histogram. Same statistics outcome, a fraction of the log and the lock.
Stop running index rebuilds as a statistics strategy. When someone on your team says the rebuild fixed it, the useful next question is which of the two things the rebuild did was the one that helped, and the test above answers it in about a minute.
A maintenance approach that is not "everything nightly with FULLSCAN"
- Leave
AUTO_CREATE_STATISTICSandAUTO_UPDATE_STATISTICSON. Turning auto-update off to stop mid-day stalls trades a small predictable cost for an unbounded unpredictable one. Use async first. - Raise compatibility level to 130 or above so the dynamic threshold applies. On anything older, or on a database pinned below 130 for application reasons, enable trace flag 2371.
- Do not run
sp_updatestatsas a blanket tool. It updates any statistics object with at least one modified row, and without@resampleit uses default sampling, which overwrites yourFULLSCANhistograms with thin ones. - Drive a targeted job off
sys.dm_db_stats_properties. Update statistics whosemodification_counteris past some fraction ofSQRT(1000 * rows), biggest offenders first, inside your maintenance window. Stop when the window closes. Ola Hallengren'sIndexOptimizedoes this with@UpdateStatistics,@StatisticsModificationLevel(which fires on a percentage you set, or on the sameSQRT(rows * 1000)dynamic threshold), and@TimeLimitto stop when the window closes.@OnlyModifiedStatistics = 'Y'is a different control: it skips statistics with zero modifications, which is a floor rather than a threshold. - Set a sample rate for the tables that earned one. Find statistics where
sample_pctis under a few percent and the table feeds expensive plans, then set an explicit rate withPERSIST_SAMPLE_PERCENT = ONon SQL Server 2016 SP1 CU4 or later so auto-update cannot undo it. - Treat ascending keys as a separate problem. Any table with a datetime or identity leading key taking large daily inserts needs a mid-day statistics update on that one statistics object, sized to the insert rate rather than to a nightly window. Trace flag 4139 or the
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYShint is a supplement, matched to your CE version. - Keep the DMV query in a job that writes to a table. A weekly history of
last_updated,rows,rows_sampled, andmodification_counterturns the next "it was fast on Friday" ticket into a lookup.
None of this is a first move. Statistics are one branch of a bad-estimate diagnosis, and you reach them after the measurement says estimates are the problem, so read this as one stop in a full tuning pass rather than a standing job you bolt on.
Nightly FULLSCAN across the whole database is not the safe conservative option. On a multi-terabyte database it is a job that starts at 10 PM, runs past the morning, and gets cancelled every night by an operator who never checks which tables it reached before it died. The tables at the end of the alphabet have not had a statistics update in fourteen months, and no alert fires because the job is technically running.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.