Should I delete Hypothetical Indexes?

Question

I have noticed that Hypothetical indexes exist in a certain database. I have searched around and it appeared that this type of indexes are created by Tuning Advisor and are not always deleted.

There are several topics including official documentation of how to clear/delete these indexed, but I was not able to find if these indexes have any impact to the server themselves.

What I have check using the script below is that there is no size information about them:

SELECT OBJECT_NAME(I.[object_id]) AS TableName
      ,I.[name] AS IndexName
      ,I.[index_id] AS IndexID
      ,8 * SUM(A.[used_pages]) AS 'Indexsize(KB)'
FROM [sys].[indexes] AS I
INNER JOIN [sys].[partitions] AS P 
    ON P.[object_id] = I.[object_id] 
    AND P.[index_id] = I.[index_id]
INNER JOIN [sys].[allocation_units] AS A 
    ON A.[container_id] = P.[partition_id]
WHERE I.[is_hypothetical] = 1
GROUP BY I.[object_id]
        ,I.[index_id]
        ,I.[name]
ORDER BY 8 * SUM(A.[used_pages]) DESC

and having them, I have decided to check if there are some usage information about them in order to leave these who are often used, but again nothing was return. (I have use the "Existing Indexes Usage Statistics" from this article).

Could anyone tell why keeping these indexes is wrong and if I can define which of them should be kept?

They don't consume any space, except for metadata and should be ignored by the QO except if `DBCC AUTOPILOT` is run. So in theory they shouldn't cause much harm. Why wouldn't you clean them up though? Even if they don't impact SQL Server much their existence may still cause problems with third party scripts for example. — Martin Smith, Jan 14 '14 at 12:02
@MartinSmith Thanks a lot. Just for curiosity, can you provided a example of such problem situation? — gotqn, Jan 14 '14 at 13:50

score 5 · Answer 1 · answered Aug 26 '15 at 20:14

Just USE the database you want to clean and run this:

DECLARE @sql VARCHAR(MAX) = ''

SELECT
    @sql = @sql + 'DROP INDEX [' + i.name + '] ON [dbo].[' + t.name + ']' + CHAR(13) + CHAR(10)
FROM 
    sys.indexes i 
    INNER JOIN sys.tables t 
        ON i.object_id = t.object_id 
WHERE 
    i.is_hypothetical = 1


EXECUTE sp_sqlexec @sql

score 3 · Accepted Answer · answered Jan 14 '14 at 14:06

3

Just delete them, they aren't actually taking up any space or causing any performance hit/benefit at all, but if you're looking at which indexes are defined on a table and forget to exclude hypothetical indexes, it might cause some confusion, also in the unlikely event that you try to create an index with the same name as one of these indexes, it will fail as it already exists.

answered Jan 14 '14 at 14:06

steoleary

8,968
2
33
47

I have delete them, but please note they that when index is dropped a table is locked which caused me a lot of issues. – gotqn Mar 17 '14 at 08:09

score 0 · Answer 3 · edited May 07 '20 at 21:06

0

If you use custom schemas and checked analyzing indexing views, you need some further improvements to the above scripts:

DECLARE     @sql VARCHAR(MAX) = ''

SELECT      @sql = @sql 
            + 'DROP INDEX [' + i.name + ']'
            + 'ON [' + OBJECT_SCHEMA_NAME(t.[object_id]) + '].[' + t.name + ']' 
            + CHAR(13) + CHAR(10)
FROM        sys.indexes i 
INNER JOIN  sys.[all_objects] t 
ON          i.object_id = t.object_id 
WHERE       i.is_hypothetical = 1

PRINT       @sql

EXECUTE     sp_sqlexec @sql

edited May 07 '20 at 21:06

Jeremy Caney

7,102
69
48
77

answered Apr 28 '20 at 11:37

Ognjen K i2i

1

I would never run autmatic scripts while dealing with indexes. – Racer SQL Aug 03 '20 at 13:20

Should I delete Hypothetical Indexes?

3 Answers3