How SQL Server Chooses Query Plans, Why Statistics Betray You, and What To Do About It

SQL Server generates candidate query plans and picks the cheapest one based on cost estimates derived from statistics objects, not actual row counts. When statistics go stale, sample poorly, or miss data skew, the optimizer confidently produces plans that collapse against real data volumes. Large tables suffer most from delayed threshold triggers.

1. The optimizer is making a bet, not a calculation

Every time SQL Server compiles a query it runs an auction. The optimizer generates a handful of candidate plans, estimates the cost of each one in abstract units that roughly map to disk and CPU work, and picks the cheapest candidate it found within its search budget. This is cost based optimization, and the word to notice is estimates. The optimizer never actually counts the rows in your tables at compile time. It relies entirely on statistics objects that describe the shape of your data, and it uses those statistics to guess how many rows will survive each filter, each join, and each aggregation before it ever touches the table.

This distinction matters because the whole architecture rests on an assumption that the statistics are a faithful summary of the data. When that assumption holds, the optimizer usually makes good choices. When it does not hold, the optimizer is still confident, it is just confidently wrong, and it will produce a plan that looks perfectly reasonable on paper while performing terribly against the actual data.

2. What a statistics object actually contains

A statistics object in SQL Server is built around a histogram, typically capped at 200 steps, that describes the distribution of values in a column or the leading column of an index key. Alongside the histogram sits a density vector that captures the average number of duplicate rows for combinations of columns, which the optimizer uses when a predicate touches more than one column at once.

SQL Server builds these objects automatically for indexed columns, and if the database option is turned on, for any column the optimizer decides would benefit from one based on a query’s filter or join predicate. That automatic creation is controlled by AUTO_CREATE_STATISTICS, and in the overwhelming majority of installations it should stay on. The histogram is not built from every row unless you ask for a full scan. By default SQL Server samples a percentage of the table, and the sample rate shrinks as the table grows, which is exactly the situation where a small sample is least likely to capture skew hiding in the tail of the distribution.

3. Auto update statistics and the threshold problem

AUTO_UPDATE_STATISTICS is the setting most people know about, and it is also the setting most misunderstood. The classic rule, still true on most databases running compatibility level below 130, is that a statistics object becomes stale enough to trigger a refresh once roughly 20 percent of the rows in the table have changed, plus a fixed base amount. For a small table this threshold is reached quickly and the statistics stay fresh. For a large table, a 20 percent change on ten million rows means two million row modifications have to accumulate before SQL Server even considers the statistics out of date. In practical terms this means a heavily transacted large table can run for hours or days on statistics that no longer describe reality, and the optimizer has no idea, because from its point of view nothing has crossed the threshold yet.

Microsoft introduced a linear, more aggressive update algorithm under trace flag 2371, and it became the default behavior once a database is running at compatibility level 130 or higher. This lowers the threshold as the table grows rather than keeping it fixed at 20 percent, so large tables get refreshed statistics more often. If your database is still running an older compatibility level for legacy reasons, this alone is worth revisiting, because you may be inheriting a staleness problem that newer databases in the same instance do not have.

There is a second wrinkle that catches people out even when the threshold logic is working exactly as designed. AUTO_UPDATE_STATISTICS is synchronous by default. The first query that trips the threshold pays the price of the statistics rebuild before its own compilation can finish, and on a large table that rebuild is not instant. If that unlucky query happens to land during a peak traffic window, you get a visible latency spike that has nothing to do with the query itself and everything to do with timing. AUTO_UPDATE_STATISTICS_ASYNC removes that synchronous penalty by letting the triggering query run against the old statistics while a background thread refreshes them for everyone after it, which trades a small window of staleness for the removal of an unpredictable stall.

4. Where this goes wrong in practice

The failure modes worth understanding are not exotic. They show up constantly in production and they mostly reduce to a handful of patterns.

The first is the ascending key problem. If a table has a clustered index on an identity column or a date column and rows are constantly appended at the top end of the range, the statistics histogram has a permanent blind spot for the newest values. Every row inserted since the last statistics refresh is invisible to the optimizer’s row estimate. A query filtering on the last hour of data can be estimated to return a handful of rows when it actually returns hundreds of thousands, and the optimizer will choose a nested loop or a plan built around a small estimate that collapses under the real volume. This is one of the more insidious problems because it gets worse the longer statistics go unrefreshed, and busy append heavy tables are exactly the tables where the default threshold takes longest to trip.

