Statistics

34.1 What Are Statistics?

Distribution metadata the optimizer uses to estimate cardinality (row counts).

Components: header, density vector, and histogram (up to 200 steps on the first key column).

DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_CustomerID');
SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('dbo.Orders');
SELECT * FROM sys.dm_db_stats_properties(OBJECT_ID('dbo.Orders'), 1);
SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('dbo.Orders'), 1);

34.2 Creating and Updating Statistics

Auto-create and auto-update are enabled by default. The dynamic threshold since 2016 is roughly SQRT(1000 * rows).

AUTO_UPDATE_STATISTICS_ASYNC avoids compile waits.

Filtered statistics, multi-column statistics, incremental statistics (partitioned tables).

CREATE STATISTICS st_Orders_ShipCity ON dbo.Orders(ShipCity) WITH FULLSCAN;
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
UPDATE STATISTICS dbo.Orders IX_Orders_CustomerID WITH SAMPLE 30 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
EXEC sp_updatestats;

34.3 Cardinality Estimator (CE)

Legacy CE (70) vs new CE (120+), controlled by compatibility level.

Hints: OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')).

Common estimation problems: stale statistics, correlated columns, table variables, MSTVFs, local variables, ascending keys, functions on columns.