Click here to monitor SSC
  • Av rating:
  • Total votes: 54
  • Total comments: 11
Fabiano Amorim

Hypothetical Indexes on SQL Server

18 December 2012

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:

SELECT SalesOrderID, OrderDate, Status, TerritoryID
  FROM Sales.SalesOrderHeader
 WHERE OrderDate = '20050701'

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

CREATE INDEX ixOrderDate ON Sales.SalesOrderHeader (OrderDate) WITH STATISTICS_ONLY = -1

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.


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:

SELECT dbid = DB_ID(),
       objectid = 
       indid = index_id
  FROM sys.indexes
object_id = 
   AND is_hypothetical = 1
  |dbid   |objectid	   |indid |
  |8	   |1266103551   |15    |

-- Use typeId 0 to enable a specifc index on AutoPilot mode
DBCC AUTOPILOT(0, 8, 1266103551, 15)
SELECT SalesOrderID, OrderDate, Status, TerritoryID
  FROM Sales.SalesOrderHeader
 WHERE OrderDate = '20050701'

Selecting the XML Showplan

The estimated execution plan

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.


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:

DBCC AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])

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:

-- CLR Proc
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
    public static void CLR_GetAutoPilotShowPlan
         SqlString SQL,
         out SqlXml PlanXML
        //Prep connection
        SqlConnection cn = new SqlConnection("Context Connection = True");

        //Set command texts
        SqlCommand cmd_SetAutoPilotOn = new SqlCommand("SET AUTOPILOT ON", cn);
        SqlCommand cmd_SetAutoPilotOff = new SqlCommand("SET AUTOPILOT OFF", cn);
        SqlCommand cmd_input = new SqlCommand(SQL.ToString(), cn);

        if (cn.State != ConnectionState.Open)

        //Run AutoPilot On

        //Run input SQL
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();

        da.SelectCommand = cmd_input;
        ds.Tables.Add(new DataTable("Results"));

        da.Fill(ds, "Results");

        //Run AutoPilot Off

        if (cn.State != ConnectionState.Closed)

        //Package XML as output
        System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
        //XML is in 1st Col of 1st Row of 1st Table
        xmlDoc.InnerXml = ds.Tables[0].Rows[0][0].ToString();
        System.Xml.XmlNodeReader xnr = new System.Xml.XmlNodeReader(xmlDoc);
        PlanXML = new SqlXml(xnr);

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

-- Enabling CLR
sp_configure 'clr enabled', 1
-- Publishing Assembly
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'CLR_ProjectAutoPilot')
  IF OBJECT_ID('st_CLR_GetAutoPilotShowPlan') IS NOT NULL
    DROP PROC st_CLR_GetAutoPilotShowPlan

  DROP ASSEMBLY CLR_ProjectAutoPilot
CREATE ASSEMBLY CLR_ProjectAutoPilot FROM 'C:\Fabiano\ ProjectAutoPilot\ProjectAutoPilot\bin\Release\ProjectAutoPilot.dll' WITH PERMISSION_SET = SAFE

CREATE PROCEDURE st_CLR_GetAutoPilotShowPlan (@Query NVarChar(MAX), @ShowPlan XML OUTPUT)
  EXTERNAL NAME CLR_ProjectAutoPilot.StoredProcedures.CLR_GetAutoPilotShowPlan

IF OBJECT_ID('st_TestHipotheticalIndexes', 'p') IS NOT NULL
  DROP PROC dbo.st_TestHipotheticalIndexes