The second is skew that survives sampling. A column where 90 percent of the rows share one value and the rest are scattered will often sample reasonably well, but any column with a long uneven tail can end up with a histogram that smooths over exactly the outlier values your query is filtering for. The optimizer sees an average that is nowhere close to the true selectivity of the specific value in play.

The third is bulk load and batch operations happening outside the normal transaction pattern the auto update thresholds were tuned around. An ETL job that truncates and reloads a large table, or a bulk insert that lands a huge volume of rows in one operation, can leave statistics dramatically wrong the moment the load finishes, and if the job does not explicitly update statistics afterward, the table sits in that state until the threshold eventually trips on top of ordinary application traffic.

The fourth is parameter sniffing interacting badly with a statistics refresh. A stored procedure compiles a plan optimized for the parameter value supplied on the first call after a recompile. If statistics were just refreshed and the first caller happens to pass in an unusually rare or unusually common value, the resulting plan gets cached and reused for every subsequent caller regardless of how different their parameter value is. This is not strictly a statistics problem, it is a caching problem, but stale or freshly rebuilt statistics are frequently the trigger that causes the recompile in the first place, so the two issues show up together constantly in real incident reports.

5. What to actually do about it

Start by checking compatibility level. If you are below 130, the linear staleness threshold under trace flag 2371 is worth adopting, and modern SQL Server makes this the default the moment you raise compatibility level rather than requiring the trace flag directly. This alone reduces how long large tables can run on genuinely wrong statistics.

Turn on AUTO_UPDATE_STATISTICS_ASYNC on any database with tables large enough that a synchronous statistics rebuild is noticeable. This does not fix stale statistics, it fixes the unpredictability of when the cost of fixing them gets paid, which is usually the more urgent operational problem.

For tables with the ascending key pattern, schedule an explicit statistics update on a cadence tighter than the default threshold would ever trigger on its own. A nightly or even hourly UPDATE STATISTICS on the specific column, run with a sample rate high enough to catch the tail, closes the blind spot that automatic triggers structurally cannot close in time. On SQL Server 2016 and later you also have trace flag 4139, which improves how the ascending key case is handled by the cardinality estimator directly, and it is worth testing against your workload.

For skewed columns that matter to performance, filtered statistics targeting the specific value ranges you query most often will give the optimizer a much sharper picture than a single histogram trying to describe the whole column. This is cheap to set up and often produces a bigger improvement than a full statistics rebuild on the whole table.

Any bulk load process should update statistics as the last step of the job, explicitly, rather than trusting the automatic threshold to notice. This is one line in a maintenance script and it removes an entire category of post load performance surprises.

For parameter sniffing interacting with fresh statistics, the fix depends on whether the underlying data genuinely has different optimal plans for different parameter values. If it does, OPTION(RECOMPILE) on the specific statement, or the OPTIMIZE FOR UNKNOWN hint, or breaking the offending query into its own procedure, are all reasonable tools. If it does not, and you are just seeing plan instability from timing, Query Store is the right lens. Enable it if it is not already on, use it to see exactly when a plan changed and what triggered the regression, and use plan forcing to pin a known good plan while you investigate rather than guessing blind.

Finally, treat statistics maintenance as part of your regular index maintenance job rather than a separate afterthought. Most maintenance solutions already update statistics as part of a reorganize or rebuild, but a rebuild on one index does not refresh statistics on other indexes or on filtered subsets, so it is worth confirming your maintenance job actually covers every statistics object on your largest and most frequently modified tables, not just the ones attached to the indexes it happens to be rebuilding that night.

6. Observing plan behavior and finding expensive plans

Everything so far has been about the causes. This section is about watching for them, and it answers a question worth asking directly, whether you can systematically find queries running on an expensive or unstable plan and confirm the diagnosis by forcing a recompile to see what changes.

