What is Hypothetical Index and how to delete it

Hi Folks,

In this post I’m going to tell you what is Hypothetical Index and how to remove them :

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

To delete these type of indexes use below command :

USE <My Database>

DROP INDEX  <Schema_Name> . <Table_Name> . <Index_Name>

Cheers :
Gaurav Shrivastav

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: