SQL Server Statistics Internals: Decoding STATS_STREAM Byte by Byte in 5 Proven Steps

SQL Server statistics internals reward the engineers who look past DBCC SHOW_STATISTICS. Behind the three familiar result sets sits a single binary object, the stats stream, and decoding it byte by byte is the most direct way to understand what the cardinality estimator actually has to work with. Our position is simple: the format is undocumented, so the right way to decode it is not to memorise someone else's offset table but to derive the layout on your own build, using values the supported views already give you.

This paper sets out that method end to end. It covers the SQL Server statistics internals of a statistics object, how the histogram drives estimates, a reproducible lab, a capture script, and an oracle-guided Python locator that finds header and histogram fields inside the blob and proves each finding against ground truth. It is written for database architects and senior DBAs who own plan stability on SQL Server 2016 through SQL Server 2025. Behaviour cited from Microsoft documentation was checked on 5 November 2026.

SQL Server statistics internals: what a statistics object contains

In SQL Server statistics internals, a statistics object describes the distribution of values in up to 32 key columns. The documented view of it has three parts. The header records when it was built, how many rows the table had, how many were sampled and how many histogram steps exist. The density vector records, for each left-based prefix of the key, the reciprocal of the number of distinct values and the average key length. The histogram, built on the leading column only, holds at most 200 steps, each with an upper boundary and four row-count measures.

Understanding SQL Server statistics internals starts with recognising that these three result sets are projections of one stored object. The stats stream is that object serialised. When you decode it, you are reading the same numbers the optimizer reads, before any presentation layer rounds or reformats them.

Figure 1. SQL Server statistics internals: three documented readers and the undocumented stream they describe.

The conceptual order shown in Figure 1 is broadly agreed in the practitioner community: a stream header with version, lengths, a column count and a checksum; one descriptor per key column carrying type, length, precision, scale and collation information; a statistic header with the timestamp, row counts, densities and average key lengths; the histogram steps; and, in later versions, trailing sections such as the string summary used for LIKE estimates. Exact sizes and offsets are not documented and have changed between releases, which is why we derive them rather than assert them.

How the SQL Server histogram drives cardinality estimation

Before touching bytes, it is worth being precise about what each histogram field is for, because the decoding exercise is only useful if it sharpens your reasoning about estimates. The estimator uses the step whose RANGE_HI_KEY is the first boundary at or above the predicate value.

Figure 2. A histogram step in SQL Server statistics internals and the field each predicate shape relies on.

An equality predicate on a boundary value is estimated from EQ_ROWS. An equality predicate on a value strictly inside a step is estimated from AVG_RANGE_ROWS, which is RANGE_ROWS divided by DISTINCT_RANGE_ROWS. A predicate whose value is unknown at compile time, such as a local variable without recompilation, falls back to the density vector: table rows multiplied by the "All density" of the leading column. These rules hold under both the legacy and the default cardinality estimator; what changed in later models is how predicates are combined, not how a single step is read.

-- SQL Server statistics internals: read the documented view first (read-only).
-- sys.dm_db_stats_histogram needs SQL Server 2016 SP1 CU2 or later.
SELECT
    s.name                         AS stats_name,
    p.last_updated,
    p.rows,
    p.rows_sampled,
    p.steps,
    p.unfiltered_rows,
    p.modification_counter,
    p.persisted_sample_percent
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.stats_probe');

SELECT
    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.stats_probe')
  AND s.name      = N'st_stats_probe_k'
ORDER BY h.step_number;

-- SQL Server statistics internals: density vector for the same object.
DBCC SHOW_STATISTICS (N'dbo.stats_probe', st_stats_probe_k) WITH DENSITY_VECTOR;

The SQL Server statistics internals lab: designing data that decodes cleanly