Query Store is the primary tool here and it is worth treating as the default lens rather than a specialist add on. Once enabled, it gives you three views that matter most day to day. Top Resource Consuming Queries ranks queries by CPU, duration, or logical reads over a chosen window, which is the fastest way to find your worst offenders without writing a single DMV query. Regressed Queries specifically looks for queries whose performance got worse between two time periods, which is the direct signal for a plan that changed for the worse. Query comparison, available from SSMS 18 onward, lets you pick two plan_id values for the same query_id and see the two execution plans side by side with the differing operators highlighted, which is the fastest way to actually see what the optimizer changed its mind about.

The underlying data behind all three views lives in sys.query_store_runtime_stats, and the columns that matter for spotting instability are the min, max, and standard deviation figures alongside the average, not just the average on its own.

SELECT
    qsq.query_id,
    qsp.plan_id,
    qsrs.avg_cpu_time,
    qsrs.min_cpu_time,
    qsrs.max_cpu_time,
    qsrs.stdev_cpu_time,
    qsrs.count_executions
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
ORDER BY qsrs.stdev_cpu_time DESC;

A query near the top of that list, where stdev_cpu_time is a large fraction of avg_cpu_time and max_cpu_time is many multiples of min_cpu_time, is telling you the same plan is behaving wildly differently depending on the parameter it was called with. That pattern is the parameter sniffing and skewed statistics problem from section 4 showing up in the data without you having to guess.

If Query Store is not enabled, the same signal exists in the plan cache through sys.dm_exec_query_stats, which tracks min, max, and last elapsed time and worker time per plan handle since the plan was compiled.

SELECT TOP 20
    qs.plan_handle,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.min_worker_time,
    qs.max_worker_time,
    qs.max_worker_time - qs.min_worker_time AS cpu_spread,
    st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.max_worker_time - qs.min_worker_time DESC;

This gives you the plan cache equivalent of the Query Store view above, ordered by the gap between the best and worst execution of the same cached plan, which is a good proxy for finding a plan that is fine for some callers and expensive for others.

To directly answer the recompile question, yes, and it is a legitimate and commonly used diagnostic technique rather than a workaround. The sequence is to identify a candidate query using either of the queries above, pull its current estimated cost out of the cached plan, and then run the same statement text with OPTION(RECOMPILE) to force a fresh compilation and see what the optimizer produces when it is not constrained by whatever plan is sitting in cache.

-- Pull the current cached plan and its estimated cost
SELECT qs.plan_handle, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.plan_handle = 0x...;

-- Force a fresh compile of the same statement and observe cost, duration and CPU
SET STATISTICS TIME, IO ON;
SELECT * FROM dbo.Orders WHERE CustomerId = @CustomerId OPTION (RECOMPILE);
SET STATISTICS TIME, IO OFF;

Compare the StatementSubTreeCost value in the query_plan XML from the cached plan against the plan the recompiled execution produces, and compare the actual CPU and elapsed time reported by STATISTICS TIME against what sys.dm_exec_query_stats shows for the cached plan’s average. A materially cheaper estimated cost and a materially faster actual run under recompile confirms the cached plan is the problem rather than the query or the schema. Do this against a copy of production or during a quiet window if the query is expensive enough that a bad recompiled plan of its own could add load, since OPTION(RECOMPILE) still has to pay for compilation and there is a small chance the fresh compile picks something worse if statistics are misleading in the other direction.

A lighter touch version of the same idea, without touching the query text at all, is to evict just that one plan from cache and let the next natural caller trigger a fresh compile.

DBCC FREEPROCCACHE(0x...); -- pass the specific plan_handle, never call this with no argument in production

This is safer than a blanket DBCC FREEPROCCACHE because it only evicts the one plan you are investigating rather than clearing the entire instance’s cache, which would cause a compilation storm across every workload sharing that server.

It is also worth knowing that SQL Server 2017 and Azure SQL Database can do a version of this comparison automatically. Automatic Plan Correction, part of Query Store, monitors for exactly the CPU and duration regression pattern described above between consecutive plans for the same query, and when it is confident a plan change caused a regression it can automatically force the last known good plan back without waiting for a human to notice. It is worth turning on as a safety net, but it should not replace the manual investigation above, because it treats the symptom by pinning a plan rather than addressing whichever underlying statistics or data change caused the regression in the first place.

