Oldest Statistics in SQL Server: Find the Stale Ones First

To find the oldest statistics in SQL Server, read two numbers for each one. Read when it was last updated and how many rows changed since. Age alone misleads. A statistic on a quiet table stays correct for years. A young statistic on a busy table can be wrong by lunchtime.

Why Age Is Only Half the Answer

A statistic is a small summary of a column. The optimizer reads it to guess how many rows a query returns. The guess goes wrong only when the data has moved away from the summary. A table that never changes can hold some of the oldest statistics in SQL Server, and that is fine. I leave them alone.

That is why a list sorted only by date sends you after the wrong objects. The query below adds the changes since the last update. It also adds a percentage, and the point where SQL Server would update the statistic by itself.

The demo database is StatsAgeDemo. It holds a jar table with an indexed view, and an orders table in which 4,000 of 10,000 rows change. The script waits two seconds, so the two groups get different update times. Run it on a test server.

IF DB_ID(N'StatsAgeDemo') IS NULL CREATE DATABASE StatsAgeDemo;
GO
USE StatsAgeDemo;
GO
DROP VIEW IF EXISTS dbo.JarFlavorCounts;
DROP TABLE IF EXISTS dbo.JarBatches;
DROP TABLE IF EXISTS dbo.PantryOrders;
CREATE TABLE dbo.JarBatches (
    BatchID int NOT NULL CONSTRAINT PK_JarBatches PRIMARY KEY,
    Flavor  varchar(20) NOT NULL
);
INSERT INTO dbo.JarBatches (BatchID, Flavor)
SELECT n, CHOOSE(n % 4 + 1, 'Plum', 'Apricot', 'Fig', 'Quince')
FROM (SELECT TOP (4000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
CREATE INDEX IX_JarBatches_Flavor ON dbo.JarBatches (Flavor);
GO
CREATE VIEW dbo.JarFlavorCounts WITH SCHEMABINDING AS
SELECT Flavor, COUNT_BIG(*) AS Jars FROM dbo.JarBatches GROUP BY Flavor;
GO
CREATE UNIQUE CLUSTERED INDEX IX_JarFlavorCounts ON dbo.JarFlavorCounts (Flavor);
GO
WAITFOR DELAY '00:00:02';
CREATE TABLE dbo.PantryOrders (
    OrderID int NOT NULL CONSTRAINT PK_PantryOrders PRIMARY KEY,
    Status  varchar(10) NOT NULL
);
INSERT INTO dbo.PantryOrders (OrderID, Status)
SELECT n, 'Open'
FROM (SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
CREATE INDEX IX_PantryOrders_Status ON dbo.PantryOrders (Status);
GO
UPDATE dbo.PantryOrders SET Status = 'Shipped' WHERE OrderID <= 4000;

The Oldest Statistics Query

The query finds the oldest statistics in SQL Server on user tables and indexed views, oldest first. sys.dm_db_stats_properties supplies the update time, the row count and the modification counter. The last column applies the automatic update rule for compatibility level 130 and higher. The rule takes the smaller of two numbers. One is 500 plus 20 percent of the rows. The other is the square root of 1,000 times the rows. A table of 500 rows or fewer always waits for 500 changes.

SELECT OBJECT_NAME(s.object_id) AS ObjectName,
       s.name AS StatisticName,
       DATEDIFF(day, sp.last_updated, SYSDATETIME()) AS DaysOld,
       sp.rows AS TableRows,
       sp.modification_counter AS Changes,
       CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS decimal(7,2)) AS PercentChanged,
       CAST(CASE WHEN sp.rows <= 500 THEN 500
                 WHEN 500 + 0.20 * sp.rows < SQRT(1000.0 * sp.rows) THEN 500 + 0.20 * sp.rows
                 ELSE SQRT(1000.0 * sp.rows) END AS int) AS AutoUpdateAt
FROM sys.stats AS s
JOIN sys.objects AS o ON o.object_id = s.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE o.is_ms_shipped = 0 AND o.type IN ('U', 'V')
ORDER BY sp.last_updated, s.name;

How to Read the Result

The first row has NULL everywhere. That statistic was created with the empty table, and nothing has filled it since. A NULL update time is the oldest case there is, so the ORDER BY puts it first.

DaysOld reads 0 for the rest, because the demo was created seconds ago. On a real database this column carries the story. The stat objects of an old, quiet table sit at the top with large values and no changes. Those need no action.

The row to watch is IX_PantryOrders_Status. It has 4,000 changes on 10,000 rows, so 40 percent of the table moved. That answers the question readers ask: how high is a high modification counter? Compare it with the row count, as the PercentChanged column does, and with AutoUpdateAt. Here 4,000 is above 2,500.

The indexed view appears as well, with its own statistic of 4 rows. The o.type IN ('U', 'V') filter is what lets indexed views into the list.

A statistic that was created with NORECOMPUTE never reaches that point. The post about the NORECOMPUTE flag, linked below, shows the query that finds them.

A High Counter Is Not Always a Problem

SQL Server checks a statistic when a query that needs it compiles. If the counter is past the update point, it refreshes the statistic first. Nobody has queried PantryOrders since the update, so the counter only grew. This query needs the statistic on Status.

SELECT MAX(OrderID) AS LastOpen FROM dbo.PantryOrders WHERE Status = 'Open';

Run the oldest statistics query again. The row for IX_PantryOrders_Status now shows 0 changes. The automatic update ran the moment a query asked for the statistic. So a large counter on a rarely used table is harmless. On a table that queries hit all day, a counter past the update point is cleared at the next compile. A counter below the update point stays, and the plans use those numbers.

When to Update by Hand

You could argue that a nightly update of every statistic is the safe habit. It is safe, but it is not free. Each update reads the table, and queries that use an updated statistic can recompile. A table with no changes gains nothing from the work.

Update by hand after a load that changes a large part of a table, and before the next query runs. Name the statistic and use a full scan when the table is small enough to afford it. For tables that are frozen by design, see Statistics That Never Auto Update: Find the NORECOMPUTE Flag. To learn what triggers an update, read When Are Statistics Updated? What Triggers an Automatic Update.

What to Remember

Sort by age, then filter by change. The oldest statistics in SQL Server matter only when the percent changed is high and the table is used. Treat a NULL update time as a statistic that was never filled.

The query reads one database at a time, so run it in each database you care about. The statistics function needs SELECT permission on the statistics columns, ownership of the table, or a role such as db_owner. Add a filter on PercentChanged when the list gets long.

When you finish the demo, drop the database.

USE master;
GO
DROP DATABASE StatsAgeDemo;

A statistic is not stale because it is old, it is stale because the data moved on.

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