City and state filters describe related information rather than independent choices. Multi-column statistics give SQL Server additional information about those combinations when estimating a query.
Recognize the Relationship
A city value can strongly restrict the possible state values. Separate statistics describe each column without storing every joint frequency. Combining their selectivities therefore depends on assumptions about the relationship.
I compare estimated and actual rows at the filter before redesigning a join. I also check whether useful combined statistics already exist through an index. A new statistics object is unnecessary when the same useful prefix is already represented.
The current cardinality estimator does not blindly treat every predicate as independent. Recent models use partial correlation assumptions for this kind of combination. Those assumptions still cannot reproduce every real distribution from separate column summaries.
Statistics describe the stored data rather than the application's geography lesson. SQL Server does not infer every city-to-state rule from the column names. It needs useful data summaries and constraints where the model supports them.
Use a disposable database for the demonstration. The fixture creates synthetic city and state pairs so the relationship is easy to inspect. Keep actual production statistics and table changes outside this experiment.
Build a Correlated Fixture
GENERATE_SERIES requires SQL Server 2022 or later and compatibility level 160 or higher. The generator's limits are deliberate fixture inputs. The modulo expression assigns each generated row to one defined city and state pair.
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();
CREATE TABLE dbo.CityStatsDemo
(
AddressID int NOT NULL CONSTRAINT PK_CityStatsDemo PRIMARY KEY,
StateName nvarchar(20) NOT NULL,
CityName nvarchar(20) NOT NULL
);
INSERT dbo.CityStatsDemo(AddressID, StateName, CityName)
SELECT value,
CASE WHEN value % 10 < 6 THEN N'West' ELSE N'East' END,
CASE WHEN value % 10 < 6 THEN N'River'
WHEN value % 10 < 8 THEN N'Hill'
ELSE N'Lake' END
FROM GENERATE_SERIES(1, 10000, 1);
CREATE STATISTICS StateOnly ON dbo.CityStatsDemo(StateName) WITH FULLSCAN;
CREATE STATISTICS CityOnly ON dbo.CityStatsDemo(CityName) WITH FULLSCAN;This fixture has no independent random assignment of states. The chosen city determines its state through the input expression. That relationship is the reason to inspect combined selectivity rather than two unrelated filters.
A full scan makes sampling variability less distracting in this small test. It does not make the optimizer's model perfect. Statistics summarize distributions, and the estimator still chooses how to use them.
The primary key supplies a separate useful statistic on the address identifier. It says nothing about the city and state relationship. Do not assume any existing statistic covers every column in its table.
Capture the First Estimate
Enable the actual execution plan in SSMS before executing the filter. Inspect the estimated and actual rows at the operator applying both predicates. A COUNT result alone does not expose where the estimate diverged.
SELECT AddressID, StateName, CityName
FROM dbo.CityStatsDemo
WHERE StateName = N'West'
AND CityName = N'River'
OPTION (RECOMPILE);
SELECT StateName, CityName, COUNT_BIG(*) AS RowsInPair
FROM dbo.CityStatsDemo
GROUP BY StateName, CityName
ORDER BY StateName, CityName;OPTION RECOMPILE ensures this test compiles against the current statistics and literal values. It is a diagnostic choice, not an automatic production recommendation. Repeated compilation costs processor time in a real workload.
Read the plan's cardinality estimation model version and database compatibility level together. A changed estimator changes the predicate combination assumptions. Keep those details stable during the first and second comparison.
Do not announce a particular wrong estimate before running the query. The current build, model, and optimizer features influence the answer. Record the values your own plan produces and identify the difference that needs explanation.
Add the Multi-Column Statistics Object
Create a statistics object with StateName first and CityName second. That gives a histogram for StateName and density information for the leading prefixes. It does not create a two-dimensional histogram.
CREATE STATISTICS StateCity
ON dbo.CityStatsDemo(StateName, CityName)
WITH FULLSCAN;
DBCC SHOW_STATISTICS (N'dbo.CityStatsDemo', N'StateCity')
WITH DENSITY_VECTOR;
DBCC SHOW_STATISTICS (N'dbo.CityStatsDemo', N'StateCity')
WITH HISTOGRAM;
SELECT AddressID, StateName, CityName
FROM dbo.CityStatsDemo
WHERE StateName = N'West'
AND CityName = N'River'
OPTION (RECOMPILE);The density vector reports All density for StateName and for the StateName, CityName prefix. Density represents an average based on distinct combinations. It does not record the exact frequency of each individual pair.
Multi-column statistics can improve estimates when equality predicates match useful leading prefixes. Their effect depends on the predicates and estimator using that information. Compare the resulting estimate rather than assuming creation guarantees an improvement.
An unchanged estimate is a useful finding. It means the added summary did not change this plan's estimate under the tested conditions. Investigate the estimator's assumptions and available alternatives rather than adding the same columns repeatedly.
Column Order in Multi-Column Statistics
A statistic on state, city, and district contains densities for state, state-city, and state-city-district. It does not provide a city-district prefix density. The definition's order therefore determines which combinations are summarized.
Only the first column receives the histogram. Reversing city and state changes that leading distribution summary. Choose the order according to the predicates and information missing from the current estimate.
Multi-column statistics provide no new physical access path. Creating one does not create an index seek option or change how rows are stored. That distinction makes a statistics experiment smaller than adding another maintained index.
A real table also contains skew within combinations. A few common city-state pairs and many rare pairs need more than average density. Filtered statistics or a different query design deserve consideration when a specific subset dominates the workload.
Check Freshness and Coverage
Inspect the statistic's age, sample, and modifications before relying on its densities. A current definition with stale data still describes an earlier distribution. The metadata query below keeps those conditions visible. On this fresh fixture, the primary key row shows NULL values because its index was built on an empty table.
SELECT s.name, p.last_updated, p.rows, p.rows_sampled,
p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE s.object_id = OBJECT_ID(N'dbo.CityStatsDemo');How different are the common and rare pairs in your real workload? Test both when comparing plans. A change that improves one familiar literal can still leave another important input poorly estimated.
Statistics also need appropriate inspection permissions. Use an account authorized to read the table's statistics and metadata. A missing metadata row under a restricted account is not proof that the statistic does not exist.
Retain the complete density output with its column list. Copying only one decimal value loses the prefix it describes. The same numeric density has different meaning when attached to a different set of columns.
Keep the Multi-Column Statistics Test Focused
Use multi-column statistics to test a specific missing relationship. Preserve the before and after plans, statistics definition, and representative inputs. Those details explain whether the new information changed a meaningful decision.
Do not schedule blanket full scans because one demonstration used them. Choose maintenance according to table size, changes, and the workload's estimate sensitivity. Statistics collection has an operational cost even without a new index.
Finish with the specific prefix that helps the target predicates and the measured plan evidence from your instance. Keep the single-column histogram limit visible. A combined density is useful information, not a complete map of every pair.
Related reading on this blog: Filtered Statistics: Fixing Estimates for Skewed Data and SQL Server 2022: Cardinality Estimation (CE) Feedback for Performance.
A density vector is not a map of every combination, it is an average for useful column prefixes.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
