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:

/*** TODO: Replace [dbo].[MyTableName] with the name of your specific table ***/
step 1: CREATE
CREATE hypothetical indexes using the WITH STATISTICS_ONLY clause:
/* TODO: Replace with your own index definitions, but don't forget to use WITH STATISTICS_ONLY */
CREATE NONCLUSTERED INDEX [IX_Hypothetical]
ON [dbo].[MyTableName] ( [column1], [column2] )
WITH STATISTICS_ONLY
GO
step 2: AUTOPILOT
Generate and run DBCC AUTOPILOT commands to mark ALL hypothetical indexes for AUTOPILOT:
DECLARE @TableName nvarchar(256) = '[dbo].[MyTableName]'
DBCC AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])
DECLARE @cmd nvarchar(max)
DECLARE cmd CURSOR
LOCAL FAST_FORWARD
FOR
SELECT N'DBCC AUTOPILOT(0,' + CONVERT(nvarchar(MAX), DB_ID()) + N',' + CONVERT(nvarchar(MAX), object_id) + N',' + CONVERT(nvarchar(MAX), index_id) + N');'
FROM sys.indexes
WHERE is_hypothetical = 1
AND object_id = OBJECT_ID(@TableName)
OPEN cmd
WHILE 1=1
BEGIN
FETCH NEXT FROM cmd INTO @cmd
IF @@FETCH_STATUS <> 0 BREAK;
PRINT @cmd;
EXEC(@cmd);
END
CLOSE cmd
DEALLOCATE cmd
GO
step 3: GENERATE ESTIMATED PLAN
run the below to generate an estimated plan assuming the existence of hypothetical indexes marked for autopilot
WARNING: This was found to sometimes cause SQL Crash Dumps, specifically when cancelling mid-execution.
GO
SET AUTOPILOT ON;
GO
/* TODO: Add your test query here */
GO
SET AUTOPILOT OFF;
GO
step 4: CLEANUP
Generate and run DROP commands for ALL hypothetical indexes on the relevant table:
DECLARE @TableName nvarchar(256) = '[dbo].[MyTableName]'
DECLARE @cmd nvarchar(max)
DECLARE cmd CURSOR
LOCAL FAST_FORWARD
FOR
SELECT N'DROP INDEX ' + QUOTENAME(name) + N' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + N'.' + QUOTENAME(OBJECT_NAME(object_id)) + N';'
FROM sys.indexes
WHERE object_id = OBJECT_ID(@TableName)
AND is_hypothetical = 1
OPEN cmd
WHILE 1=1
BEGIN
FETCH NEXT FROM cmd INTO @cmd
IF @@FETCH_STATUS <> 0 BREAK;
PRINT @cmd;
EXEC(@cmd);
END
CLOSE cmd
DEALLOCATE cmd
GO
The script is also available in our Madeira Toolbox repository

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.