Hypothetical Indexes – Part 1

I recently came across a production SQL Server with a large amount of hypothetical indexes – nearly 1000.

I’d occasionally seen these before and had read that that they could safely be removed – however, because there was so many of them, I decided to do a bit of research, and that is the subject of this blog post.

So, how do these hypothetical indexes get created and what are they used for?

Well, as it turns out the Database Engine Tuning Advisor is responsible for their creation and should also be responsible for their removal – however, sometimes this does not happen, and the hypothetical index is left behind.

At this point it probably should be cleaned up – and a hypothetical index can be removed just like any other index

In order to see this, I learned how to create one of these manually. Let’s have a look at an example.

In order to demonstrate this I’ll use tempdb.

USE tempdb;
GO

IF OBJECT_ID('dbo.test') IS NOT NULL
  BEGIN	
    DROP TABLE dbo.test;
  END;
GO


CREATE TABLE dbo.test
(
  PKID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_test PRIMARY KEY CLUSTERED,
  COL1 INT NOT NULL DEFAULT(10)
);
GO

INSERT INTO dbo.test DEFAULT VALUES;
GO 20

Note that at this point we have a single table with one clustered index.

I’ll now create two hypothetical indexes

CREATE INDEX TestIndex01 ON dbo.test (COL1) WITH STATISTICS_ONLY = 0;

CREATE INDEX TestIndex02 ON dbo.test (COL1) WITH STATISTICS_ONLY = -1;

Using the undocumented clause will created the index, the parameter 0 or -1 simple indicated is statistics are created the index.
Lets now also create a standard non clustered index

CREATE INDEX TestIndex03 ON dbo.test (COL1);

Now, lets have a look at what indexes we have. We can use the is_hypothetical column of sys.indexes in order to see this

As expected we have

• 2 hypothetical indexes
  • TestIndex01
  • TestIndex02
• 2 other indexes
  • 1 Clustered Index PK_test
  • 1 NonClustered Index TestIndex03

/*
Let's query sys.indexes
*/
SELECT i.is_hypothetical , * FROM sys.indexes as i WHERE OBJECT_NAME(i.[object_id]) = 'test' and i.is_hypothetical = 1;

SELECT i.is_hypothetical , * FROM sys.indexes as i WHERE OBJECT_NAME(i.[object_id]) = 'test' and i.is_hypothetical = 0;

These hypothetical indexes are not used by any of our queries. If you want to use them then before you do you need run some undocumented DBCC commands and pass things like the index and table ids – this is something you only want be doing for curiosity purposes on your local development machine and definitely not in production – we’ll look at how exactly to do this in another blog post

Anyway, back to the issue at hand – how do we prove that these hypothetical indexes can not be used.

Lets first force our query to use our standard indexes.

SELECT * FROM dbo.test T;                                              --optimiaer chose a real index
SELECT * FROM dbo.test T OPTION (TABLE HINT (T, INDEX(PK_test)));      --Real index, OK to be used
SELECT * FROM dbo.test T OPTION (TABLE HINT (T, INDEX(TestIndex03)));  --Real index, OK to be used

And now let’s try a HINT that involves the hypothetical indexes

SELECT * FROM dbo.test T OPTION (TABLE HINT (T, INDEX(TestIndex01)));  --Hypothetical can't be used 

Msg 308, Level 16, State 1, Line 41
Index 'TestIndex01' on table 'dbo.test' (specified in the FROM clause) does not exist.

SELECT * FROM dbo.test T OPTION (TABLE HINT (T, INDEX(TestIndex02)));  --Hypothetical can't be used 

Msg 308, Level 16, State 1, Line 42
Index 'TestIndex02' on table 'dbo.test' (specified in the FROM clause) does not exist.

It’s as though the indexes do not exist, and as far as the optimiser is concerned – they don’t

These indexes can be disabled just like normal indexes.

ALTER INDEX TestIndex03 ON dbo.test DISABLE; 
ALTER INDEX TestIndex01 ON dbo.test DISABLE; 
ALTER INDEX TestIndex02 ON dbo.test DISABLE; 
ALTER INDEX PK_test     ON dbo.test DISABLE;

And re-enabled

ALTER INDEX PK_test     ON dbo.test REBUILD; 
ALTER INDEX TestIndex03 ON dbo.test REBUILD; 
ALTER INDEX TestIndex01 ON dbo.test REBUILD; 
ALTER INDEX TestIndex02 ON dbo.test REBUILD;

As a side not here, disabling your clustered index will in fact prevent operations on the table taking place such as SELECT, INSERT etc

The conclusion that we may be led to here is that they can be safely dropped – and they can but be aware of a surrounding issue.

And that issue is that on a busy production server you might get 1 of three outcomes.

• The index drops instantly – which is the desired outcome.
• The drop statement is blocked, potentially for a long time and you end up blocking other.
• The drop statement is chosen as a deadlock victim (again after a period of time when you also block other)

Similar scenarios will also play out if the index is to be disabled before dropping.

Basically, having these hypothetical indexes hanging around is a (very) negligible performance overhead however, it may lead you to false assumptions when looking at certain metadata regarding indexes.

It’s probably also good house keeping to get rid of these – but do be aware of any blocking that may occur.

Here’s what Microsoft has to say – https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190172(v=sql.105)