Manually comparing statistics gets tedious when every result arrives in a separate grid. sys.dm_db_stats_histogram lets you filter and join those rows instead of reading each DBCC result by eye. The stored numbers still describe the last statistics sample.
Choose the Statistic and Its Leading Column
The histogram describes the first column of a statistics object. A multicolumn statistic does not contain a separate histogram for every column. Begin with sys.stats and sys.stats_columns so the chosen statistics identifier matches the column you want to inspect.
I check that leading column before interpreting any range. A correctly written query against the wrong statistic gives a very neat wrong answer. Index statistics and automatically created column statistics can coexist on the same table. Their identifiers belong to the object, so always carry object_id alongside stats_id.
This example uses SQL Server 2016 SP1 CU2 or later with the histogram function available. Use a disposable database and fresh object names. The sample dates provide predictable input, but you will inspect the stored histogram on your own server rather than assume its step layout.
CREATE TABLE dbo.HistogramReviewDemo
(OrderID int NOT NULL PRIMARY KEY, OrderDate date NOT NULL);
INSERT dbo.HistogramReviewDemo VALUES
(1,'20260901'),(2,'20260901'),(3,'20260903'),
(4,'20260905'),(5,'20260905'),(6,'20260905');
CREATE STATISTICS st_HistogramReviewDemo_OrderDate
ON dbo.HistogramReviewDemo(OrderDate) WITH FULLSCAN;
SELECT s.stats_id, s.name, c.name AS LeadingColumn
FROM sys.stats s
JOIN sys.stats_columns sc ON sc.object_id=s.object_id
AND sc.stats_id=s.stats_id AND sc.stats_column_id=1
JOIN sys.columns c ON c.object_id=sc.object_id
AND c.column_id=sc.column_id
WHERE s.object_id=OBJECT_ID(N'dbo.HistogramReviewDemo');Query sys.dm_db_stats_histogram as a Rowset
CROSS APPLY supplies the table and statistic identifiers to the function. It returns one row for each stored step. Convert range_high_key from sql_variant to the known column type before comparing it with dates. Do not apply one arbitrary conversion across statistics for unrelated types.
SELECT s.name, h.step_number,
CONVERT(date,h.range_high_key) AS RangeHighDate,
h.range_rows, h.equal_rows,
h.distinct_range_rows, h.average_range_rows
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_histogram(s.object_id,s.stats_id) h
WHERE s.object_id=OBJECT_ID(N'dbo.HistogramReviewDemo')
AND s.name=N'st_HistogramReviewDemo_OrderDate'
ORDER BY h.step_number;Equal_rows estimates rows equal to the step's upper boundary. Range_rows describes rows between the previous upper boundary and this one, excluding the current endpoint. Average_range_rows distributes that interval across its distinct values. These are different quantities, so the endpoint's equality count cannot represent every value in its range.
A histogram has a limited number of steps. Large datasets therefore combine many values into ranges. Fullscan improves the input count but does not create one step for every possible value. A stored equality estimate inside a broad range still relies on the range representation.
Locate the Step Covering a Value
The first upper boundary greater than or equal to the requested value supplies its covering step. Equality at the endpoint uses equal_rows; an interior value relates to average_range_rows. A value beyond the largest endpoint has no covering step and needs separate out-of-range estimation behavior.
DECLARE @Value date='20260903';
SELECT TOP (1) h.step_number,
CONVERT(date,h.range_high_key) AS RangeHighDate,
h.range_rows, h.equal_rows, h.average_range_rows
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_histogram(s.object_id,s.stats_id) h
WHERE s.object_id=OBJECT_ID(N'dbo.HistogramReviewDemo')
AND s.name=N'st_HistogramReviewDemo_OrderDate'
AND CONVERT(date,h.range_high_key)>=@Value
ORDER BY h.step_number;
SELECT COUNT_BIG(*) AS ActualRowsForValue
FROM dbo.HistogramReviewDemo WHERE OrderDate=@Value;The COUNT reads current data. The histogram reads stored statistics. Their disagreement is diagnostic evidence, not proof that the function malfunctioned. Sampling, later inserts, and compression of values into steps explain why those numbers serve different purposes.
Compare Range Counts With Current Data
Use LAG to make the previous boundary visible. Then count rows strictly between that boundary and the current one. Excluding both endpoints matches the interval represented by range_rows. The first step needs separate treatment because it has no previous endpoint.
WITH Steps AS
(
SELECT h.step_number, CONVERT(date,h.range_high_key) AS HighDate,
LAG(CONVERT(date,h.range_high_key)) OVER
(ORDER BY h.step_number) AS PreviousHighDate,
h.range_rows, h.equal_rows
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_histogram(s.object_id,s.stats_id) h
WHERE s.object_id=OBJECT_ID(N'dbo.HistogramReviewDemo')
AND s.name=N'st_HistogramReviewDemo_OrderDate'
)
SELECT s.*, a.CurrentRangeRows
FROM Steps s
CROSS APPLY
(SELECT COUNT_BIG(*) AS CurrentRangeRows
FROM dbo.HistogramReviewDemo d
WHERE s.PreviousHighDate IS NOT NULL
AND d.OrderDate>s.PreviousHighDate AND d.OrderDate<s.HighDate) a
ORDER BY s.step_number;Run that comparison selectively on a real large table. One count per step can generate substantial reads. A diagnostic that audits every statistic can become its own workload problem. Narrow the table, statistic, and value range before expanding the investigation.
Compare sys.dm_db_stats_histogram With New Data
Combine the histogram with sys.dm_db_stats_properties for its update time, sampled rows, and modification counter. Add newer rows after the initial statistics creation to make the comparison concrete. Do not expect the tiny demonstration to reproduce every automatic-update threshold or plan behavior.
INSERT dbo.HistogramReviewDemo VALUES (7,'20260920'),(8,'20260921');
SELECT s.name, p.last_updated, p.rows, p.rows_sampled,
p.modification_counter, h.LastHistogramDate,
d.NewestDataDate
FROM sys.stats s
OUTER APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) p
OUTER APPLY
(SELECT MAX(CONVERT(date,range_high_key)) AS LastHistogramDate
FROM sys.dm_db_stats_histogram(s.object_id,s.stats_id)) h
CROSS APPLY
(SELECT MAX(OrderDate) AS NewestDataDate
FROM dbo.HistogramReviewDemo) d
WHERE s.object_id=OBJECT_ID(N'dbo.HistogramReviewDemo')
AND s.name=N'st_HistogramReviewDemo_OrderDate';I compare the last boundary with the workload's requested dates. A fresh query targeting newer rows deserves closer estimate review. An old boundary alone does not prove the optimizer chose a bad plan. Capture estimated and actual rows for the statement that prompted the investigation.
Know the Limits of sys.dm_db_stats_histogram
The function exposes the histogram, not the density vector. Use DBCC SHOW_STATISTICS when that additional statistics information is required. Filtering its displayed output manually and querying histogram rows are complementary techniques, not competing correctness levels.
Permissions can also hide statistics metadata. Check the required access before classifying an empty result as missing statistics. Preserve the capture time with any report. Is the discrepancy caused by stale input, a sampled distribution, or a value the histogram cannot represent precisely?
Update the relevant statistic only after identifying a meaningful estimate problem. Choose sample scope and maintenance timing deliberately. Then recheck the affected plan rather than treating a newer timestamp as the tuning result. The purpose is to explain an estimate with evidence that can be queried and repeated.
Keep Type and Null Semantics Consistent
A date histogram cannot be compared safely by sorting its displayed text. Convert endpoints to date, as the examples do. For numeric columns, use the matching numeric type. For character columns, collation and the stored string type affect comparison. Preserve the same semantics as the predicate under investigation.
NULL values also need their own inspection. An equality predicate for a non-null date and an IS NULL predicate ask different questions. Do not make a generic conversion that drops null boundaries and then claim the report explains every query on the column. Review the histogram output before building a reusable report around it.
Record the Query That Needs the Estimate
Keep the statement text, requested value, compatibility level, and actual plan with the statistics capture. The cardinality estimator combines statistics with predicate shape, joins, and assumptions. A standalone histogram count is not necessarily the final estimate shown several operators later in a complex plan.
For a simple equality, compare the relevant endpoint or range estimate with the access operator's estimate. For a range predicate, inspect every covered step and its boundaries. For joins, review both distributions instead of attributing the complete estimate to one histogram. This extra context turns a statistics snapshot into an explanation that another reader can reproduce. It also prevents a blanket statistics update from being proposed when the real issue is a different predicate or an implicit conversion.
The output of sys.dm_db_stats_histogram belongs to one identified statistic and capture time. Pair sys.dm_db_stats_histogram with the actual predicate before using it to explain a plan estimate.
Related reading on this blog: Filtered Statistics: Fixing Estimates for Skewed Data and Execution Plan: Estimated vs Actual: SQL in Sixty Seconds #113.
A histogram rowset is not a live row count, it is a queryable model of the distribution captured by a statistics update.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
