sp_updatestats and Disabled Indexes: What Gets Updated

With sp_updatestats and disabled indexes, the procedure still updates the statistics of disabled nonclustered indexes. It skips a table only when its clustered index is disabled. So a disabled index keeps a small cost. It is one more reason to drop an index you no longer need.

Disable or Drop an Unused Index

When an index is unused, teams ask whether to disable it or drop it. Disabling feels safer. The definition stays, and a rebuild brings the index back. Dropping returns the space at once and removes every cost of the index. Both are fine steps in order: disable, wait, then drop. Disabling an index on purpose for a bulk load, and rebuilding it afterwards, is a different case. There the extra cost lasts only for the load.

The question is what a disabled index still costs. The space is free, because the data is gone. The statistics are not. They stay, and the standard update procedure refreshes them.

Build the Demo

The demo database is UpdStatsDemo. It holds a table of 1,000,000 rows with the primary key and five nonclustered indexes. A second, small table has a clustered index that we disable later. Run it on a test server. Building the table takes a few seconds.

IF DB_ID(N'UpdStatsDemo') IS NULL CREATE DATABASE UpdStatsDemo;
GO
USE UpdStatsDemo;
GO
DROP TABLE IF EXISTS dbo.PotOrders;
DROP TABLE IF EXISTS dbo.ClusteredOff;
CREATE TABLE dbo.PotOrders (
    OrderID int NOT NULL CONSTRAINT PK_PotOrders PRIMARY KEY,
    A int NOT NULL, B int NOT NULL, C int NOT NULL, D int NOT NULL, E int NOT NULL
);
INSERT INTO dbo.PotOrders (OrderID, A, B, C, D, E)
SELECT n, n % 1000, n % 997, n % 991, n % 983, n % 977
FROM (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b CROSS JOIN sys.all_objects AS c) AS x;
CREATE INDEX IX_A ON dbo.PotOrders (A);
CREATE INDEX IX_B ON dbo.PotOrders (B);
CREATE INDEX IX_C ON dbo.PotOrders (C);
CREATE INDEX IX_D ON dbo.PotOrders (D);
CREATE INDEX IX_E ON dbo.PotOrders (E);
CREATE TABLE dbo.ClusteredOff (ID int NOT NULL, V int NOT NULL);
CREATE CLUSTERED INDEX CX_ClusteredOff ON dbo.ClusteredOff (ID);
CREATE INDEX IX_ClusteredOff_V ON dbo.ClusteredOff (V);
INSERT INTO dbo.ClusteredOff (ID, V)
SELECT n, n FROM (SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects) AS x;

Prove It on Disabled Nonclustered Indexes

The first step updates 20 percent of the big table and disables two of its indexes. A report then lists each statistic with its state and its change counter. Next, sp_updatestats runs, and the same report runs again. The statistics created by the load carry no changes yet, so a first run settles them.

EXEC sp_updatestats;
GO
UPDATE dbo.PotOrders SET A = A + 1, B = B + 1, C = C + 1, D = D + 1, E = E + 1 WHERE OrderID % 5 = 0;
ALTER INDEX IX_B ON dbo.PotOrders DISABLE;
ALTER INDEX IX_C ON dbo.PotOrders DISABLE;
GO
SELECT s.name AS StatisticName, i.is_disabled AS IsDisabled, sp.modification_counter AS ChangesBefore
FROM sys.stats AS s
JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.stats_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.PotOrders')
ORDER BY s.name;
GO
EXEC sp_updatestats;
GO
SELECT s.name AS StatisticName, i.is_disabled AS IsDisabled, sp.modification_counter AS ChangesAfter
FROM sys.stats AS s
JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.stats_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.PotOrders')
ORDER BY s.name;

The procedure prints a section for every table of the database, and system tables are in the list. That makes the output long. The part for the demo table reads like this. The statistics of the two disabled indexes were updated.

Updating [dbo].[PotOrders]
    [PK_PotOrders], update is not necessary...
    [IX_A] has been updated...
    [IX_B] has been updated...
    [IX_C] has been updated...
    [IX_D] has been updated...
    [IX_E] has been updated...
    5 index(es)/statistic(s) have been updated, 1 did not require update.

Both disabled indexes got a fresh statistic, like the enabled ones. The primary key was left alone, because its key column never changed.

The Clustered Case

A disabled clustered index makes the table unreadable, and it disables the nonclustered indexes with it. In that case, sp_updatestats skips the table. The next batch disables the clustered index of the small table and changes 500 rows first.

UPDATE dbo.ClusteredOff SET V = V + 1 WHERE ID <= 500;
ALTER INDEX CX_ClusteredOff ON dbo.ClusteredOff DISABLE;
GO
EXEC sp_updatestats;
GO
SELECT s.name AS StatisticName, i.is_disabled AS IsDisabled, sp.modification_counter AS Changes
FROM sys.stats AS s
JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.stats_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.ClusteredOff')
ORDER BY s.name;

