Hypothetical Indexes

11 July,2016 by Jack Vamvas

Question: I was reviewing a list of sql indexes from a database and spotted some indexes prefixed with “_dta_index”. What are they ? How did they get here? And how can I clean them up.

Answer: These are hypothetical indexes created by the Database Tuning Advisor (DTA).

To find the indexes use the following the query

Use MyDB
GO
select * from sys.indexes where is_hypothetical = 1

If DTA is allowed to complete gracefully , the DTA cleans up the indexes. If the DTA crashes such as  shutting  down unexpectedly  then the hypothetical indexes remain in place – under these circumstances , the hypothetical indexes will remain in the metadata files.

There’s a number of good reasons to keep these hypothetical indexes out of the database. Hypothetical indexes add a management overhead . Maintaining strict controls around the usage of DTA – particularly on a Production system is important.  

You may attempt to delete the hypothetical indexes and find you cannot . There are potentially a number of reasons , such as :

1) Filegroup is read only

2) Permission issues – for a DROP INDEX , ALTER permission is required.

Read More

Last time Index was rebuilt (SQL Server DBA)

Manage Transaction Logs during Index Rebuild (SQL Server DBA)


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Hypothetical Indexes


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer