SQL Server › Query Optimization & Plans

How do outdated or missing statistics affect the query optimizer's ability to choose an efficient execution plan?

Intermediate

Answer

SQL Server relies on statistics about the distribution of data within your tables to estimate how many rows a query will return and choose the most efficient plan accordingly, and if those statistics become outdated after significant data changes, the optimizer can make poor estimates, leading to inefficient plans, such as choosing the wrong join type or failing to use a helpful index.

UPDATE STATISTICS Orders;

-- Or check when statistics were last updated
SELECT name, STATS_DATE(object_id, stats_id) AS LastUpdated
FROM sys.stats WHERE object_id = OBJECT_ID('Orders');

Real-world example A team notices query performance suddenly degrading after a large data import, and updating the table's statistics immediately resolves the issue by giving the optimizer accurate information to choose a better execution plan.

Common follow-ups: How often does SQL Server automatically update statistics on its own?;What is the performance cost of manually updating statistics on a very large table?

Indexes;Query Store