Byte-level decoding is an experiment, and experiments need controlled inputs. We use a disposable database, a single integer column and a distribution engineered so that every histogram measure takes a value distinct from the others. Each key value v appears (v % 7) + 2 times, so boundary counts vary between 2 and 8, interior row counts are several times larger than interior distinct counts, and averages are rarely whole numbers.

Figure 3. The five-step oracle-guided method for decoding SQL Server statistics internals.

-- SQL Server statistics internals lab, step 1: controlled data.
-- LAB ONLY. Run in a disposable database on a non-production instance.
-- CONFIRMATION GATE: the next statement must return 'stats_lab' before you continue.
SELECT DB_NAME() AS current_database;

-- Verification before the drop: confirm the object, if present, is the lab table.
SELECT o.name, o.create_date
FROM sys.objects AS o
WHERE o.object_id = OBJECT_ID(N'dbo.stats_probe');

DROP TABLE IF EXISTS dbo.stats_probe;

CREATE TABLE dbo.stats_probe (
    probe_id INT IDENTITY(1, 1) NOT NULL,
    k        INT                NOT NULL,
    CONSTRAINT pk_stats_probe PRIMARY KEY CLUSTERED (probe_id)
);

WITH numbers AS (
    SELECT TOP (20000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS v
    FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b
)
INSERT INTO dbo.stats_probe (k)
SELECT n.v
FROM numbers AS n
CROSS APPLY (
    SELECT TOP ((n.v % 7) + 2) 1 AS x
    FROM sys.all_objects
) AS repeat_rows;

-- Step 2: build the statistic deterministically and keep it stable.
CREATE STATISTICS st_stats_probe_k
    ON dbo.stats_probe (k)
    WITH FULLSCAN, NORECOMPUTE;

-- Validation after: row count and step count are known before decoding starts.
SELECT p.rows, p.rows_sampled, p.steps
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.stats_probe')
  AND s.name      = N'st_stats_probe_k';

FULLSCAN removes sampling variance from the experiment, and NORECOMPUTE stops auto-update from replacing the object between captures. In a real workload both choices have costs; in the lab they are what make SQL Server statistics internals reproducible.

Capturing STATS_STREAM together with its ground truth

The capture packages three things into one JSON document: the stream as hexadecimal, the header values from sys.dm_db_stats_properties, and every histogram step from sys.dm_db_stats_histogram. The values are captured in the same batch so that they describe the same version of the statistic. Without that pairing, no claim about SQL Server statistics internals can be verified.

-- SQL Server statistics internals, step 3: capture stream plus ground truth.
-- Read-only. Requires SELECT on the table and its statistics columns.
SET NOCOUNT ON;

DECLARE @object_id INT = OBJECT_ID(N'dbo.stats_probe');
DECLARE @stats_id  INT = (
    SELECT s.stats_id
    FROM sys.stats AS s
    WHERE s.object_id = @object_id
      AND s.name      = N'st_stats_probe_k'
);

DECLARE @capture TABLE (
    stats_stream VARBINARY(MAX),
    row_count    BIGINT,
    data_pages   BIGINT
);

INSERT INTO @capture (stats_stream, row_count, data_pages)
EXEC (N'DBCC SHOW_STATISTICS (N''dbo.stats_probe'', st_stats_probe_k)
        WITH STATS_STREAM, NO_INFOMSGS;');

DECLARE @json NVARCHAR(MAX) = (
    SELECT
        CONVERT(VARCHAR(MAX), c.stats_stream, 2) AS stream_hex,
        'int'                                    AS key_type,
        JSON_QUERY((
            SELECT p.last_updated, p.rows, p.rows_sampled,
                   p.steps, p.unfiltered_rows
            FROM sys.dm_db_stats_properties(@object_id, @stats_id) AS p
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ))                                       AS header,
        JSON_QUERY((
            SELECT h.step_number,
                   CAST(h.range_high_key AS INT) AS range_high_key,
                   h.range_rows,
                   h.equal_rows,
                   h.distinct_range_rows,
                   h.average_range_rows
            FROM sys.dm_db_stats_histogram(@object_id, @stats_id) AS h
            ORDER BY h.step_number
            FOR JSON PATH
        ))                                       AS histogram
    FROM @capture AS c
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

SELECT @json AS capture_json;

Assigning the JSON to a variable before selecting it avoids the line splitting that client tools apply to streamed FOR JSON output. The small Python wrapper below runs the capture and writes the file; credentials come from environment variables and nothing is hardcoded.

"""SQL Server statistics internals: run capture_stats.sql and save the JSON.

Requires: pyodbc and Microsoft ODBC Driver 18 for SQL Server.
Environment: MSSQL_HOST, MSSQL_DATABASE, MSSQL_USER, MSSQL_PASSWORD
Usage: python capture_stats.py capture_stats.sql before.json
"""
import os
import sys

import pyodbc

# SQL Server statistics internals: connection built only from environment variables
conn_str = (
    "DRIVER={ODBC Driver 18 for SQL Server};"
    f"SERVER={os.environ['MSSQL_HOST']};"
    f"DATABASE={os.environ['MSSQL_DATABASE']};"
    f"UID={os.environ['MSSQL_USER']};"
    f"PWD={os.environ['MSSQL_PASSWORD']};"
    "Encrypt=yes;TrustServerCertificate=no;"
)

with open(sys.argv[1], encoding="utf-8") as fh:
    batch = fh.read()

with pyodbc.connect(conn_str, autocommit=True) as conn:
    cur = conn.cursor()
    cur.execute(batch)
    while cur.description is None:          # skip rowcount-only results
        cur.nextset()
    capture_json = cur.fetchone()[0]

with open(sys.argv[2], "w", encoding="utf-8") as fh:
    fh.write(capture_json)
print(f"wrote {sys.argv[2]} ({len(capture_json)} characters)")

Decoding SQL Server statistics internals with an oracle-guided locator

The locator applies SQL Server statistics internals reasoning directly: it turns every ground-truth value into the byte patterns the engine could plausibly have used to store it, namely little-endian 8-byte and 4-byte integers, 2-byte unsigned integers, and 4-byte and 8-byte IEEE 754 floats. It then searches the blob. For the histogram it uses a stronger technique. Because the boundary keys are strictly increasing integers, it finds the offset of step one's key, the offset of step two's key, and tests whether that distance repeats for every step. A stride that matches all steps is not a coincidence; it is the record size.

Once the grid is known, each remaining histogram measure is found by testing every relative offset inside the stride and keeping only those that decode correctly for every step. That is the core discipline of decoding SQL Server statistics internals: a field is identified only when it agrees with ground truth across the whole histogram.

"""SQL Server statistics internals: oracle-guided STATS_STREAM field locator.

Locates header fields and histogram step fields inside an undocumented
STATS_STREAM blob by searching for the binary encodings of values that the
supported DMVs already report. Read-only; works on a JSON capture file.

Usage:
    python stats_stream_locator.py capture.json
    python stats_stream_locator.py --diff before.json after.json
"""
import json
import struct
import sys
from collections import Counter

KEY_CODECS = {"tinyint": "<B", "smallint": "<h", "int": "<i", "bigint": "<q"}
FLOAT_CODECS = {"float32": "<f", "float64": "<d"}


# SQL Server statistics internals: generic byte search helpers
def find_all(blob, pattern):
    hits, i = [], blob.find(pattern)
    while i != -1:
        hits.append(i)
        i = blob.find(pattern, i + 1)
    return hits


def encodings(value):
    """Every plausible little-endian encoding of a numeric value.

    SQL Server statistics internals: counts may be stored as integers or floats.
    """
    out = {name: struct.pack(fmt, float(value)) for name, fmt in FLOAT_CODECS.items()}
    if float(value).is_integer():
        n = int(value)
        out["int64"] = struct.pack("<q", n)
        if -2**31 <= n < 2**31:
            out["int32"] = struct.pack("<i", n)
        if 0 <= n < 2**16:
            out["uint16"] = struct.pack("<H", n)
    return out


def locate_header(blob, header):
    report = {}
    for field in ("rows", "rows_sampled", "unfiltered_rows", "steps"):
        if header.get(field) is None:
            continue
        for enc, pattern in encodings(header[field]).items():
            hits = find_all(blob, pattern)
            if hits and len(hits) <= 8:          # ignore patterns too common to mean anything
                report.setdefault(field, []).append({"encoding": enc, "offsets": hits})
    return report


def infer_histogram_grid(blob, steps, key_type):
    """Find (base offset, stride) using the strictly increasing RANGE_HI_KEY values.

    SQL Server statistics internals: fixed-width keys make step records uniform.
    """
    fmt = KEY_CODECS[key_type]
    hits = [set(find_all(blob, struct.pack(fmt, int(s["range_high_key"])))) for s in steps]
    candidates = Counter()
    for o0 in hits[0]:
        for o1 in hits[1]:
            if 0 < o1 - o0 <= 256:
                candidates[(o0, o1 - o0)] = sum(
                    1 for i, offs in enumerate(hits) if o0 + i * (o1 - o0) in offs)
    if not candidates:
        return None
    (base, stride), score = candidates.most_common(1)[0]
    return {"key_base": base, "stride": stride, "steps_matched": score, "steps_total": len(steps)}


def locate_step_fields(blob, steps, grid):
    """For each histogram column, find the offset relative to the key that decodes every step."""
    fields = {}
    base, stride = grid["key_base"], grid["stride"]
    for field in ("equal_rows", "range_rows", "distinct_range_rows", "average_range_rows"):
        for enc, fmt in FLOAT_CODECS.items():
            width = struct.calcsize(fmt)
            for rel in range(-stride, stride):
                ok = True
                for i, step in enumerate(steps):
                    pos = base + i * stride + rel
                    if pos < 0 or pos + width > len(blob):
                        ok = False
                        break
                    got = struct.unpack_from(fmt, blob, pos)[0]
                    want = float(step[field])
                    if abs(got - want) > max(1e-3, abs(want) * 1e-6):
                        ok = False
                        break
                if ok:
                    fields.setdefault(field, []).append({"encoding": enc, "relative_offset": rel})
    return fields


def diff(blob_a, blob_b):
    """Changed byte ranges between two captures of the same statistic."""
    ranges, start = [], None
    for i in range(max(len(blob_a), len(blob_b))):
        same = i < len(blob_a) and i < len(blob_b) and blob_a[i] == blob_b[i]
        if not same and start is None:
            start = i
        elif same and start is not None:
            ranges.append((start, i - 1))
            start = None
    if start is not None:
        ranges.append((start, max(len(blob_a), len(blob_b)) - 1))
    return ranges


def load(path):
    with open(path, encoding="utf-8") as fh:
        cap = json.load(fh)
    return cap, bytes.fromhex(cap["stream_hex"].removeprefix("0x"))


if __name__ == "__main__":
    if sys.argv[1] == "--diff":
        (_, a), (_, b) = load(sys.argv[2]), load(sys.argv[3])
        print(f"lengths: {len(a)} -> {len(b)} bytes")
        for lo, hi in diff(a, b):
            print(f"changed 0x{lo:04X}-0x{hi:04X} ({hi - lo + 1} bytes)")
        sys.exit(0)
    cap, blob = load(sys.argv[1])
    print(f"stream length: {len(blob)} bytes")
    print("header:", json.dumps(locate_header(blob, cap["header"]), indent=2))
    grid = infer_histogram_grid(blob, cap["histogram"], cap.get("key_type", "int"))
    print("histogram grid:", grid)
    if grid and grid["steps_matched"] == grid["steps_total"]:
        print("step fields:", json.dumps(locate_step_fields(blob, cap["histogram"], grid), indent=2))

We unit-test the locator against synthetic streams with known layouts before using it on real captures; the synthetic data exists only in our test suite and is not represented here as engine output. Two properties of the method matter. First, it is self-checking: a field that is reported for one offset in every step cannot be an accident across 200 steps. Second, it is version-neutral, because it never assumes an offset table; it rediscovers the layout from whatever build produced the capture.

Reading the locator’s output

Three outcomes are common, and each one teaches something specific about SQL Server statistics internals.

• A single candidate per field. The layout for that build is resolved. Record the stride, the relative offsets and the encodings in your lab notes together with @@VERSION.
• Two candidates for one field. Two measures had equal values on every step. Redesign the distribution so they differ, rebuild with FULLSCAN and capture again.
• No grid found. Either the key type is not fixed-width, the statistic includes a NULL step that shifts the first record, or step records are not uniform on that build. Start again with a BIGINT key, a NOT NULL column and fewer distinct values.

Header fields behave differently. Row counts such as 100,000 can appear more than once in a stream, for example as both rows and rows sampled under FULLSCAN. That is why the locator reports every candidate offset of the SQL Server statistics internals it finds rather than guessing, and why the verification step that follows is not optional.

Verifying SQL Server statistics internals with a differential experiment

The strongest test of any decoded layout is to change one thing and watch which bytes move. We add rows for a single key value, rebuild the statistic, capture again and diff the two streams.

-- SQL Server statistics internals, step 5: change one thing, rebuild, recapture.
-- LAB ONLY. Same confirmation gate as step 1: DB_NAME() must be 'stats_lab'.
SELECT DB_NAME() AS current_database;

DECLARE @target_key INT = (
    SELECT CAST(h.range_high_key AS INT)
    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.stats_probe')
      AND s.name        = N'st_stats_probe_k'
      AND h.step_number = 100
);

INSERT INTO dbo.stats_probe (k)
SELECT TOP (1000) @target_key
FROM sys.all_objects;

UPDATE STATISTICS dbo.stats_probe (st_stats_probe_k)
    WITH FULLSCAN, NORECOMPUTE;

-- Validation after: if the target key is still a boundary, its EQ_ROWS
-- should have grown by 1,000.
SELECT h.step_number, h.range_high_key, h.equal_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.stats_probe')
  AND s.name      = N'st_stats_probe_k'
  AND CAST(h.range_high_key AS INT) = @target_key;

Run the capture again into after.json, then python stats_stream_locator.py --diff before.json after.json. The expectation, which you should confirm on your build rather than take from us, is that the changed ranges fall into three groups: the update timestamp and row counts in the statistic header, any checksum or length fields in the stream header, and the record of the target step. If the rebuild also moved step boundaries, the diff widens across the histogram; rerun the locator on after.json and compare the two grids. Either result advances your understanding of SQL Server statistics internals, because both are grounded in measured bytes.

Proving the estimator reads what you decoded

Decoding SQL Server statistics internals is only worth the effort if it connects to plan behaviour. The final check compares the decoded EQ_ROWS and AVG_RANGE_ROWS against the estimates the optimizer produces for matching predicates.

-- SQL Server statistics internals, step 6: compare estimates with decoded values.
-- Estimated plans only; nothing is executed against the data.
SET SHOWPLAN_XML ON;
GO
-- Boundary value: EstimateRows should equal EQ_ROWS of that step.
SELECT COUNT_BIG(*) FROM dbo.stats_probe WHERE k = 10050 OPTION (RECOMPILE);
GO
-- Interior value: EstimateRows should equal AVG_RANGE_ROWS of the containing step.
SELECT COUNT_BIG(*) FROM dbo.stats_probe WHERE k = 10049 OPTION (RECOMPILE);
GO
SET SHOWPLAN_XML OFF;
GO

Replace the literals with a boundary and an interior value from your own histogram, since step boundaries depend on the build and the data. Read the EstimateRows attribute of the scan or seek operator in each plan. When those numbers match the decoded fields, the loop is closed: you have traced an estimate from the plan, through the histogram, down to the bytes that store it.

Moving statistics between environments: supported and unsupported paths

The most common practical reason to care about SQL Server statistics internals is reproducing a production plan without production data. Two paths exist, and the distinction matters for any regulated estate.

Histogram boundaries are real column values. A stats stream taken from a table of customer names or account numbers contains some of those values, so streams and clones must be handled under the same data classification as the source database.

What this means for platform owners

Few teams need to decode statistics routinely. The value of mastering SQL Server statistics internals lies in the decisions it sharpens. Teams that understand how a step is stored and read make better choices about sampling rates and PERSIST_SAMPLE_PERCENT, recognise when a histogram's 200-step limit is the real cause of a skewed estimate, design filtered statistics for hot ranges deliberately, and diagnose ascending-key problems from evidence rather than folklore.

We recommend three standing practices. Monitor modification_counter and rows_sampled against rows for the statistics behind critical queries. Capture DBCC CLONEDATABASE snapshots before major releases so that plan regressions can be studied offline. And keep byte-level analysis of SQL Server statistics internals in a lab, documented with the exact build it was performed on.

Limits of this analysis

For SQL Server statistics internals we publish a method and tooling, not a fixed offset table, because the format is undocumented and Microsoft states that future compatibility is not guaranteed. The locator handles a SQL Server histogram on fixed-width integer keys; string, date and multi-column statistics use different representations and need extended codecs. We have not reported measured offsets here; run the locator on your own build and record the results with its version. All scripts should be tested on a non-production instance first, and any environment holding cloned statistics should sit inside a tested backup and disaster recovery posture.

Frequently asked questions

What is STATS_STREAM in SQL Server statistics internals?

It is the binary serialisation of a statistics object, returned by DBCC SHOW_STATISTICS ... WITH STATS_STREAM. It contains the header, density vector, histogram and, in newer versions, additional sections. Microsoft documents it as informational only and unsupported.

Can I edit a statistics stream and load it back?

The stream carries a checksum, and loading it uses undocumented UPDATE STATISTICS ... WITH STATS_STREAM syntax. It is suitable for research in a lab. For production troubleshooting, use DBCC CLONEDATABASE.

Why use sys.dm_db_stats_histogram if DBCC SHOW_STATISTICS already shows the histogram?

The DMF returns typed, set-based output that can be joined and serialised to JSON, which is what makes ground-truth capture and automated decoding practical. It requires SQL Server 2016 SP1 CU2 or later. The column definitions are in the sys.dm_db_stats_histogram documentation.

Does the layout differ between SQL Server versions?

Community analysis shows it has changed across releases, and Microsoft gives no compatibility guarantee. That is the reason our method derives the layout from each capture instead of reusing offsets. The DBCC SHOW_STATISTICS documentation describes the supported result sets.

Is decoding SQL Server statistics internals safe on production?

Reading the stream is a read-only DBCC call, but the analysis belongs in a lab. Building controlled statistics, inserting test rows and loading streams back are lab activities. Keep SQL Server statistics internals research on non-production instances with their own data classification.

How many steps can a SQL Server histogram have?

Up to 200 steps for the leading key column, with an additional step for NULL when the column contains NULLs. Skewed distributions that need more resolution are typically addressed with filtered statistics.

How MinervaDB helps

MinervaDB is a vendor-neutral database infrastructure company trusted by 900+ enterprises. Our SQL Server consulting practice applies SQL Server statistics internals to plan stability reviews, cardinality estimation audits and upgrade assessments from SQL Server 2016 to SQL Server 2025, and our 24x7 SQL Server support handles plan regressions under a 15-minute S1 SLA.