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.  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