7. Worked examples

The points above are easier to trust once you can see them. Here is the same material with the actual T-SQL behind it.

Looking inside a statistics object is the natural starting point. DBCC SHOW_STATISTICS returns three result sets, a header with the sample size and date, the density vector, and the histogram itself.

DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_OrderDate');

The header result set tells you how old the statistics are and what fraction of the table was sampled. A rows sampled figure far below the total row count on a large skewed column is your first clue that the histogram may be smoothing over exactly the values a query cares about. The histogram result set gives you RANGE_HI_KEY, RANGE_ROWS, and EQ_ROWS for each step, and comparing EQ_ROWS for a specific value against what you know the real count to be is the quickest sanity check for the skew problem described in section 4.

Checking compatibility level and moving to the linear staleness threshold is a two line check followed by a considered change, not something to flip blindly on a production system without testing.

SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabase';

ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 150;

Raising compatibility level changes cardinality estimation behavior more broadly than just the statistics threshold, so test this against a representative workload before pushing it to production rather than treating it as a free win.

Turning on asynchronous statistics updates removes the synchronous stall described in section 3, and it is a single database scoped option.

ALTER DATABASE YourDatabase SET AUTO_UPDATE_STATISTICS_ASYNC ON;

For the ascending key problem on an append heavy table, an explicit scheduled update with a higher sample rate closes the blind spot that the automatic threshold cannot close in time.

UPDATE STATISTICS dbo.Orders (IX_Orders_OrderDate) WITH SAMPLE 50 PERCENT;

-- Or, for a smaller table where the cost of a full scan is acceptable
UPDATE STATISTICS dbo.Orders (IX_Orders_OrderDate) WITH FULLSCAN;

A SQL Agent job running this on a schedule tighter than the default 20 percent threshold, or the linear equivalent under a newer compatibility level, is the practical fix rather than waiting for SQL Server to notice on its own.

Filtered statistics target the specific value ranges your queries actually filter on, which gives the optimizer a sharper histogram than a single object covering the whole column.

CREATE STATISTICS stat_Orders_RecentHighValue
ON dbo.Orders (CustomerId)
WHERE OrderDate >= '2026-01-01' AND OrderTotal > 10000;

Any bulk load process should end with an explicit statistics update rather than trusting the automatic threshold, since a truncate and reload or a large batch insert can leave the histogram badly wrong the moment the job finishes.

TRUNCATE TABLE staging.DailyTransactions;
BULK INSERT staging.DailyTransactions FROM '\\server\share\daily.csv' WITH (FORMAT = 'CSV');
UPDATE STATISTICS staging.DailyTransactions WITH FULLSCAN;

For parameter sniffing where the underlying data genuinely needs different plans for different callers, OPTION(RECOMPILE) or OPTIMIZE FOR UNKNOWN are the direct tools.

SELECT * FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);

SELECT * FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId UNKNOWN));

RECOMPILE pays a compilation cost on every execution in exchange for a plan tailored to the actual parameter each time, which is worth it for a query executed occasionally but not for one executed thousands of times a second. OPTIMIZE FOR UNKNOWN asks the optimizer to use the average distribution from the histogram rather than sniffing the first parameter value, which trades peak case performance for consistency across callers.

When the real issue is plan instability from timing rather than genuinely different optimal plans, Query Store is where you diagnose and then hold the line. Enabling it and finding a regression looks like this.

ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

SELECT qsq.query_id, qsp.plan_id, qsrs.avg_duration, qsrs.last_execution_time
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
WHERE qsq.query_id = 12345
ORDER BY qsrs.last_execution_time DESC;

Once you have identified the plan_id that performed well before the regression, forcing it buys you stability while you investigate the underlying cause rather than leaving production exposed to whichever plan the optimizer picks next.

EXEC sp_query_store_force_plan @query_id = 12345, @plan_id = 67;

Forced plans should be treated as a temporary stabiliser, not a permanent fix. Once the statistics or indexing issue behind the regression is resolved, unforce the plan and let the optimizer compile fresh so it can adapt as the data continues to change.

