Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Performance Tuning with Hypothetical Indexes


By:   |   Read Comments (4)   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, March 29, 2017 - 12:45:19 AM - Torsten Back To Top

Hi Daniel!

Great post.

I was playing around with dbcc autopilot a bit and I guess typeid 10 is using the heap (table scan) to get a base line.

Still do not know what typeid 11 is for.

Also even if you go with do not propose include index SQL Server will consider them if you use the dta tool which is interesting.

Thanks for your engagement!

Torsten

 


Sunday, September 07, 2014 - 1:02:41 AM - Daniel Farina Back To Top

Hi Mahesh and thanks for reading!

Since this is an undocumented feature there is not information about it but, as far as I can tell, giving the fact that AUTOPILOT is the key component of Database Engine Tuning Advisor (DTA); this should work on all version in which DTA is available.

You can check this link http://msdn.microsoft.com/en-us/library/cc645993 and look at the section named "Management Tools" for this feature. In short, the minimum required version 2008 Standard.

Best Regards!

 


Tuesday, September 02, 2014 - 3:51:59 PM - Mahesh Back To Top

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting

Which version of SQL Server are these applicable on? I ran the code on SQL server 2008 R2 and found that the query plan does not return results as noted in the artic;le.


Friday, June 06, 2014 - 9:30:00 AM - Neeraj Mittal Back To Top

Excellent write up on unknown SQL options in-depth with examples !! Keep it Up !!
 
Cheers
Neeraj


Learn more about SQL Server tools