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.
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.
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
GO5. 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)
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'))
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
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.
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
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
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.
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
Next Steps
- Read about DBCC Commands on MSDN.
- For other ways of getting information about statistics this tip will do the trick: Querying SQL Server Index Statistics.
- Take a look at more Indexing Tips.
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning. He studied at Universidad de Buenos Aires. Daniel started working as a programmer at a young age. Over the years he specialized in databases, particularly SQL Server and Oracle. Now with 30 years of age, his work experience includes working with various technologies like VB, C, .NET, web development, Windows and Linux systems. He likes to read about science, psychology, philosophy and many other things. In his spare time, he trains powerlifting aiming to compete.
- MSSQLTips Awards: Author of the Year – 2018 | Champion (100+ tips) – 2018 | Author Contender – 2015-2017, 2019