CREATE PROCEDURE dbo.st_TestHipotheticalIndexes (@SQLIndex NVarChar(MAX), @Query NVarChar(MAX))
    DECLARE @CreateIndexCommand NVarChar(MAX),
            @IndexName NVarChar(MAX),
            @TableName NVarChar(MAX),
            @SQLIndexTMP NVarChar(MAX),
            @SQLDropIndex NVarChar(MAX),
            @SQLDbccAutoPilot NVarChar(MAX),
            @i Int,
            @QuantityIndex Int,
            @Xml XML

    IF SubString(@SQLIndex, LEN(@SQLIndex), 1) <> ';'
      RAISERROR ('Last character in the index should be ;', -- Message text.
                 16, -- Severity.
                 1 -- State.

    SET @SQLDropIndex = '';
    SET @QuantityIndex = LEN(@SQLIndex) - LEN(REPLACE(@SQLIndex, ';', ''))
    SELECT @SQLIndexTMP = SUBSTRING(@SQLIndex, 0, CharIndex(';', @SQLIndex))
    SET @i = 0
    WHILE @i < @QuantityIndex
      SET @SQLIndexTMP = SUBSTRING(@SQLIndex, 0, CharIndex(';', @SQLIndex))
      SET @CreateIndexCommand = SUBSTRING(@SQLIndexTMP, 0, CharIndex(' ON ',@SQLIndexTMP))
      SET @IndexName = REVERSE(SubString(REVERSE(@CreateIndexCommand), 0, CharIndex(' ', REVERSE(@CreateIndexCommand))))
      SET @TableName = SUBSTRING(REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', ''), 0, CharIndex(' ', REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', '')))
      SET @SQLIndex = REPLACE(@SQLIndex, @SQLIndexTMP + ';', '')
      --SELECT @SQLIndex, @SQLIndexTMP, @CreateIndexCommand, @TableName, @IndexName
      -- Creating hypotetical index
      IF CharIndex('WITH STATISTICS_ONLY =', @SQLIndexTMP) = 0
        SET @SQLIndexTMP = @SQLIndexTMP + ' WITH STATISTICS_ONLY = -1'
      -- PRINT @SQLIndexTMP
      EXEC (@SQLIndexTMP)
      -- Creating query to drop the hypotetical index
      SELECT @SQLDropIndex = @SQLDropIndex + 'DROP INDEX ' + @TableName + '.' + @IndexName + '; '
      -- PRINT @SQLDropIndex
      -- Executing DBCC AUTOPILOT
      SET @SQLDbccAutoPilot = 'DBCC AUTOPILOT (0, ' + 
                                               CONVERT(VarChar, DB_ID()) + ', '+ 
                                               CONVERT(VarChar, OBJECT_ID(@TableName),0) + ', ' +
                                               CONVERT(VarChar, INDEXPROPERTY(OBJECT_ID(@TableName), @IndexName, 'IndexID')) + ')'

      EXEC (@SQLDbccAutoPilot)
      --PRINT @SQLDbccAutoPilot
      SET @i = @i + 1
    -- Executing Query
    DECLARE @PlanXML xml

    EXEC st_CLR_GetAutoPilotShowPlan @Query = @Query, 
                                     @ShowPlan = @PlanXML OUT
    -- Droping the indexes
    EXEC (@SQLDropIndex)
    -- Execute error retrieval routine.
    SELECT ERROR_NUMBER()    AS ErrorNumber,
           ERROR_SEVERITY()  AS ErrorSeverity,
           ERROR_STATE()     AS ErrorState,
           ERROR_PROCEDURE() AS ErrorProcedure,
           ERROR_LINE()      AS ErrorLine,
           ERROR_MESSAGE()   AS ErrorMessage;

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:
@SQLIndex = 'CREATE INDEX ix_12 ON Products (Unitprice, CategoryID, SupplierID) INCLUDE(ProductName);CREATE INDEX ix_Quantity ON Order_Details (Quantity);',
  • @Query: Here you should write the query you want to try.

Here is a sample of how to call it:

EXEC dbo.st_TestHipotheticalIndexes @SQLIndex = 'CREATE INDEX ix ON Order_Details(Quantity);', 
@Query = 'SELECT * FROM Order_Details WHERE Quantity < 1'

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

the query plan

Another sample:

-- Sample 2
EXEC dbo.st_TestHipotheticalIndexes @SQLIndex = 'CREATE INDEX ix_12 ON Products (Unitprice, CategoryID, SupplierID) INCLUDE(ProductName);CREATE INDEX ix_Quantity ON Order_Details (Quantity);', @Query = 'SELECT p.ProductName, p.UnitPrice, s.CompanyName, s.Country, od.quantity FROM Products as P INNER JOIN Suppliers as S ON P.SupplierID = S.SupplierID INNER JOIN order_details as od ON p.productID = od.productid WHERE P.CategoryID in (1,2,3) AND P.Unitprice < 20 AND S.Country = ''uk'' AND od.Quantity < 90'

