The Curious Case of the Missing Histogram

A few weeks ago, I published a blog post titled "When Statistics Stay Empty Forever: A Hidden Edge Case with Empty Tables"

In that post, I described a scenario where statistics are created on an empty table, resulting in a statistics object without a histogram. If AUTO_UPDATE_STATISTICS is disabled and your maintenance solution doesn't explicitly update statistics whose modification_counter is NULL, those statistics may never get a histogram - even after the table grows to millions or billions of rows.

Recently, I ran into another case involving missing histograms.

At first, I thought it was the same issue.

It wasn't.

An Impossible Observation

One of our customers had a table with about 7 million rows.

The table was created about two years ago and had a clustered primary key on an identity column.

When I checked the statistics properties for the clustered index, every value returned by sys.dm_db_stats_properties was NULL.

SELECT *
FROM sys.dm_db_stats_properties
(
    OBJECT_ID('dbo.MyTable'),
    @stats_id
);

That included:

• last_updated

• rows

• rows_sampled

• steps

• modification_counter

This normally means the statistics blob doesn't exist - the histogram has never been created.

But this table certainly wasn't empty.

Even more interesting, AUTO_UPDATE_STATISTICS was enabled at the database level, and the statistics object wasn't marked with NORECOMPUTE.

So why had SQL Server never created the histogram?

Eliminating the Obvious

The first thing I checked was whether statistics on the same table were being updated normally.

They were.

Two automatically created column statistics had been updated only a week earlier.

Only the clustered index statistics had never been initialized.

That ruled out any database-wide statistics issue.

A Different Hypothesis

After discussing the case with ChatGPT, a different explanation emerged.

Maybe SQL Server had simply never needed the histogram.

At first, that sounded unlikely.

After all, the table contained seven million rows and had been in production for two years.

But then I started thinking about the workload.

The clustered key was a surrogate primary key.

Most queries looked like this:

SELECT ...
FROM dbo.MyTable
WHERE ID = @ID;

For a unique index, SQL Server already knows the cardinality of an equality predicate.

There can be at most one matching row.

A histogram adds no value here.

Testing the Theory

I first verified that none of the execution plans currently in the plan cache appeared to require a histogram on the clustered key.

Then I executed an equality lookup myself:

SELECT *
FROM dbo.MyTable
WHERE ID = 1234;

Still no histogram.

Then I changed the predicate:

SELECT *
FROM dbo.MyTable
WHERE ID <= 1234;

Immediately afterward, the histogram existed.

The statistics had finally been initialized.

Why It Makes Sense

The clustered index had originally been created when the table was empty.

Since there was no data, SQL Server created the statistics object without a histogram.

Over the next two years, the table grew to seven million rows.

However, SQL Server only creates or updates statistics when the optimizer needs them.

Apparently, every compiled query against the clustered key was either an equality lookup or another operation that didn't require histogram-based cardinality estimation.

Until a range predicate appeared, there was simply no reason for SQL Server to create the histogram.

Nothing was wrong.

SQL Server was doing exactly what it was designed to do: avoiding unnecessary work.

In fact, this may not be an unusual workload at all.

Many OLTP systems use surrogate keys as clustered primary keys and almost always access rows using equality predicates. If range predicates on the primary key never occur, SQL Server may never need the histogram.

How This Differs from the Previous Edge Case

Although both cases involve statistics objects without histograms, the underlying causes are completely different.

In my previous post:

• The histogram should have been created.

• AUTO_UPDATE_STATISTICS was disabled.

• The maintenance solution didn't handle modification_counter = NULL.

• As a result, the statistics could remain uninitialized forever, even if queries later required the histogram.

In this case:

• AUTO_UPDATE_STATISTICS was enabled.

• SQL Server was fully capable of creating the histogram.

• It simply never had a reason to do so.

That's an important distinction.

One case is a maintenance gap.

The other is expected optimizer behavior.

A Small but Important Improvement

In my previous post, I suggested that Ola Hallengren should update his maintenance solution such that statistics with modification_counter = NULL should always be updated during statistics maintenance, since this indicates that the statistics blob doesn't exist.

I'm happy to say that this behavior has now been incorporated into the latest version of his maintenance solution.

As a result, statistics whose modification_counter is NULL are now always updated during maintenance.

This addresses the maintenance scenario described in my previous post and ensures that uninitialized statistics won't remain that way simply because automatic statistics updates are disabled.

The Missing Histogram - Final Thoughts

This investigation reminded me that SQL Server's behavior is often more deliberate than it first appears.

Seeing a statistics object without a histogram on a seven-million-row table looked like evidence that something had gone wrong.

In reality, nothing had.

The optimizer had simply never needed the histogram.

Sometimes, the most interesting SQL Server mysteries turn out not to be bugs at all - they're opportunities to better understand how the optimizer really works.