Index Tuning Wizard Left a Mess Behind

Blitz Result: Index Tuning Wizard Left a Mess Behind

The Index Tuning Wizard and Database Tuning Advisor help you improve query performance by adding more indexes. (Whether or not they’re good tools is a separate discussion, and we’ll set that aside for now.)

These tools work by creating fake indexes, then getting a new execution plan for your query. Unfortunately, if these tools crash, they leave a bunch of these fake indexes behind. This part of our SQL Server sp_Blitz script checks sys.indexes looking for indexes with is_hypothetical = 1.

Hypothetical indexes may not be a big performance problem on the surface, but if you’re doing index tuning, and you’re checking to see whether an index already exists, you might get fooled by hypothetical indexes.

To Fix the Problem

Simply drop this index – it’s not helping anything.

Here’s a script to help you find hypothetical indexes and statistics to drop:

WITH hi AS (
SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table] , QUOTENAME([i].[name]) AS [Index_or_Statistics], 1 AS [Type]
FROM sys.[indexes] AS [i]
JOIN sys.[objects] AS [o]
ON i.[object_id] = o.[object_id]
WHERE 1=1 
AND INDEXPROPERTY(i.[object_id], i.[name], 'IsHypothetical') = 1
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1

UNION ALL

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(o.[object_id])) AS [Table], QUOTENAME([s].[name]) AS [Index_or_Statistics], 2 AS [Type]
FROM sys.[stats] AS [s]
JOIN sys.[objects] AS [o]
ON [o].[object_id] = [s].[object_id]
WHERE [s].[user_created] = 0
AND [o].[name] LIKE '[_]dta[_]%'
AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1
)
SELECT [hi].[Table] ,
       [hi].[Index_or_Statistics] ,
       CASE [hi].[Type] 
       WHEN 1 THEN 'DROP INDEX ' + [hi].[Index_or_Statistics] + ' ON ' + [hi].[Table] + ';'
       WHEN 2 THEN 'DROP STATISTICS ' + hi.[Table] + '.' + hi.[Index_or_Statistics] + ';'
       ELSE 'DEAR GOD WHAT HAVE YOU DONE?'
       END AS [T-SQL Drop Command]
FROM [hi]

WITH hi AS (

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(i.[object_id])) AS [Table] , QUOTENAME([i].[name]) AS [Index_or_Statistics], 1 AS [Type]

FROM sys.[indexes] AS [i]

JOIN sys.[objects] AS [o]

ON i.[object_id] = o.[object_id]

WHERE 1=1

AND INDEXPROPERTY(i.[object_id], i.[name], 'IsHypothetical') = 1

AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1

UNION ALL

SELECT QUOTENAME(SCHEMA_NAME(o.[schema_id])) +'.'+ QUOTENAME(OBJECT_NAME(o.[object_id])) AS [Table], QUOTENAME([s].[name]) AS [Index_or_Statistics], 2 AS [Type]

FROM sys.[stats] AS [s]

JOIN sys.[objects] AS [o]

ON [o].[object_id] = [s].[object_id]

WHERE [s].[user_created] = 0

AND [o].[name] LIKE '[_]dta[_]%'

AND OBJECTPROPERTY([o].[object_id], 'IsUserTable') = 1

)

SELECT [hi].[Table] ,

[hi].[Index_or_Statistics] ,

CASE [hi].[Type]

WHEN 1 THEN 'DROP INDEX ' + [hi].[Index_or_Statistics] + ' ON ' + [hi].[Table] + ';'

WHEN 2 THEN 'DROP STATISTICS ' + hi.[Table] + '.' + hi.[Index_or_Statistics] + ';'

ELSE 'DEAR GOD WHAT HAVE YOU DONE?'

END AS [T-SQL Drop Command]

FROM [hi]

If you’re feeling ambitious, you can also check for hypothetical statistics, partition functions, and partition schemes.

Return to sp_Blitz or Ask Us Questions