When Statistics Stay Empty Forever: A Hidden Edge Case with Empty Tables

Guy Glantser
12 hours ago
3 min read

Many SQL Server DBAs rely on automated statistics maintenance solutions such as Ola Hallengren’s maintenance scripts. These scripts typically update statistics only when the modification counter exceeds a threshold.

But there is a corner case that can cause statistics to remain empty forever, and many DBAs are not aware of it.

I certainly wasn’t. And I would like to thank Nirit Leibovitch for teaching me this lesson.

The Scenario

Consider the following simple sequence:

Create a table.
Create an index on it while the table is empty.
Insert data later.
Disable AUTO_UPDATE_STATISTICS and rely on a scheduled maintenance job (like Ola’s).

Example:

CREATE TABLE
	dbo.TestTable
(
	Id		INT	NOT NULL ,
	Value	INT	NOT NULL
);

CREATE INDEX
	IX_TestTable_Value
ON
	dbo.TestTable (Value ASC);

Since the table is empty, SQL Server creates the statistics object, but the histogram is empty.

This is expected.

Now insert data:

INSERT INTO
	dbo.TestTable
SELECT
	Id		= ROW_NUMBER () OVER (ORDER BY (SELECT NULL) ASC) ,
	Value	= ABS (CHECKSUM (NEWID ()))
FROM
	GENERATE_SERIES (1 , 100000 , 1);

Now inspect the statistics metadata:

SELECT
	StatsId				= Stats.stats_id ,
	StatsName			= Stats.name ,
	LastUpdated			= StatsProperties.last_updated ,
	NumberOfRows		= StatsProperties.rows ,
	ModificationCounter	= StatsProperties.modification_counter
FROM
	sys.stats AS Stats
CROSS APPLY
	sys.dm_db_stats_properties (Stats.object_id , Stats.stats_id) AS StatsProperties
WHERE
	Stats.object_id = OBJECT_ID(N'dbo.TestTable');

You might expect to see a modification counter reflecting the inserted rows.

Instead, you will see something surprising:

LastUpdated           NULL
NumberOfRows          NULL
ModificationCounter   NULL

The Problem

Because the table was empty when the statistics were created, SQL Server did not create a statistics blob (the internal structure that stores the histogram and density vector).

Without this blob:

last_updated is NULL
rows is NULL
modification_counter is also NULL

And here is the critical point:

The modification counter will remain NULL no matter how many rows are inserted or modified later.

Even if you insert millions or billions of rows, the statistics metadata stays NULL.

The statistics only become initialized after the first statistics update.

For example:

UPDATE STATISTICS
	dbo.TestTable IX_TestTable_Value;

After this command, the DMV suddenly reports valid metadata and a histogram is created.

Why This Matters

Many DBAs disable AUTO_UPDATE_STATISTICS and instead rely on scheduled maintenance jobs.

A very common example is Ola Hallengren’s IndexOptimize procedure, which updates statistics based on the modification counter.

Internally, it relies on:

sys.dm_db_stats_properties.modification_counter

But since the value is NULL, the script effectively treats it as 0.

Which means:

modification_counter < threshold

So the statistics never qualify for an update.

The result:

The statistics object exists
The histogram does not
The metadata remains NULL
The maintenance job never updates it

And the statistics can remain in this state indefinitely.

Why Auto-Update Statistics Usually Masks the Problem

If AUTO_UPDATE_STATISTICS is enabled, SQL Server will eventually create the histogram when a query compilation detects stale statistics.

This means the issue usually resolves itself automatically.

However, if a DBA disables auto-update statistics and relies solely on scheduled maintenance based on the modification counter, the statistics may never be initialized.

Detecting the Problem

You can detect such statistics easily:

SELECT
	SchemaName	= SCHEMA_NAME (Tables.schema_id) ,
	TableName	= Tables.name ,
	StatsName	= Stats.name ,
	LastUpdated	= StatsProperties.last_updated
FROM
	sys.tables AS Tables
INNER JOIN
	sys.stats AS Stats
ON
	Tables.object_id = Stats.object_id
OUTER APPLY
	sys.dm_db_stats_properties (Stats.object_id , Stats.stats_id) AS StatsProperties
WHERE
	StatsProperties.last_updated IS NULL;

These statistics objects exist but have never been built.