Reading DBCC SHOW_STATISTICS Step by Step

The optimizer expects a handful of rows, and the query returns a crowd. Reading SHOW_STATISTICS helps explain the expectation before you change an index. Its summary describes the data the optimizer knew about.

Choose the Statistics Object Deliberately

I identify the statistics used by a predicate before asking for a full update. A table can have several statistics objects. Their column order and filters determine what each one describes.

An index normally has an associated statistics object. SQL Server also creates column statistics when automatic creation is enabled and a query needs them. User-created statistics add another possibility.

The histogram describes only the first key column of the statistics object. A composite index doesn't provide a separate histogram for every key column. That distinction explains several surprising estimates.

Run the sample in a disposable database. Its repeating region values create a distribution you can inspect. The generated inputs aren't measured results from an existing application.

The explicit statistics object has RegionId first and CustomerId second. Keep that order in mind when reading the later density vector. The histogram will describe RegionId alone.

CREATE TABLE dbo.StatisticsReadingDemo
(
    SaleId int NOT NULL PRIMARY KEY,
    RegionId int NOT NULL,
    CustomerId int NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT dbo.StatisticsReadingDemo(SaleId, RegionId, CustomerId, Amount)
SELECT n, CASE WHEN n % 10 = 0 THEN 1 ELSE 2 END, n % 100, 10.00
FROM (SELECT TOP (2000) ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS numbers;
CREATE STATISTICS ST_StatisticsReadingDemo_RegionCustomer
ON dbo.StatisticsReadingDemo(RegionId, CustomerId) WITH FULLSCAN;
DBCC SHOW_STATISTICS(N'dbo.StatisticsReadingDemo', N'ST_StatisticsReadingDemo_RegionCustomer');

Start SHOW_STATISTICS with Rows and the Update Time

The header tells you when the object was last updated. Rows describes the population represented at that update. It isn't a live count maintained for every subsequent modification.

Rows Sampled reports how much data contributed to the statistics calculation. A sampled histogram extrapolates from that sample. FULLSCAN reads the full applicable population for that update.

Steps reports the histogram's number of intervals. A histogram has at most two hundred steps. It isn't a complete row-level inventory of every distinct value.

Filtered statistics describe the qualifying population. Their row total therefore differs from the whole table by design. Read the filter definition before declaring the header wrong.

The properties function adds a modification counter to the review. It helps show change since the last update. Don't treat its value as a universal threshold requiring immediate FULLSCAN.

SELECT s.name, s.has_filter, s.filter_definition,
       p.last_updated, p.rows, p.rows_sampled, p.steps, p.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE s.object_id = OBJECT_ID(N'dbo.StatisticsReadingDemo');

Read the SHOW_STATISTICS Density Vector by Prefix

The density vector reports information for column prefixes. For this object, RegionId is one prefix. RegionId with CustomerId is another.

All density represents inverse distinctness for the listed combination. It helps estimate an average equality selectivity. It doesn't preserve the complete correlation between every possible pair of values.

A low density describes more distinct combinations. A higher density describes fewer combinations. The listed columns tell you which combination you are interpreting.

Average length describes the relevant stored value width. It doesn't supply a query's measured memory grant. Keep storage summary and execution evidence separate.

I check the prefix before using a density value in an estimate explanation. Reading the wrong prefix makes a plausible story with the wrong data. SHOW_STATISTICS is useful only when its object matches the predicate's question.

Separate Boundary Rows from Range Rows

RANGE_HI_KEY is a step's upper boundary value. EQ_ROWS estimates how many rows equal that boundary. RANGE_ROWS describes rows between the previous boundary and this boundary, excluding the endpoints.

DISTINCT_RANGE_ROWS estimates distinct values inside that interval. AVG_RANGE_ROWS divides the range population across those distinct values. It gives an average frequency for an interior value.

A predicate exactly matching RANGE_HI_KEY can use its EQ_ROWS information. A value inside a range needs a different estimate. Don't apply the boundary count to every value in the interval.

Sampled statistics can produce fractional estimated counts. That isn't a claim that the table stores half a row. It is the result of summarizing and extrapolating data.

Ranges with skew lose detail in the summary. Even a fresh histogram isn't a full map of every interior value. A mismatch doesn't always mean the statistics are old.

Query the Histogram with Its Actual ID

sys.dm_db_stats_histogram exposes comparable information in a relational result. Its column names differ from the DBCC display. Use equal_rows and average_range_rows in this function's output.

Obtain stats_id from sys.stats rather than assuming a fixed number. IDs are scoped to the object. Another table's statistics with the same name still needs its own object identifier.

The high key is sql_variant. Convert it to the appropriate type before comparing values. The sample's first statistics column is int, so an int conversion is suitable.

The function returns step numbers for ordered inspection. Keep that order when explaining intervals. The preceding boundary matters to the meaning of the next range.

You need appropriate metadata and column permissions to inspect statistics. Test the review identity directly. A missing row under restricted permissions isn't evidence that no statistics exist.

SELECT h.step_number, h.range_high_key, h.equal_rows,
       h.range_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.StatisticsReadingDemo')
  AND s.name = N'ST_StatisticsReadingDemo_RegionCustomer'
ORDER BY h.step_number;

Predict an Equality and Check the Plan

Inspect the histogram step for RegionId equal to one. If it is a boundary, EQ_ROWS supplies a useful first prediction. Then run the predicate with the actual execution plan enabled.

Compare estimated rows with actual rows at the filtering operator. The optimizer also considers other available statistics and model rules. The selected estimate isn't always a direct copy of your chosen object's value.

The following queries expose both the matching population and the data rows. With this sample, 1 is a boundary step with EQ_ROWS of 200. The count query also returns 200.

For a composite predicate, the histogram and density information interact. Correlated values and additional filters complicate the estimate. Explain that complexity instead of pretending a single division predicts every plan.

What evidence shows the optimizer used this statistics object? Inspect the plan's statistics usage information where available. That closes the gap between reading a summary and explaining the compiled decision.

SELECT COUNT_BIG(*) AS MatchingRows FROM dbo.StatisticsReadingDemo WHERE RegionId = 1;
SELECT SaleId, CustomerId, Amount FROM dbo.StatisticsReadingDemo WHERE RegionId = 1;

Update Only after SHOW_STATISTICS Explains the Mismatch

A changed population warrants a statistics freshness review. A fresh but coarse histogram warrants a distribution review. Missing useful statistics warrants another review altogether.

I compare those explanations before adding index hints. A hint can hide the estimate problem without fixing it. The next parameter or data change can expose the same weakness again.

SHOW_STATISTICS gives you a compact account of what was summarized. Read its header, prefixes and boundaries together. Then test the estimate against the query that matters.

Related reading on this blog: Statistics Histograms Explained and Find Oldest Updated Statistics: Outdated Statistics.

A histogram is not a copy of the table, it is a summary that helps explain the optimizer's estimate.

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