When Are Statistics Updated? What Triggers an Automatic Update

Statistics updated automatically need two things: enough changed rows, and a query that uses the statistic. Most confusion comes from mixing those up with a third event, when a statistic becomes stale.

What a Statistic Is

A statistic is a small summary of a column: how many rows there are, and how the values are spread. The optimizer reads it to guess how many rows a filter returns. Those guesses decide between a seek and a scan, and between join methods.

Statistics don’t change when data changes. SQL Server counts the modifications to the leading column and marks the statistic stale when the count passes a limit. Stale and updated are two separate events.

Each statistic keeps its own change counter, and it counts changes to the statistic’s first column. An index named IX_Orders_CustomerID gets a statistic with the same name. SQL Server also creates single-column statistics by itself when a query filters on a column that has none.

When Are Statistics Updated Automatically

Two things must happen, in this order. First, enough rows change to pass the threshold. Second, a query that uses the statistic is compiled or recompiled. The update runs at that moment, before the query, unless asynchronous updating is on.

The threshold has changed over the years. The old rule had three parts. An empty table updates after its first added row. A table of 500 rows or fewer waits for 500 changes. A larger table waits for 500 changes plus 20 percent of its rows, which is 200,500 changes for 1,000,000 rows.

From level 130 up, large tables use a second limit: the square root of 1,000 times the row count. SQL Server applies the smaller of the two. The formulas cross near 20,000 rows, so the newer limit matters only above that. For 1,000,000 rows, it’s 31,622 changes.

Watch It Happen

The demo uses a database named StatsDemo and a table of 1,000,000 orders with an index on CustomerID. The index comes with a statistic of the same name. A small view reads that statistic’s properties, so each check is one short query. Run it on a test instance.

IF DB_ID(N'StatsDemo') IS NULL CREATE DATABASE StatsDemo;
GO
USE StatsDemo;
GO
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders (
    OrderID    int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerID int           NOT NULL,
    Amount     decimal(10,2) NOT NULL
);
INSERT INTO dbo.Orders (CustomerID, Amount)
SELECT TOP (1000000) (n * 7919) % 50000, 10 + n % 90
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS nums
ORDER BY n;
CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID);
GO
CREATE OR ALTER VIEW dbo.CustomerIndexStats
AS
SELECT sp.rows, sp.rows_sampled, sp.modification_counter, CONVERT(varchar(23), sp.last_updated, 121) AS last_updated
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.Orders') AND s.name = N'IX_Orders_CustomerID';
GO
SELECT name, compatibility_level, is_auto_update_stats_on, is_auto_update_stats_async_on
FROM sys.databases
WHERE name = N'StatsDemo';

Automatic updating is on and the async option is off, which are the defaults. Here is the starting point, with both limits worked out from the row count.

SELECT rows, rows_sampled, modification_counter, last_updated,
       CAST(SQRT(1000.0 * rows) AS int) AS NewLimit,
       CAST(500 + 0.2 * rows AS int) AS OldLimit
FROM dbo.CustomerIndexStats;

Creating the index read every row, so the first statistic is a full scan. Watch last_updated through the next steps. First, add 20,000 orders, which is below the limit, and run a query that uses the index.

INSERT INTO dbo.Orders (CustomerID, Amount)
SELECT TOP (20000) CustomerID, Amount FROM dbo.Orders ORDER BY OrderID;
SELECT OrderID, Amount FROM dbo.Orders WHERE CustomerID = 777;
SELECT rows, rows_sampled, modification_counter, last_updated FROM dbo.CustomerIndexStats;

The counter shows 20,000 and last_updated didn’t move. Now add 20,000 more, which passes the limit, and run no query at all.

INSERT INTO dbo.Orders (CustomerID, Amount)
SELECT TOP (20000) CustomerID, Amount FROM dbo.Orders ORDER BY OrderID;
SELECT rows, rows_sampled, modification_counter, last_updated FROM dbo.CustomerIndexStats;

The statistic is stale, and it is still the old one. Changing the data doesn’t update statistics. Only a compile does. This query filters on CustomerID and returns columns outside the index. The optimizer has a real choice, so it reads the statistic.

