Get Free Tips
MSSQLTips.com
Home » SQL Server Performance Tuning with Hypothetical Indexes
Performance Tuning

SQL Server Performance Tuning with Hypothetical Indexes

written by Daniel Farina 0 comments

Problem

You are tuning a query and need to check the performance impact of a new index. But the table has millions of rows and it will take a lot of time to create an index. Keep reading and you will see how to create and test hypothetical indexes with the undocumented DBCC AUTOPILOT command.

Solution

Query tuning is not an easy task. Basically it depends on three factors: your degree of knowledge, the query itself and the amount of optimization required. It’s not about creating indexes everywhere as some inexperienced people do. Indexes consume disk space and need to be maintained. I am not referring to fragmentation here which is also an issue, but the work SQL Server does to keep indexes updated when rows are inserted or modified. The more indexes a table has the more resources it takes to commit changes.

What Are SQL Server Hypothetical Indexes

If you perform a query to sys.indexes system view you will find a bit column labeled is_hypothetical. According to SQL Server documentation, when this bit is set to 1 then the “Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics”.

Every index has statistics associated with it, but the opposite is not always true. When you create an index SQL Server keeps track of the distribution and density of values that are used to determine query cost and of course, those are the index’s own statistics.

Hypothetical indexes are like an index’s empty shell in the way that it doesn’t contain data.

How to create SQL Server Hypothetical Indexes

To create Hypothetical indexes you must add the undocumented option WITH STATISTICS_ONLY to the CREATE INDEX statement. Here is an example:

CREATE INDEX MyIndex ON MyTable (MyColumn) WITH STATISTICS_ONLY = 0 
CREATE INDEX MyIndex ON MyTable (MyColumn) WITH STATISTICS_ONLY = -1

The option STATISTICS_ONLY accepts two values: 0 to create an index without computed statistics; and -1 to generate statistics.

But as I previously stated, you won’t be able to use those indexes. So you may be asking yourself what are the next steps and the answer is the undocumented DBCC AUTOPILOT command.

SQL Server DBCC AUTOPILOT Command Usage

Like every DBCC command, even the undocumented ones, they have minimal help available by executing the DBCC HELP command. But since this is an undocumented feature it’s a little trickier. To get help you must enable trace flag 2588 first.

As you can see on the image below the help provided is limited to parameter places and nothing more. So to learn its basic usage I had to play with the Database Engine Tuning Advisor and set up a background Profiler trace to capture the DBCC calls. The next was trial and error.

DBCC Autopilot Help

But it doesn’t end there. In order to get autopilot to work we must set the AUTOPILOT option on.  This option is like SHOWPLAN; the difference is that when the AUTOPILOT option is set the query processor generates a plan by taking into consideration the directives passed by the DBCC AUTOPILOT command. Of course, if you don’t give any directives then the plan will be made as usual. Being that, if you send incorrect commands like enabling nonexistent indexes you will face an error message telling you that the query processor could not obtain access to a required interface.

DBCC Error Message

SQL Server DBCC AUTOPILOT Parameters

Here is the list of DBCC AUTOPILOT parameters.

Parameter

Description

typeid

Type ID of the command.  By now I identified three values for typeid parameter, but there are more.

Type ID = 5: Start session or clean previous commands.

Type ID = 0: To use with non-clustered indexes.

Type ID = 6: To use with clustered indexes only.

dbid

The database ID in which to enable/execute the command.

maxQueryCost

Supposedly to set query cost. I have not yet discovered a practical way to use it.

tabid

Table ID to tune.

indid

Table’s index ID to tune.

pages

To simulate object’s physical pages

flag

Unknown

rowcounts

This is used to set the number of rows in some commands.

Sample Testing Code

Now I will go through the usage of these commands, but first we need to create our sample database.

1. Here is the CREATE DATABASE script