The revamped execution plan

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.


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.

Fabiano Amorim

Author profile:

Fabiano is fascinated by the SQL Server Query Processor and the way it works to optimize queries, procedures and functions. He graduated as a Technical Processor from Colégio Bezerra de Menezes, SP- Brazil, and has worked for several years with SQL Server, focusing in creating Data Warehouses and optimizing T-SQL codes for many companies in Brazil and Argentina. Fabiano is a SQL Server MVP, MCP for SQL Server 2000, MCTS and MCITP Data Base Developer for SQL Server 2005 and 2008. He also is actively involved in SQL Server community though forums such as MSDN and TechNet Brazil, writes articles for Simple-Talk and SQL Server Magazine Brazil, and he also presents online Webcasts and In-Person events for Microsoft Brazil. His blog is on

Search for other articles by Fabiano Amorim

Rate this article:   Avg rating: from a total of 54 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.

Subject: Gud.
Posted by: Anonymous (not signed in)
Posted on: Tuesday, January 1, 2013 at 11:56 PM
Message: Good Article :). Started New Year with your great article :)

Subject: Dang
Posted by: Anonymous (not signed in)
Posted on: Monday, January 7, 2013 at 7:00 AM
Message: Fabiano, great stuff thank you.

Subject: Perfect!
Posted by: Mariana (not signed in)
Posted on: Tuesday, January 22, 2013 at 6:52 AM
Message: Fabiano, it´s exciting read something written by you. You make the internal mechanisms of SQL Server look simple.

Thanks for all your posts that I´ve ever read.

I can say I´m your fan!!

Subject: .
Posted by: mcflyamorim (view profile)
Posted on: Tuesday, January 22, 2013 at 7:00 AM
Message: Thanks all for the kind words in your comments, I really appreciate it.

That is what motivates me on keep writing :-)

Fabiano Amorim

Subject: Warning Message
Posted by: Anonymous (not signed in)
Posted on: Tuesday, January 22, 2013 at 8:27 AM
Message: Fabiano-

The command to create the hypothetical index works, but SSMS highlights STATISTICS_ONLY as an error with this text:

Invalid usage of the option STATISTICS_ONLY in the CREATE INDEX statement.

Any ideas, perhaps since the option is unofficial, undocumented?

Subject: Answered my own question
Posted by: Anonymous (not signed in)
Posted on: Tuesday, January 22, 2013 at 8:29 AM
Message: If you modify the provided command slight to include parentheses around the STATISTICS_ONLY option, the error is removed:


Subject: Very interesting
Posted by: Anonymous (not signed in)
Posted on: Tuesday, January 22, 2013 at 8:52 AM
Message: I always wondered how DTA worked. Interesting article but it does need to be proof read.

Subject: Compatibility
Posted by: Jonathan D. Myers (not signed in)
Posted on: Tuesday, January 22, 2013 at 1:38 PM
Message: Is this valid for only 2012, or is it backwards-compatible with 2005 and 2008? If not, is there a related method to do similar work in the legacy versions?


Subject: Great article
Posted by: Roshan Joseph (not signed in)
Posted on: Tuesday, January 22, 2013 at 8:01 PM
Message: Thanks for sharing the tip. Definitely helpful !

Subject: Test
Posted by: Anonymous (not signed in)
Posted on: Wednesday, January 23, 2013 at 3:27 AM
Message: nice explanations

Subject: Jonathan D. Myers
Posted by: mcflyamorim (view profile)
Posted on: Wednesday, January 23, 2013 at 11:16 AM
Message: Hi Jonathan I've tried this on SQL2005/2008/2012 and they all works.

Not sure about 2000...


Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Microsoft and Database Lifecycle Management (DLM): The DacPac

The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx), provides an... Read more...

 View the blog

Top Rated

Working with SQL Server data in Power BI Desktop
 What's the best way of providing self-service business intelligence (BI) to data that is held in... Read more...

Microsoft and Database Lifecycle Management (DLM): The DacPac
 The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx),... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Archiving Hierarchical, Deleted Transactions Using XML
 When you delete a business transaction from the database, there are times when you might want to keep a... Read more...

Rollback and Recovery Troubleshooting; Challenges and Strategies
 What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.