8. A real world case study, the daytime recompile storm

Everything above is more convincing with a concrete failure attached to it, so here is one that plays out constantly in production and rarely gets diagnosed on the first attempt.

The symptom is a sudden CPU spike in the middle of the day with no obvious trigger. Nothing changed in the application. No deployment went out. No unusually large batch job ran. The spike lasts a few minutes, disappears on its own, and the queries running during it look nothing like the queries that ran perfectly well an hour earlier against the same tables. Whoever is on call at the time reasonably rules out the usual suspects, since the code did not change and the data volume looks normal, and the investigation stalls because the actual cause left almost no trace by the time anyone goes looking.

The real sequence is two costs landing in the same few seconds rather than one. A busy table crosses its automatic statistics threshold during ordinary daytime traffic, and because AUTO_UPDATE_STATISTICS is synchronous by default, the unlucky query that tripped the threshold pays for the histogram rebuild before it can even compile. The moment that rebuild finishes, every cached plan depending on that statistics object is invalidated, because the plan cache treats a statistics change as a correctness dependency rather than just a cost input. If the table is busy, this invalidation is discovered by many concurrent callers within the same short window, and they all recompile at once. Compilation is CPU expensive on its own, and with several joins in the mix it can be substantial multiplied across dozens of simultaneous callers. Whichever caller happens to win that race and compile first gets its parameter value sniffed into the plan that everyone else then inherits, and if that value was unusual, the plan that comes out the other side looks bizarre relative to what the table normally sees, because it was optimized for an edge case rather than for the typical caller.

The fix that usually gets reached for first, disabling automatic statistics updates entirely, does stop the spike, and it is worth being honest about what that trade actually costs. It removes the only mechanism that keeps statistics from drifting away from reality over time. Every table with the setting off is now accumulating staleness with nothing to correct it, and the ascending key problem and general skew described earlier in this piece will eventually reassert themselves quietly, without a spike to point at, which makes the resulting performance regression considerably harder to attribute later.

A more durable answer targets the specific tables that caused the spikes rather than the whole database. Disabling automatic recompute on just those tables with NORECOMPUTE, and then updating their statistics explicitly on a schedule during a quiet window, gets you the same absence of daytime spikes without giving up the correction mechanism everywhere else in the database.

UPDATE STATISTICS dbo.Orders WITH NORECOMPUTE;

-- Scheduled job, run during a low traffic window rather than left to a random daytime caller
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

Confirming this diagnosis, and catching it early the next time a table drifts toward the same threshold, comes down to three tools rather than guesswork after the fact. The Extended Events auto_stats event records every automatic statistics operation with a timestamp, which lets you line the event up directly against a CPU graph rather than relying on memory of when the spike happened. The sql_statement_recompile event, filtered to recompile_cause equal to 3 for a statistics change, catches the recompile storm itself in the act, and seeing a cluster of these fire in the same second against the same table is about as direct a confirmation as this problem offers. sys.dm_db_stats_properties lets you watch the modification counter on your known busy tables climb toward the automatic threshold ahead of time, which turns this from something you diagnose after a spike into something you can see coming and schedule around before it ever happens again.

The broader point this case makes is the same one running through the rest of this piece from a different angle. The optimizer’s confidence and the plan cache’s efficiency are both working exactly as designed here. The spike is not a bug in either mechanism, it is the predictable cost of a correctness dependent invalidation landing on a busy table during peak hours with no control over the timing. Once you take the timing back under your own control, the mechanism that caused the pain becomes the same mechanism that keeps your statistics honest without ever costing you a daytime CPU spike again.

9. The underlying lesson

None of this is really about SQL Server internals for their own sake. The optimizer is doing exactly what it was designed to do, which is make a fast, confident decision based on the best information available to it at compile time. The failures described here are not bugs, they are the predictable consequence of feeding a confident decision maker with information that has quietly gone out of date. The fix is never to distrust the optimizer. It is to be deliberate about the freshness of what you are feeding it, because a cost based optimizer working from good statistics will consistently outperform any amount of manual plan tuning, and one working from stale statistics will confidently produce the wrong answer every time.