SQL SERVER – How to Drop All the Hypothetical Indexes of Database?

Earlier I wrote a blog post SQL SERVER – Say No To Database Engine Tuning Advisor and I got an email from my existing client where I frequently engage for Comprehensive Database Performance Health Check. They wanted me to help them remove all the negative impact of the Database Engine Tuning Advisor (DTA). I was able to help them achieve this by removing the Hypothetical Indexes they had added using the DTA.

Before you continue reading this blog post, I suggest that you read this blog post SQL SERVER – What is Hypothetical Indexes? to understand what actually is Hypothetical Indexes.

In simple words, Hypothetical Index is a kind of index which contains no data but only statistics information about the columns specified in the definition of the index. Database Engine Tuning Advisor has one of the habits to suggest so many of such indexes which just creates the statistics and no real data. As the workload changes for your organizations, the need of such statistics also changes, however, as a user we never end up dropping those indexes build a clutter in our database which takes our maintenance tasks to take a longer period of time.

The best thing is to do identify all the Hypothetical Indexes and drop them. Let us see a quick script about how to identify them.

SELECT *
FROM sys.indexes
WHERE is_hypothetical = 1

Here is another script which you can use to drop all such indexes in your database.

SELECT 'DROP INDEX ' + QUOTENAME(i.name)  + ' ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) +'.'+ QUOTENAME(OBJECT_NAME(i.object_id)) + ';'
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE i.is_hypothetical = 1

Reference: Pinal Dave (https://blog.sqlauthority.com)