In one line: WHERE City = 'Paris' AND Country = 'France' matches every Paris row, because Paris is in France. The optimizer doesn’t know that, so it treats the columns as unrelated or only partly related, and underestimates.
Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/correlated-predicates on GitHub.
What you’ll see
- Estimates far below the actual rows for queries that filter on hierarchical columns: city and country, product and category, model and make.
- Worse estimates with every extra predicate. In the demo, the truth was 5,000 throughout:
| legacy estimator | new estimator | |
|---|---|---|
| Paris, France | 250 | 1,118 |
| Paris, France, Europe | 50 | 748 |
| Paris, Japan (truth: 0) | 250 | 1,118 |
Why it happens
- The legacy estimator assumes independence and multiplies the selectivities: 1/200 × 1/20.
- The new estimator (2014+) uses exponential backoff. It takes the most selective predicate in full, then the square root of the next, and so on. That’s some correlation, but not enough for a strict hierarchy.
- Single-column statistics can’t tell “always together” from “never together”. Paris-France and Paris-Japan got the same estimate.
What to do
- Multi-column statistics (or an index) on
(City, Country, Continent). Both estimators use the density, so Paris-France becomes exact. Only leading prefixes get a density, so column order matters. - Filtered statistics such as
City WHERE Country = 'France'for the specific values that matter. They’re exact for that value and know nothing else. OPTION (USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'))for a query you know filters on correlated columns. The new estimator then uses the most selective predicate alone, which gave 5,000 for two predicates or three.
What doesn’t help
- Expecting multi-column statistics to be precise. A density is an average: it knows how many combinations exist, not which ones. Paris-Japan went from 1,118 to 5,000 when the truth is zero.
- Using the hint broadly. It’s wrong in the other direction for columns that really are independent.
- A filtered statistic covering three predicates. It fixes the two columns it covers and treats the third as independent again: 1,000 instead of 5,000.
Check your server
-- Multi-column and filtered statistics in this database: the fixes already in place.
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
OBJECT_NAME(s.object_id) AS table_name,
s.name AS stats_name,
Columns = STUFF((SELECT ', ' + c.name
FROM sys.stats_columns AS sc
JOIN sys.columns AS c ON c.object_id = sc.object_id AND c.column_id = sc.column_id
WHERE sc.object_id = s.object_id AND sc.stats_id = s.stats_id
ORDER BY sc.stats_column_id
FOR XML PATH('')), 1, 2, ''),
s.has_filter,
s.filter_definition
FROM sys.stats AS s
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
AND (s.has_filter = 1
OR (SELECT COUNT(*) FROM sys.stats_columns AS sc
WHERE sc.object_id = s.object_id AND sc.stats_id = s.stats_id) > 1);See it happen
The correlated-predicates demo measures both estimators against the truth for two and three correlated predicates and for a contradiction. It then applies each fix and shows its blind spot. Every estimate matched the arithmetic exactly.
Related: statistics sampling and skew · the ascending-key problem · MSTVFs and table variables
Measured on SQL Server 2022 CU25 (16.0.4255.1). Your numbers will differ; the behaviour shouldn’t.
Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.
