Hypothetical Indexes

When Database Engine Tuning Advisor runs on a database, it makes some recommendations. For all the recommendations it actually creates the objects on the database. The recommendations can be Indexes, Stats etc. They are called Hypothetical Indexes/Statistics.

If Database Engine Tuning Advisor is closed normally, it ensures that all the objects that it had created are cleaned up. If for some reason, it does not exit normally, then these objects will still remain on the database.

Since these objects are hypothetical, they can be removed manually. These objects’ name will start with “_dta”. They can also be identified with the below query.

SELECT * FROM sys.indexes WHERE is_hypothetical = 1

Sometimes, we may want to retain the objects created by Database Engine Tuning Advisor. Then ensure to rename those objects properly, so that the clean up of obsolete objects created when DTA runs next time, becomes easy !