Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

When a query that was fine yesterday is slow today, the cause is nearly always one of two things: the statistics no longer describe the data, or a cached plan built for one parameter value is being reused for another. They need different fixes, so find out which it is first.

How SQL Server uses statistics

Every index has a statistics object, and SQL Server creates column statistics automatically (names starting _WA_Sys_) when a column is used in a filter or join. Each one holds:

• a histogram of up to 200 steps for the first column, showing how many rows have each range of values;
• the density (1 divided by the number of distinct values) for each left-based combination of columns;
• when it was last updated and how many rows were sampled.

The optimiser uses these to estimate how many rows each step of the plan will produce (selectivity), and the estimates decide the join types, the memory grant and whether to seek or scan.

-- All statistics on a table, with age and changes since the last update
SELECT s.name AS stats_name, s.auto_created, s.has_filter,
       sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY sp.modification_counter DESC;

-- The histogram for one of them
DBCC SHOW_STATISTICS (N'dbo.Orders', N'IX_Orders_CustomerID') WITH HISTOGRAM;

When statistics are updated automatically

With AUTO_UPDATE_STATISTICS on (the default), statistics are marked out of date after a number of changes, and updated the next time a query needs them. From compatibility level 130 (SQL Server 2016) the threshold for large tables is about the square root of 1,000 times the row count, so a 100-million-row table updates after about 316,000 changes rather than 20 million. Automatic updates use a sample, which can miss skew in large tables.

Is it statistics? The checks

\1. In the actual plan, find the first operator (reading right to left) where estimated and actual rows differ by 10 times or more.

\2. Check that table’s statistics: last_updated and modification_counter from the query above. Many changes since the last update, or new data beyond the last histogram step (the classic case: today’s date on an ascending key), points to statistics.

\3. Update the statistics on just that table and run the query again:

UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
-- or one statistics object: UPDATE STATISTICS dbo.Orders IX_Orders_CustomerID WITH FULLSCAN;

If the estimates are now right and the query is fast, it was statistics. Note that updating statistics also removes the cached plans that use the table, so a query that improves after an update might have been a caching problem instead. Run step 4 to tell them apart.

Is it caching? The checks

\4. Run the query with OPTION (RECOMPILE), or call the procedure WITH RECOMPILE, using the slow parameter value. If that is fast and the normal call is slow, the cached plan is the problem: it was compiled for a different value (parameter sniffing).

\5. Look at the compiled values in the cached plan (root operator properties, Parameter List: Parameter Compiled Value against Parameter Runtime Value), or in Query Store, which also shows whether the query has several plans.

-- Queries in Query Store with more than one plan
SELECT q.query_id, COUNT(DISTINCT p.plan_id) AS plans, LEFT(MAX(qt.query_sql_text), 150) AS query_text
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
GROUP BY q.query_id
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY plans DESC;

Fixes for a statistics problem

• Update statistics more often on tables with fast-changing or ascending keys, with FULLSCAN or a larger sample, from a SQL Agent job (Ola Hallengren’s IndexOptimize with @UpdateStatistics = ‘ALL’ and @OnlyModifiedStatistics = ‘Y’ does this).
• Create filtered statistics for skewed subsets, or multi-column statistics where columns are correlated:

CREATE STATISTICS st_Orders_Customer_Date ON dbo.Orders (CustomerID, OrderDate);
CREATE STATISTICS st_Orders_Large ON dbo.Orders (CustomerID) WHERE Amount >= 500;

• Keep AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS on. Consider AUTO_UPDATE_STATISTICS_ASYNC on busy OLTP systems, so queries do not wait for an update.

Fixes for a caching problem

• OPTION (RECOMPILE) on the one statement that needs a plan for each value. It costs a compilation per run, so keep it for statements that do not run thousands of times a minute.
• OPTION (OPTIMIZE FOR (@c = 42)) for a value that gives a plan good enough for everyone, or OPTIMIZE FOR UNKNOWN for the average.
• Force the good plan in Query Store while you fix the cause properly.
• On SQL Server 2022 at compatibility level 160, Parameter Sensitive Plan optimisation can keep several plans for one query automatically.
• Split a procedure that handles very different cases (for example one customer or all customers) into separate procedures.

From my SQL Server DBA handbook.