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.

TL;DR: 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 need to specify the table name for the @TableName variable in the script).
3. Activate AUTOPILOT mode 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, specify the relevant table name for the @TableName variable here).

Once you fill in all the blanks, you can simply run the whole 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 in my GitHub Gists:

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.

For more details and to learn more about Hypothetical Indexes, check out the full blog post at madeiradata.com