The procedure prints this line for the table and moves on to the next one.

Table [dbo].[ClusteredOff]: cannot perform the operation on the table because its clustered index is disabled.

The counter of IX_ClusteredOff_V is still 500. Nothing was updated.

What the Disabled Indexes Cost

Now the timing of sp_updatestats and disabled indexes. The script below rebuilds all indexes, so that all five are enabled, changes 200,000 rows and times sp_updatestats. Then it changes the rows again, disables four indexes and times it a second time. Last, it drops the same four indexes, changes the rows once more and times it a third time.

DECLARE @t0 datetime2, @enabledMs int, @disabledMs int, @droppedMs int;
ALTER INDEX ALL ON dbo.PotOrders REBUILD;
UPDATE dbo.PotOrders SET A = A + 1, B = B + 1, C = C + 1, D = D + 1, E = E + 1 WHERE OrderID % 5 = 0;
SET @t0 = SYSDATETIME();
EXEC sp_updatestats;
SET @enabledMs = DATEDIFF(millisecond, @t0, SYSDATETIME());
UPDATE dbo.PotOrders SET A = A + 1, B = B + 1, C = C + 1, D = D + 1, E = E + 1 WHERE OrderID % 5 = 0;
ALTER INDEX IX_B ON dbo.PotOrders DISABLE;
ALTER INDEX IX_C ON dbo.PotOrders DISABLE;
ALTER INDEX IX_D ON dbo.PotOrders DISABLE;
ALTER INDEX IX_E ON dbo.PotOrders DISABLE;
SET @t0 = SYSDATETIME();
EXEC sp_updatestats;
SET @disabledMs = DATEDIFF(millisecond, @t0, SYSDATETIME());
DROP INDEX IX_B ON dbo.PotOrders;
DROP INDEX IX_C ON dbo.PotOrders;
DROP INDEX IX_D ON dbo.PotOrders;
DROP INDEX IX_E ON dbo.PotOrders;
UPDATE dbo.PotOrders SET A = A + 1, B = B + 1, C = C + 1, D = D + 1, E = E + 1 WHERE OrderID % 5 = 0;
SET @t0 = SYSDATETIME();
EXEC sp_updatestats;
SET @droppedMs = DATEDIFF(millisecond, @t0, SYSDATETIME());
SELECT @enabledMs AS FiveEnabledMs, @disabledMs AS FourDisabledMs, @droppedMs AS FourDroppedMs;

The script prints three timings. Earlier runs gave the ranges in the table. The time with four disabled indexes is close to the time with all five enabled. It is two to seven times the time after dropping them. Your numbers will differ with the hardware and the load. In every run here, the four disabled indexes cost far more than dropped ones.

The cost appears only for statistics whose columns changed. A disabled index on a column that never changes is skipped with update is not necessary.

A Targeted Update

You could argue that a nightly sp_updatestats is the simple, safe choice. It is, until a database carries many disabled or unused indexes. Then write a small solution that picks the statistics that need work. This query builds one UPDATE STATISTICS command for each changed statistic that does not sit on a disabled index. It prints the commands and runs nothing. The block first recreates two of the dropped indexes.

CREATE INDEX IX_B ON dbo.PotOrders (B);
CREATE INDEX IX_C ON dbo.PotOrders (C);
UPDATE dbo.PotOrders SET B = B + 1, C = C + 1 WHERE OrderID % 10 = 0;
ALTER INDEX IX_C ON dbo.PotOrders DISABLE;
GO
SELECT N'UPDATE STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(s.object_id))
       + N' ' + QUOTENAME(s.name) + N';' AS Command
FROM sys.stats AS s
JOIN sys.objects AS o ON o.object_id = s.object_id AND o.is_ms_shipped = 0 AND o.type = 'U'
LEFT JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.stats_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE sp.modification_counter > 0 AND ISNULL(i.is_disabled, 0) = 0
ORDER BY s.name;

IX_C also changed, but it is disabled, so the query leaves it out. Review the list, run the commands you accept, and drop the disabled indexes when you are sure of them. Use Unused Index Script: Find Indexes That Only Cost You Writes to find candidates. Use Oldest Statistics in SQL Server: Find the Stale Ones First to see which statistics are due.

What to Remember

Disable an unused index only as a short stop on the way to dropping it. Check the cost on your own server with the first demo blocks before you change a schedule. With sp_updatestats and disabled indexes, the statistics of a disabled nonclustered index are still updated, and the time adds up. Dropping also gives the space back.

When you finish the demo, drop the database.

USE master;
GO
DROP DATABASE UpdStatsDemo;

A disabled index is not a free index, it is an index you keep paying to describe.

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