SELECT OrderID, Amount FROM dbo.Orders WHERE CustomerID = 778;
SELECT rows, rows_sampled, modification_counter, last_updated FROM dbo.CustomerIndexStats;

The update ran during that compile. The counter is back to 0, and last_updated has a new time. It also sampled only about a third of the 1,040,000 rows. Automatic updates sample, and the share gets smaller as the table grows.

When to Update by Hand

Statistics updated by hand don’t wait for a threshold. I run one after a big load, a big delete or a partition switch. Otherwise the next query runs on old numbers until the threshold passes. WITH FULLSCAN reads every row, which beats a sample when the data is skewed. An index rebuild also updates that index’s statistics with a full scan. A reorganize doesn’t.

UPDATE STATISTICS dbo.Orders IX_Orders_CustomerID WITH FULLSCAN;
SELECT rows, rows_sampled, modification_counter, last_updated FROM dbo.CustomerIndexStats;

Why the Old Rule Hurt Big Tables

Compatibility level 120 uses the old rule. For 1,040,000 rows, it waits for 208,500 changes. This also applies to any database left below level 130 after an upgrade. The next script loads 40,000 rows at level 120 and runs the same kind of query.

ALTER DATABASE StatsDemo SET COMPATIBILITY_LEVEL = 120;
GO
INSERT INTO dbo.Orders (CustomerID, Amount)
SELECT TOP (40000) CustomerID, Amount FROM dbo.Orders ORDER BY OrderID;
SELECT OrderID, Amount FROM dbo.Orders WHERE CustomerID = 780;
SELECT rows, rows_sampled, modification_counter, last_updated FROM dbo.CustomerIndexStats;

Nothing updated, although the same 40,000 changes had passed the newer limit. Now return to level 170 and run one more query.

ALTER DATABASE StatsDemo SET COMPATIBILITY_LEVEL = 170;
GO
SELECT OrderID, Amount FROM dbo.Orders WHERE CustomerID = 781;
SELECT rows, rows_sampled, modification_counter, last_updated FROM dbo.CustomerIndexStats;

Same data, different outcome. The query triggered the update, and the counter is back to 0. Only the rule changed. On big tables, the old rule waited for changes that the newer rule no longer needs.

Asynchronous Updates

By default the query waits while the statistic updates. With AUTO_UPDATE_STATISTICS_ASYNC on, the query runs at once with the old statistic. A background task updates it for the next query. That helps when a long update would stall a busy application. It works only while AUTO_UPDATE_STATISTICS is on. With that option off, nothing updates statistics except you.

ALTER DATABASE StatsDemo SET AUTO_UPDATE_STATISTICS_ASYNC ON;
SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on
FROM sys.databases
WHERE name = N'StatsDemo';

Common Questions

Do inserts count? Yes. Inserts and deletes always raise the counter, so one large load can pass the limit in a single statement. An update raises it only when it changes the statistic’s first column. Should you run sp_updatestats every night? It touches every statistic with at least one change, however small. I would rather target the tables that change the most.

Can you delete the automatically created statistics? You can. SQL Server builds them again the next time a query filters on that column, and that query pays for it. Leave them alone unless you have a specific reason.

A table variable has no column statistics, so the optimizer knows less about its values than about a temp table’s. A read-only database can’t update its stored statistics, so refresh them before you set it to read-only.

What to Remember

Changes make a statistic stale, and a query that uses it makes the update happen. You could argue that a nightly job that updates every statistic makes this knowledge unnecessary. It removes surprises, but it costs reads on big tables and doesn’t help a table that changes heavily between runs.

I keep automatic updates on and add a manual update after loads. If a plan changes right after an update, compare the old and new row estimates. Parameter-driven slowdowns have a different cause, and I cover them in Stored Procedure Optimization Tips That Still Matter. Run the cleanup script when you finish.

USE master;
GO
DROP DATABASE IF EXISTS StatsDemo;

A statistic is not updated when the data changes, it is updated when a query needs it.

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