Hypothetical Indexes in SQL Server: Find and Drop Them All

Hypothetical indexes are leftovers from the Database Engine Tuning Advisor, and one script finds and drops every one of them. They hold no data. They still clutter every list of indexes.

What a Hypothetical Index Is

A hypothetical index is an index definition plus statistics, with no data behind it. The Database Engine Tuning Advisor creates them while it tests what-if choices. It removes them when a session ends normally. A stopped or crashed session can leave them behind.

The advisor names its objects with the prefix _dta_index. A name is not proof, though. Recommendations that you apply create real indexes with the same prefix. The catalog flag is_hypothetical is the reliable test, so the scripts below use it.

The advisor is a tool, so the demo makes the same kind of object by hand. The option STATISTICS_ONLY = -1 is undocumented, and the advisor relies on it. Use it only on a test server, and only to build clutter you want to clean. The script also adds one statistic named like the ones the advisor leaves.

IF DB_ID(N'HypotheticalIndexDemo') IS NULL CREATE DATABASE HypotheticalIndexDemo;
GO
USE HypotheticalIndexDemo;
GO
DROP TABLE IF EXISTS dbo.Seeds;
CREATE TABLE dbo.Seeds (
    SeedID  int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Seeds PRIMARY KEY,
    Variety nvarchar(40) NOT NULL,
    Packet  int          NOT NULL,
    Price   decimal(6,2) NOT NULL
);
INSERT INTO dbo.Seeds (Variety, Packet, Price) VALUES (N'Basil', 10, 3.50), (N'Mint', 20, 4.00), (N'Sage', 15, 3.00);
CREATE INDEX IX_Seeds_Real_Variety ON dbo.Seeds (Variety);
CREATE INDEX _dta_index_Seeds_5_1 ON dbo.Seeds (Packet) WITH STATISTICS_ONLY = -1;
CREATE INDEX _dta_index_Seeds_5_2 ON dbo.Seeds (Price, Packet) WITH STATISTICS_ONLY = -1;
CREATE STATISTICS _dta_stat_Seeds_5_3 ON dbo.Seeds (Variety, Packet);

List the Hypothetical Indexes

The listing query reads every user table and shows how many pages each index uses. A real index has pages. A hypothetical one has none, because nothing is stored. Add WHERE i.is_hypothetical = 1 to see only the leftovers. These scripts work on the current database, so run them in each database you tune.

SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS TableName,
       i.name AS IndexName,
       i.is_hypothetical AS IsHypothetical,
       ISNULL(SUM(ps.used_page_count), 0) AS PagesUsed
FROM sys.indexes AS i
JOIN sys.objects AS o ON o.object_id = i.object_id AND o.is_ms_shipped = 0 AND o.type = 'U'
LEFT JOIN sys.dm_db_partition_stats AS ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE i.index_id > 0
GROUP BY o.schema_id, o.name, i.name, i.is_hypothetical
ORDER BY TableName, IndexName;

The two hypothetical indexes use zero pages. Inserts, updates and deletes have nothing to maintain in them.

They Cannot Serve a Query

A hypothetical index cannot answer a query. Force it with a hint and SQL Server acts as if the index does not exist.

SELECT SeedID FROM dbo.Seeds WITH (INDEX(_dta_index_Seeds_5_1)) WHERE Packet = 10;

The message reads as follows.

Msg 308, Level 16, State 1, Line 1
Index '_dta_index_Seeds_5_1' on table 'dbo.Seeds' (specified in the FROM clause) does not exist.

So dropping hypothetical indexes removes no access path from any plan. The statistics are another matter. The optimizer reads statistics for its row estimates, and an index brings its statistics along. A plan can change when they go. Test your most important queries before you drop on a production server.

Drop Them in Two Steps

The first script builds one DROP INDEX statement per hypothetical index and stores the statements in a temporary table. It then lists them. Nothing is dropped yet. Read the list. On a real server, it should hold only names you recognize.

DROP TABLE IF EXISTS #DropPlan;
SELECT N'DROP INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + N';' AS Statement
INTO #DropPlan
FROM sys.indexes AS i
JOIN sys.objects AS o ON o.object_id = i.object_id
WHERE i.is_hypothetical = 1 AND o.is_ms_shipped = 0;

SELECT Statement FROM #DropPlan ORDER BY Statement;

The second script is the drop. It runs only the statements in the list, in the same window. The IF keeps it quiet when the list is empty. Dropping an index needs ALTER permission on the table.

DECLARE @drops nvarchar(max) = (SELECT STRING_AGG(CONVERT(nvarchar(max), Statement), NCHAR(10)) FROM #DropPlan);
IF @drops IS NULL
    PRINT N'Nothing to drop.';
ELSE
    EXEC (@drops);

Dropping a hypothetical index also removes its statistics. The statistics the advisor creates on their own stay behind. They are ordinary user statistics, named with the prefix _dta_stat. Applied recommendations can leave statistics with the same prefix, and the optimizer can use them. So the statistics get the same two steps. List them first, test your main queries, and drop them second.

DROP TABLE IF EXISTS #StatPlan;
SELECT N'DROP STATISTICS ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) + N'.' + QUOTENAME(s.name) + N';' AS Statement
INTO #StatPlan
FROM sys.stats AS s
JOIN sys.objects AS o ON o.object_id = s.object_id
WHERE s.user_created = 1 AND s.name LIKE N'[_]dta[_]stat[_]%' AND o.is_ms_shipped = 0;

SELECT Statement FROM #StatPlan ORDER BY Statement;

DECLARE @drops nvarchar(max) = (SELECT STRING_AGG(CONVERT(nvarchar(max), Statement), NCHAR(10)) FROM #StatPlan);
IF @drops IS NULL
    PRINT N'Nothing to drop.';
ELSE
    EXEC (@drops);

Run the check once more.

SELECT COUNT(*) AS HypotheticalIndexesLeft FROM sys.indexes WHERE is_hypothetical = 1;

It returns 0. The real index and the primary key are still in place.

Real indexes with the same prefix are a different job. They hold data and cost writes. List them with their reads and their columns before you decide. The Index Key Columns and Included Columns post builds that list.

Is It Worth the Trouble?

A reviewer pays for these objects in attention. They show up in index reviews as indexes with zero reads, and someone has to explain each one. A script that flags unused indexes lists them first.

The advisor statistics cost a little work as well. Automatic updates refresh them like any other user statistic. The demo does not test whether SQL Server refreshes the statistics of a hypothetical index itself. Fewer objects mean a shorter list when you review a table. Better still, run the advisor against a restored copy. Then any leftover lives in a database you can throw away.

You could argue that hypothetical indexes are harmless, because they take no space. That is true for disk. It is not true for the person who reads the list. A clean list holds only the indexes that exist.

What to Remember

Find the leftovers with is_hypothetical = 1. List the drop statements, read them, and run them as a second step. Drop the advisor statistics as a second step. A hypothetical index serves no query. The risk sits in the statistics, and a test of your main queries covers it.

After every tuning session, check for hypothetical indexes before you close the ticket. The check takes a second. When you finish with the demo, run the cleanup script.

USE master;
GO
IF DB_ID(N'HypotheticalIndexDemo') IS NOT NULL
BEGIN
    ALTER DATABASE HypotheticalIndexDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE HypotheticalIndexDemo;
END;

A hypothetical index is not an asset, it is a note nobody threw away.

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