Disabled Indexes

What this check looks for

Indexes with is_disabled = 1 in sys.indexes, across every database on the instance. The message names the database and the object.

Why it matters

A disabled index is the worst of both states. It does nothing for reads, and it still exists to be maintained in the catalog and in your thinking.

When a non clustered index is disabled:

• Its data is dropped. The index pages are deallocated, so it holds nothing.
• The definition remains, so it appears in every index listing, every script, every review.
• Queries that would have used it get a different plan, usually a scan. This is the real cost, and it is invisible: nobody gets an error, the query simply becomes slower.
• It is not maintained, so nothing about it degrades further, but nothing improves either.

When a clustered index is disabled, the situation is entirely different and much more serious. Disabling a clustered index makes the whole table inaccessible. Every query against it fails with an error saying the index is disabled. Every non clustered index on that table is disabled implicitly as well. If this check reports a disabled clustered index, that table is effectively offline and it should be treated as an outage.

How indexes end up disabled:

• Deliberately, before a bulk load. Disabling a non clustered index and rebuilding it afterwards is faster than maintaining it during the load. This is legitimate, and the finding is the rebuild that never happened, usually because the load failed partway or the script had no error handling.
• As a soft delete. Somebody wanted to test whether an index was needed and disabled it rather than dropping it, intending to re-enable it if anything broke. Then nothing broke loudly enough, and it stayed.
• By a failed online rebuild. An online index rebuild that is interrupted can leave the index disabled in some circumstances.
• From a restore or a migration that carried the state across.

The soft delete case is worth naming, because it is a reasonable instinct implemented badly. If the intent is to test removing an index, the right approach is to script the index definition somewhere durable and drop it, rather than leaving a disabled skeleton that confuses the next person and still occupies a name.

How to confirm it yourself

Run this in each database:

SELECT SCHEMA_NAME(o.[schema_id])          AS [schema_name],
       o.[name]                            AS [table_name],
       i.[name]                            AS [index_name],
       i.[type_desc],
       i.[is_disabled],
       i.[is_unique],
       i.[is_primary_key],
       i.[is_unique_constraint]
  FROM sys.indexes      AS i WITH (NOLOCK)
 INNER JOIN sys.objects AS o WITH (NOLOCK) ON o.[object_id] = i.[object_id]
 WHERE i.[is_disabled] = 1
   AND o.[is_ms_shipped] = 0
 ORDER BY i.[type_desc], [table_name];

Read type_desc first. A CLUSTERED row is an inaccessible table and is urgent; a NONCLUSTERED row is a cleanup item.

Every database at once:

EXEC sp_MSforeachdb N'
USE [?];
IF DB_ID() > 4
SELECT DB_NAME() AS [database_name],
       OBJECT_SCHEMA_NAME(i.[object_id]) AS [schema_name],
       OBJECT_NAME(i.[object_id])        AS [table_name],
       i.[name]                          AS [index_name],
       i.[type_desc]
  FROM sys.indexes AS i WITH (NOLOCK)
 WHERE i.[is_disabled] = 1;';

Whether it would be used if it were enabled, which decides between rebuilding and dropping:

SELECT OBJECT_NAME(s.[object_id]) AS [table_name],
       i.[name]                   AS [index_name],
       s.[user_seeks], s.[user_scans], s.[user_lookups], s.[user_updates],
       s.[last_user_seek], s.[last_user_scan]
  FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
 INNER JOIN sys.indexes AS i WITH (NOLOCK) ON i.[object_id] = s.[object_id]
                                          AND i.[index_id]  = s.[index_id]
 WHERE s.[database_id] = DB_ID()
 ORDER BY s.[user_seeks] + s.[user_scans] DESC;

A disabled index has no usage by definition, so this is really about the surrounding indexes: if another index covers the same leading columns, the disabled one may be genuinely redundant.

And script the definition before you do anything, because a dropped index is gone and a disabled one at least still tells you what it was:

SELECT i.[name] AS [index_name],
       STUFF((SELECT ', ' + QUOTENAME(c.[name]) +
                    CASE WHEN ic.[is_descending_key] = 1 THEN ' DESC' ELSE '' END
               FROM sys.index_columns AS ic
              INNER JOIN sys.columns  AS c ON c.[object_id] = ic.[object_id]
                                          AND c.[column_id] = ic.[column_id]
              WHERE ic.[object_id] = i.[object_id] AND ic.[index_id] = i.[index_id]
                AND ic.[is_included_column] = 0
              ORDER BY ic.[key_ordinal]
                FOR XML PATH('')), 1, 2, '') AS [key_columns]
  FROM sys.indexes AS i
 WHERE i.[is_disabled] = 1;

How to fix it

Decide per index: rebuild it or drop it. Leaving it disabled is not a third option.

If a clustered index is disabled, rebuild it now. The table is unusable until you do:

ALTER INDEX [PK_Orders] ON [dbo].[Orders] REBUILD;

Rebuilding the clustered index re-enables the table. The non clustered indexes on it stay disabled and need rebuilding separately, so follow with:

ALTER INDEX ALL ON [dbo].[Orders] REBUILD;

For a non clustered index you want back:

ALTER INDEX [IX_Orders_CustomerID] ON [dbo].[Orders]
  REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 4);

The rebuild is the only way to enable it. There is no ALTER INDEX ENABLE; the index has no data, so it has to be built. Plan it like any index build: it reads the whole table, it needs space, and offline it takes a schema modification lock.

For one you have decided is not needed, drop it:

DROP INDEX [IX_Orders_CustomerID] ON [dbo].[Orders];

Script the definition into source control first. Once dropped, the only record of the column order and included columns is what you saved.

How to choose: if the index was disabled deliberately for a load and the load is long since finished, rebuild it. If nobody knows why it is disabled and the table performs acceptably without it, drop it and keep the script. An index that has been disabled for months without anybody noticing a performance problem has already run the experiment.

Then fix the process that left it. If a load script disables indexes, it needs a TRY ... CATCH that rebuilds them on failure as well as on success. An unfinished load is the most common origin of this finding, and it will recur otherwise.

How long it takes

About half an hour to review and decide. The rebuilds run in the normal maintenance window, except a disabled clustered index which should be rebuilt immediately.

Related reports

Related checks

Frequently asked questions

Can I just enable it without a rebuild? No. Disabling drops the index data, so enabling means building it again. ALTER INDEX ... REBUILD is the only path.

Why would anyone disable an index on purpose? To speed up a bulk load. Maintaining a non clustered index during a large insert is expensive, so disabling it and rebuilding afterwards is a legitimate technique. The finding is the rebuild that did not happen.

A clustered index is disabled and queries are failing. That is expected. Disabling a clustered index makes the table inaccessible. Rebuild it, then rebuild the non clustered indexes on the same table.

Is it taking up space? Almost none. The data pages are deallocated when it is disabled, so only the metadata remains. The cost is the queries that would have used it, not the storage.