Performance Tuning Like a Pro - with Hypothetical Indexes

Every once in a while there comes an opportunity to "upgrade" your abilities with newly acquired knowledge that lets you "step up your game" and possibly add some "wow factor" to your work, leaving your peers awe-struck by your amazing new "magic trick".

As a SQL Server consultant, one such opportunity that I had in my line of work, is when I learned about "Hypothetical Indexes" and how to use them.

What are Hypothetical Indexes?

Using Hypothetical Indexes, you can generate an estimated execution plan for a given query, that would essentially assume the existence of a "hypothetical" index as if it actually exists as a real index. Compare that estimated execution plan to its counterpart without the hypothetical index, and you'll be able to determine whether creating this index for real is worth the time and effort.

Hypothetical Indexes are actually nothing new in SQL Server. It existed since SQL Server version 2005. However, its use is still not widespread to this day. Most likely because it's not very easy to use and the relevant commands are undocumented.

You see, creating a hypothetical index is pretty easy. You simply add the WITH STATISTICS_ONLY clause to your index creation command, and that will create a "hypothetical" index object with only statistics but no search tree.

But the difficult part comes when you actually need to use it, as that involves utilizing undocumented commands such as DBCC AUTOPILOT and SET AUTOPILOT ON, which are not very user-friendly.

There is already a very thorough article about Hypothetical Indexes over at the MSSQLTIPS website. It's very detailed and I strongly recommend reading it to learn more about hypothetical indexes in SQL Server. There's also a similar article by Pinal Dave on this topic which is pretty good. And there's also this article by Fabiano Amorim on the Red-Gate website Simple Talk which even provides an interesting CLR function to play with.

Seeing as this topic was already covered pretty well by other people, I wouldn't be rehashing the same thing. What I want to do instead, is provide you with one more tool to your belt which should make it even easier for you to create and use hypothetical indexes.

Here's a Script

I created the T-SQL script below as a very easy-to-use "template" of sorts for hypothetical indexes.

It involves 4 steps:

1. CREATE the hypothetical index or indexes using the WITH STATISTICS_ONLY clause. Replace this with your own relevant index definition.

2. Apply DBCC AUTOPILOT on all hypothetical indexes for a given table (you can specify the table name for the @TableName variable in the script, or leave it NULL to apply on all hypothetical indexes in the database).

3. Set AUTOPILOT mode to ON and GENERATE an ESTIMATED PLAN. Paste the relevant query that you want to test here, for which you want to generate the estimated plan. IMPORTANT NOTE: Dynamic SQL will NOT work here as you won't be getting the estimated plan for the query within.

4. CLEANUP by dropping all hypothetical indexes for a given table (again, you can optionally specify the relevant table name for the @TableName variable here, or leave it NULL to drop all hypothetical indexes in the database).

Once you fill in all the blanks, you can simply run the script as a whole and it will do all the steps necessary to CREATE the hypothetical index, activate DBCC AUTOPILOT as needed, generate the ESTIMATED EXECUTION PLAN, and DROP the hypothetical index.

• Here is the script, available at our Madeira Toolbox on GitHub.

Crash Warning!

A cautionary word of advice:

While using hypothetical indexes, I have experienced an ACCESS VIOLATION CRASH in SQL Server, specifically when trying to cancel the execution of a query running with SET AUTOPILOT ON. I saw this happen on multiple SQL Server versions, including 2016, 2017, and 2019.

This generates a Memory Crash Dump and error messages in the error log that look like this:

Error: 17310, Severity: 20, State: 1. A user request from the session with SPID 566 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory

Error: 3624, Severity: 20, State: 1. A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.

I can only guess that this behavior has something to do with the fact that the AUTOPILOT commands are not documented.

But, as long as you don't use this feature too frequently, and as long as you don't cancel an AUTOPILOT execution, then you should be fine.