Statistics Histograms Explained

A row estimate can look mysterious until you see the sampled values behind it. Statistics histograms show how SQL Server summarizes the leading column of a statistic for the optimizer.

Start with the First Key

A statistics object has a histogram on its first key column. A multi-column statistic also has density information for key prefixes, but it does not have a separate histogram for every column. That detail explains why a predicate on the second column can be estimated poorly even when the statistic exists. Read the key order before interpreting the chart.

I check the statistic that the plan actually used. A table can have many statistics, and reading an unrelated histogram wastes time. If the plan estimate diverges from actual rows at one operator, find the predicate and the statistic tied to it. Then compare the queried value with the histogram’s boundaries. The exercise turns a vague estimate complaint into a data-shape question.

Read the Header First

DBCC SHOW_STATISTICS returns a header, density vector, and histogram unless you select one part. The header shows when the statistic was updated, how many rows it describes, and how many rows were sampled. Those fields tell you whether the histogram is a recent and representative picture. A sampled statistic can still be useful. Full scan is not automatically better for every query.

The first code block requests the header for a named statistic. Replace the table and statistic with objects in your database. I capture this result beside the plan. If the object was recently loaded, compare the update time with the load time before blaming the histogram.

DBCC SHOW_STATISTICS (N'dbo.YourTable', N'YourStatistic')
WITH STAT_HEADER;

Inspect the Steps in Statistics Histograms

Each step ends at RANGE_HI_KEY. EQ_ROWS estimates rows equal to that boundary value. RANGE_ROWS estimates rows inside the interval before that boundary, excluding the boundary itself. DISTINCT_RANGE_ROWS and AVG_RANGE_ROWS describe distinct values and average frequency inside the interval. These are estimates derived from sampling or a scan, not a ledger of every row.

I read statistics histograms from left to right. A boundary with high EQ_ROWS signals a common value. A broad range with many distinct values gives the optimizer less detail about one value inside it. The maximum step count is limited, so a column with many distinct values cannot have a step for each value. That compression is useful and imperfect.

DBCC SHOW_STATISTICS (N'dbo.YourTable', N'YourStatistic')
WITH HISTOGRAM;

Use the DMV for Queryable Rows

sys.dm_db_stats_histogram returns similar step data as rows you can filter and join. Pair it with sys.stats to identify the target statistic. The range_high_key is sql_variant, so comparisons need careful conversion. A simple ordered listing avoids type guesses and lets you inspect the entire shape.

I use the DMV when comparing statistics across environments or before and after a load. It is still the same underlying sampled model. A queryable result does not make the estimates exact. Keep update time and sample size next to the step values.

SELECT s.name AS statistic_name,
       h.step_number,
       h.range_high_key,
       h.range_rows,
       h.equal_rows,
       h.distinct_range_rows,
       h.average_range_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.object_id, s.stats_id) AS h
WHERE s.object_id = OBJECT_ID(N'dbo.YourTable')
  AND s.name = N'YourStatistic'
ORDER BY h.step_number;

Connect a Predicate to a Step

For equality on a boundary value, EQ_ROWS is a useful clue to the optimizer’s estimate. For a value between boundaries, the range estimates inform the calculation. Real cardinality estimation also considers predicates, correlation assumptions, parameter values, and plan context. Do not expect to reproduce every operator estimate by reading one cell.

I compare the queried value with the nearest boundaries. If a value lies beyond the last boundary, new ascending data can matter. If a rare value shares a wide range with common values, sampling and step compression can hide its shape. These observations suggest a targeted statistic update, filtered statistic, or query change. They do not justify refreshing the entire database on a schedule.

Understand Sampling Effects

The histogram is built from sampled rows unless the update used a full scan. A rare value can be absent or underrepresented in the sample. The header’s rows and rows sampled show the scale of the sample. A full scan can improve a specific estimate at a significant I/O cost. Test the query before and after a targeted update to see whether the extra work pays.

I avoid the phrase bad statistics until I can name the mismatch. An estimate can be wrong even with a fresh full-scan histogram because the predicate combines columns or uses a parameter-sensitive plan. The histogram represents one column’s distribution, not every relationship in the table. That is a limitation of the model, not a sign that the database is broken.

Watch Skew and Popular Values in Statistics Histograms

Skew means some values occur far more than others. EQ_ROWS makes popular boundary values visible. A query compiled for one popular value can choose a different plan from one compiled for a rare value. If the plan is reused across both, parameter sensitivity can matter more than statistic age. Inspect Query Store and actual plans for representative values.

I ask whether the slow case is a particular value or every value. That question directs the investigation. If only one value suffers, a global index rebuild is a blunt response. Compare the histogram shape, plan, and workload frequency. A targeted design can help without making the common case worse.

Read More Than One Statistic

The optimizer can use several statistics for a query. Check index statistics, auto-created column statistics, and filtered statistics relevant to the predicate. A filtered statistic describes only rows matching its filter, so its histogram has a different population. Compare the filter with the actual query. A mismatch can explain why the optimizer did not use it.

I keep the plan open while reading statistics. The plan names the objects that matter to that compilation. Reading every histogram on a wide table is a fine way to use a whole afternoon and learn little. Start at the operator with the estimate gap, then follow its inputs.

Use the Shape of Statistics Histograms to Choose a Fix

A stale upper bound after a large load suggests a timely targeted update. A wide range hiding a critical status value suggests a filtered statistic or index. A plan reused across very different values suggests a parameter-sensitive approach. A predicate that applies a function to the column suggests query rewriting. Statistics histograms help choose among those paths.

What value did the optimizer think was common, and what did the actual plan show? Answer that with your server’s data. Do not invent a universal EQ_ROWS threshold. A histogram is useful when it explains a specific estimate and leads to a testable change.

Related reading on this blog: Understanding Incremental Statistics and Find Outdated Statistics: SQL in Sixty Seconds #137.

A histogram is not a complete copy of the data, it is a compact model for estimating rows.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.