4. Statistics, cardinality estimation and the query optimizer

The optimizer is cost-based: it estimates how many rows each step will produce (cardinality) and picks the plan with the lowest estimated cost (CPU + I/O). Estimates come from statistics:

• A histogram of up to 200 steps on the first column of the statistics object (values and row counts per range).
• Density vectors for column combinations (average uniqueness).
• Created automatically for indexes and for columns used in predicates (AUTO_CREATE_STATISTICS), updated automatically after enough changes (AUTO_UPDATE_STATISTICS; dynamic threshold ≈ √(1000 × rows) since SQL Server 2016 / compat level 130).

DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_Customer_Date') WITH HISTOGRAM;
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;    -- after big data loads
SELECT * FROM sys.dm_db_stats_properties(OBJECT_ID('dbo.Orders'), 2);   -- last updated, rows sampled, modification counter

Optimisation phases: trivial plan (simple queries — no costing needed) → simplification (remove redundant joins, push predicates) → cost-based search in stages (transaction processing → quick plan → full optimisation), stopping when a "good enough" plan is found or a time budget ends.

Things that break estimates: stale statistics, functions on columns (WHERE YEAR(OrderDate) = 2026), implicit conversions (NVARCHAR parameter vs VARCHAR column), table variables (estimated 1 row before SQL Server 2019's deferred compilation), multi-statement table-valued functions, local variables (optimizer can't see the value), correlated columns, complex OR/LIKE predicates.

SARGable predicates (Search ARGument-able) let the optimizer seek:

-- ❌ Non-SARGable: function on the column → scan
WHERE YEAR(OrderDate) = 2026
WHERE LEFT(Email, 3) = 'abc'
WHERE CustomerCode = 12345            -- CustomerCode is VARCHAR → implicit conversion of the column
-- ✅ SARGable rewrites → seek
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'
WHERE Email LIKE 'abc%'
WHERE CustomerCode = '12345'