USE [master]
GO
/****** Object:  Database [TestDB]    Script Date: 08/03/2014 04:17:57 p.m. ******/
CREATE DATABASE [TestDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDB', FILENAME = N'E:\MSSQL\TestDB.mdf' , 
SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDB_log', FILENAME = N'E:\MSSQL\TestDB.ldf' , 
SIZE = 4096KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO
ALTER DATABASE [TestDB] SET RECOVERY SIMPLE

2. We are going to create two simple tables

USE TestDB
GO
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
    DROP TABLE dbo.Customers
GO
CREATE TABLE Customers (
CustomerID  INT NOT NULL,
CustomerName  VARCHAR(50) NOT NULL,
CustomerAddress VARCHAR(50) NOT NULL,
[State]   CHAR(2) NOT NULL,
CustomerCategoryID CHAR(1) NOT NULL,
LastBuyDate  DATETIME,
PRIMARY KEY CLUSTERED (CustomerID)
)
IF OBJECT_ID('dbo.CustomerCategory', 'U') IS NOT NULL
    DROP TABLE dbo.CustomerCategory
GO
CREATE TABLE CustomerCategory (
CustomerCategoryID CHAR(1) NOT NULL,
CategoryDescription VARCHAR(50) NOT NULL
)

3. You can create your own test data, but I provide the next few inserts for testing purposes.

USE [TestDB]
GO
USE TestDB
GO
INSERT INTO [dbo].[Customers] ([CustomerID],
                               [CustomerName],
                               [CustomerAddress],
                               [State],
                               [CustomerCategoryID],
                               [LastBuyDate])
SELECT   1,
        'Nicole Franco',
        '78 Fabien Freeway',
        'AZ',
        'C',
        '2013-05-27 02:46:20'
UNION ALL
SELECT  2,
        'Pablo Terry',
        '29 West Milton St.',
        'DE',
        'A',
        '2013-12-27 14:52:57'
UNION ALL
SELECT  3,
        'Desiree Lambert',
        '271 Fabien Parkway',
        'NY',
        'C',
        '2013-01-13 21:44:21'
UNION ALL
SELECT  4,
        'Chadwick Stephenson',
        '444 North Rocky Milton Avenue',
        'NH',
        'A',
        '2013-04-03 09:55:17'
UNION ALL
SELECT  5,
        'Daphne Gilbert',
        '97 Hague Blvd.',
        'VT',
        'B',
        '2014-01-15 20:55:35'
UNION ALL
SELECT  6,
        'Sonya Mosley',
        '60 West Fabien Avenue',
        'AZ',
        'A',
        '2014-01-23 01:38:20'
UNION ALL
SELECT  7,
        'Katina Browning',
        '835 Cowley Street',
        'NH',
        'B',
        '2014-04-08 21:10:51'
UNION ALL
SELECT  8,
        'Kari Boyd',
        '35 West First St.',
        'ND',
        'C',
        '2013-11-23 21:46:33'
UNION ALL
SELECT   9,
        'Seth Mc Bride',
        '912 First Freeway',
        'AK',
        'B',
        '2013-05-17 12:06:45'
UNION ALL
SELECT   10,
        'Bernard Knight',
        '33 Nobel Blvd.',
        'NY',
        'B',
        '2013-12-14 19:04:15'
GO
GO
INSERT [dbo].[CustomerCategory] ([CustomerCategoryID], [CategoryDescription])
 VALUES (N'A', N'Elite')
INSERT [dbo].[CustomerCategory] ([CustomerCategoryID], [CategoryDescription])
 VALUES (N'B', N'Special')
INSERT [dbo].[CustomerCategory] ([CustomerCategoryID], [CategoryDescription])
 VALUES (N'C', N'Regular')

4. This step is to create hypothetical indexes.  In this script I will create one clustered index and two non-clustered, one with statistics and the others without them.

USE TestDB
GO
CREATE INDEX IX_CustomerCategoryID_NO_STATS 
    ON dbo.CustomerCategory (CustomerCategoryID) WITH STATISTICS_ONLY = 0  
CREATE INDEX IX_CustomerCategoryID_STATS  
    ON dbo.CustomerCategory (CustomerCategoryID) WITH STATISTICS_ONLY = -1
CREATE CLUSTERED INDEX IX_CustomerCategoryID_CLUSTERED  
    ON dbo.CustomerCategory (CustomerCategoryID) WITH STATISTICS_ONLY = -1
GO

5. With this script we get information about our indexes statistics.

USE TestDB
GO
exec sp_helpindex CustomerCategory

DBCC SHOW_STATISTICS (CustomerCategory, IX_CustomerCategoryID_CLUSTERED)
DBCC SHOW_STATISTICS (CustomerCategory, IX_CustomerCategoryID_NO_STATS)
DBCC SHOW_STATISTICS (CustomerCategory, IX_CustomerCategoryID_STATS)
Index Statistics

6. We must keep note of our tables and indexes ID’s in order to use the DBCC AUTOPILOT command.

USE TestDB
GO
SELECT  object_id ,
   OBJECT_NAME(object_id) AS 'Table' ,
        name ,
        index_id ,
        type ,
        type_desc ,
        is_unique ,
        is_hypothetical 
    FROM sys.indexes 
    WHERE object_id in (object_id('CustomerCategory'), object_id('Customers'))
Indexes

7. As I previously stated, you can set autopilot to ON without executing any DBCC command and you will get the query’s plan.

USE TestDB
GO
SET AUTOPILOT ON
GO
SELECT  CustomerID ,
        CustomerName ,
        CustomerAddress ,
        State ,
        C.CustomerCategoryID ,
        LastBuyDate ,
        CC.CustomerCategoryID ,
        CategoryDescription FROM dbo.Customers C 
INNER JOIN dbo.CustomerCategory CC
    ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE State = 'NY'
GO 
SET AUTOPILOT OFF
GO
Autopilot without DBCC

Sample SQL Server Hypothetical Clustered Index

8. This script will enable our hypothetical Clustered Index.

USE TestDB
GO
DBCC AUTOPILOT (5, 20, 0, 0, 0)
DBCC AUTOPILOT (6,20,293576084,4)
GO 
SET AUTOPILOT ON
GO
 
SELECT  CustomerID ,
        CustomerName ,
        CustomerAddress ,
        State ,
        C.CustomerCategoryID ,
        LastBuyDate ,
        CC.CustomerCategoryID ,
        CategoryDescription FROM dbo.Customers C 
INNER JOIN dbo.CustomerCategory CC 
    ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE State = 'NY' 
GO
SET AUTOPILOT OFF
GO

Check the execution plan on the images below.

Autopilot with Hypothethical Clustered Index

I must advise that if you use TypeID = 6 for a Non-Clustered index Autopilot won’t raise any error, but on the execution plan the index will appear as Clustered, you can look at this yourself. The plan shows our previously created non-clustered index IX_CustomerCategoryID_NO_STATS as a clustered one

Autopilot with bogus Clustered Index

Sample SQL Server Hypothetical Non-Clustered Index

9. Now we do so with our hypothetical Non-Clustered Index.

USE TestDB
GO 
DBCC AUTOPILOT(5,20)
dbcc autopilot (0,20,293576084,2)
GO
SET AUTOPILOT ON
GO
SELECT  CustomerID ,
        CustomerName ,
        CustomerAddress ,
        State ,
        C.CustomerCategoryID ,
        LastBuyDate ,
        CC.CustomerCategoryID ,
        CategoryDescription FROM dbo.Customers C 
INNER JOIN dbo.CustomerCategory CC
    ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE State = 'NY'
GO
SET AUTOPILOT OFF
GO
DBCC AUTOPILOT(5,20)
GO

Check the execution plan on the images below

Autopilot with Hypothethical Non-Clustered Index

Tuning SQL Server Row Count and Physical Pages

10. To make the optimizer believe that our table has more rows than it has we set parameters five and seven of the DBCC AUTOPILOT to the desired values.  This is useful if you are working with a small data set like on development environments. A word of advice is that this command works only for clustered indexes, but you can use this command with a non-clustered index by using typeId = 6 taking into consideration that the query plan will show the index as a clustered index.

USE TestDB
GO 
DBCC AUTOPILOT(5,20)
dbcc autopilot (6, 20, 293576084, 4, 95959, 0, 155555550)
dbcc autopilot (6, 20, 277576027, 1, 2000, 0, 5000)
DBCC AUTOPILOT(1,20, -1)
GO
SET AUTOPILOT ON
GO
SELECT  CustomerID ,
        CustomerName ,
        CustomerAddress ,
        State ,
        C.CustomerCategoryID ,
        LastBuyDate ,
        CC.CustomerCategoryID ,8
        CategoryDescription FROM dbo.Customers C 
INNER JOIN dbo.CustomerCategory CC
    ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE State = 'NY'

GO
SET AUTOPILOT OFF
GO
DBCC AUTOPILOT(5,20)

Look at the number of rows in the execution plan.

Row Count tuning

Mysterious Output Table

11. There is a mysterious TypeID value that returns a table which I don’t fully understand with the execution plan.  Take a look.

USE TestDB
GO
DBCC AUTOPILOT (7, 20, 0, 0, 0, 1)
GO
SET AUTOPILOT ON
GO
SELECT  CustomerCategoryID,
        CategoryDescription
FROM dbo.CustomerCategory
GROUP BY CustomerCategoryID,CategoryDescription
GO
SET AUTOPILOT OFF
GO
Row Count tuning

Next Steps

Copyright (c) 2006-2025 Edgewood Solutions, LLC All rights reserved
sql server 2025 guide