Hypothetical Indexes on SQL Server

Sometimes, you have to test out alternative indexing strategies, but the task of creating the candidate indexes would just take too long. Is there another way? Well, yes, why not use the same method the DTA (Database Tuning Advisor) uses, and take the tedium out of the job.

If we want to predict how well a query will perform as a result of adding a new index on the table, we need to wait while the index is created before we can test it. On larger tables, the creation of the index can take a significant amount of time and if you are trying a number of alternative indexing strategies, the wait can become very tedious. Furthermore, it is a common frustration to find that, after waiting for many minutes for the creation of the index, you realize that it is not using the index when you go to look at the query plan.

So wouldn’t it be nice if we could try a hypothetical index just to test if the index really will be useful for the query. That is possible, but not straightforward; The reason that the technique exists is that it is used by the DTA (Database Tuning Advisor) to recommend a missing index. In this article I’ll present you some undocumented commands that are used to do it.

Creating a hypothetical index

There is a special syntax of the CREATE INDEX command that allows us to create a hypothetical index. This is an index that creates the metadata of the index on sysindexes and a statistic associated to the index, but does not create the index itself.

Suppose we have the following query from AdventureWorks2012 database:

If we want to create a hypothetical index on SalesOrderHeader table we could run:

The relational index option STATISTICS_ONLY = -1, which is undocumented, means that the index itself will not be created, but only the statistic associated with the index. This index be neither considered nor used by the query optimizer unless you run a query in AUTOPILOT mode.

DBCC AUTOPILOT and AUTOPILOT MODE

There is command called “SET AUTOPILOT ON” used to enable support to hypothetical indexes, and this is used with other DBCC command called “DBCC AUTOPILOT“.

First let’s see them working together and then I’ll give you more details about it:

1705-1-c490d761-2232-4322-804d-546dc0c74

1705-1-2019f5e2-e642-47c2-8e42-3c06710ab

When running on autopilot mode, SQL Server doesn’t execute the query but it returns an estimated execution plan that considers all indexes enabled by DBCC AUTOPILOT command, including the hypothetical ones.

DBCC AUTOPILOT

There are a few things you could do with this command, first let’s find out what the syntax is. We can find out the syntax of all undocumented commands by using the trace flag 2588 and then running DBCC HELP to see:

Making AUTOPILOT easier to use

The parameters that you have to use are not straightforward. This means that, if you are working with a query with lots of tables, it can get boring to write all the DBCC AUTOPILOT commands and this might discourage you from using it. Because of this, I’ve created a procedure to make it a little easier to use.

Originally I created this procedure after answering a student’s question about how to make it easier to use hypothetical indexes on SQL Server. So I thought you may like it.

Unfortunately it relies on a CLR stored procedure to SET the AUTOPILOT, but if you don’t mind to use it in a develop environment (which is something normal to do) then you can use it, following is the CLR code, and if you are interested you can download the project code here:

And following is the code to compile it on SQL Server and to create another procedure to simulate the hypothetical indexes:

The stored procedure st_TestHipotheticalIndexes expects two input parameters:

  • @SQLIndex: Here you should specify the command to create the index that you want to try (the hypothetical indexes), if you want to try more than one index, just call it separating many “create index” commands by a semicolon. For instance:

  • @Query: Here you should write the query you want to try.

Here is a sample of how to call it:

The results of the query above is an XML datatype with the query plan considering the suggested index:

1705-b27d8e96-93c7-44b3-aadb-431ec43a9dd

Another sample:

1705-3c56c020-53c1-4157-93f6-1d641c00443

Now it is easier to try out the effect of various indexes. Let me know what do you think and please don’t mind the clumsy code in the procedure to get the tablename, indexname.

Conclusion

There is a lot of mystery about these undocumented features, but I’m sure this will be enough to get you started with doing tests using Hypothetical indexes. I am sure I don’t need to tell you not to use this is in production environment do I? This is undocumented stuff, so nobody can guarantee what it is really doing, and the side-effects unless Microsoft chooses to make it officially public and documented.

That’s all folks.

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue