Did someone say SQL Server AUTOPILOT?

After trying for years, in 2016, I was finally able to attend a SQLskills training class. I was sure this in-depth knowledge would take my SQL skills to the next level (no pun intended) and, man was I right! That was truly a week to remember and my brain has never absorbed so much awesome data. I was still processing what I learned for weeks following.

There was one topic that was not really part of the core training, but was mentioned and caught my attention: DBCC AUTOPILOT. I have been around SQL Server for a long time and had never even heard the reference. After reading Brent Ozar’s (t) office hours for 01/11/2017 and hearing the topic of hypothetical indexes come up and the team not mentioning this function, I thought- blog idea!

As frequent with Microsoft, DBCC AUTOPILOT is an undocumented function and there is not a lot of material about its use. Even Google didn’t find much detail to explain how I could put SQL Server in AUTOPILOT mode…

This function might have you excited like I was, but if you have not heard of it you might not really know what you are excited about. No, it will not fix all your SQL Server worries and take over so you can go make your favorite caffeinated beverage. However, it will help you in the area of query and index tuning. The DBCC AUTOPILOT function allows you to create hypothetical indexes on your database without affecting disk space or your table structure. You can troubleshoot a slow-running query and find a possible index that you are just 100% sure is the magic that will fix the slowness. Well, who wants to wait for hours to apply this index magic to a 100-million row table? Not me. Now you have DBCC AUTOPILOT to the rescue. You can create that index that will take full advantage of your data size and statistics without the wait time. Once you create the hypothetical index you can run your slow-running query against it and see if it is the magic or if you need to go back to the tuning board.

Now let’s see a demo of this magic in action.

I am using the AdventureWorks2014 database and doing a simple query against the Person.Person table. I’ve removed a built in index to make my demo work.

SET STATISTICS IO ON;
GO

SELECT rowguid
FROM AdventureWorks2014.Person.Person
WHERE rowguid = 'A948E590-4A56-45A9-BC9A-160A1CC9D990';


SET STATISTICS IO OFF;
GO

SET STATISTICS IO ON;

SELECT rowguid

FROM AdventureWorks2014.Person.Person

WHERE rowguid = 'A948E590-4A56-45A9-BC9A-160A1CC9D990';

SET STATISTICS IO OFF;

You see that when I run the query and show the actual execution plan an index scan occurred on the primary key. Also, notice that SQL Server has identified that this query could benefit from adding an index on the Person.Person table. Wow! That is great news. However, in production my Person.Person table has 20 million rows and that would take a while to add just to test the new performance of my query.

-- Add suggested index

USE [AdventureWorks2014];
GO

CREATE UNIQUE NONCLUSTERED INDEX [AK_Person_rowguid] 
ON [Person].[Person](
	[rowguid] ASC
)
WITH STATISTICS_ONLY = 0;
GO

-- STATISTICS_ONLY = 0   ::  Create an index without computed statistics
-- STATISTICS_ONLY = -1  ::  Generate statistics

-- Add suggested index

USE [AdventureWorks2014];

CREATE UNIQUE NONCLUSTERED INDEX [AK_Person_rowguid]

ON [Person].[Person](

[rowguid] ASC

)

WITH STATISTICS_ONLY = 0;

-- STATISTICS_ONLY = 0 :: Create an index without computed statistics

-- STATISTICS_ONLY = -1 :: Generate statistics

I have now added the index using the hint STATISTICS_ONLY which triggers SQL Server to create the hypothetical index. Notice it does not show up in the index list of the table, but does appear when I run the sp_helpindex procedure. Notice the index_description for the newly added index AK_Person_rowguid shows “hypothetical”. This means that the index has been created but will not be evaluated or included unless AUTOPILOT is initiated.

Here comes DBCC AUTOPILOT to the rescue. Let’s see how I can get help for DBCC AUTOPILOT and see the settings required to use this function.

DBCC TRACEON (2588);
GO
DBCC HELP ('AUTOPILOT');
GO
/*
dbcc AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])

TypeId = 0 - Use with non-clustered indexes
 5 - Start Session or clean previous command
 6 - Use with clustered indexes
DBID = Database ID in which to enable/execute the command (SELECT DB_ID(name), name FROM sys.databases)
MaxQueryCost = Set query cost, possibly for parrallelism
tabid = Table ID to tune (SELECT object_id(name), * FROM sys.indexes WHERE name = '')
indid = Index ID to tune (SELECT object_id(name), * FROM sys.indexes WHERE name = '')
pages = Simulate object physical pages
flag = unknown function
rowcounts = Set the number of rows in some command
*/

DBCC TRACEON (2588);

DBCC HELP ('AUTOPILOT');

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

TypeId = 0 - Use with non-clustered indexes

5 - Start Session or clean previous command

6 - Use with clustered indexes

DBID = Database ID in which to enable/execute the command (SELECT DB_ID(name), name FROM sys.databases)

MaxQueryCost = Set query cost, possibly for parrallelism

tabid = Table ID to tune (SELECT object_id(name), * FROM sys.indexes WHERE name = '')

indid = Index ID to tune (SELECT object_id(name), * FROM sys.indexes WHERE name = '')

pages = Simulate object physical pages

flag = unknown function

rowcounts = Set the number of rows in some command

You can see there are 8 parameters that can be specified. I will only focus on 4 of these parameters for this demonstration.

The TypeId is needed to tell AUTOPILOT how to run. I’m using value of 5 to start the AUTOPILOT session. DBID is just what you think, you will need to get the database ID from the sys.databases table. Then I need to access the TabId and IndId, which are the Table ID and Index ID, from the table and index I’m working with. See the query below to get that information. And notice how I dropped the hypothetical index and clear the cache to proceed with the demo.

-- Get index ID's
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('Person.Person'));
GO

-- Drop Indexes for cleanup
DROP INDEX [AK_Person_rowguid] ON Person.Person;
GO

-- Clear cache
DBCC FREEPROCCACHE;
GO

-- Get index ID's

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('Person.Person'));

-- Drop Indexes for cleanup

DROP INDEX [AK_Person_rowguid] ON Person.Person;

-- Clear cache

DBCC FREEPROCCACHE;

Now let’s see AUTOPILOT in action.

USE AdventureWorks2014;
GO

SET AUTOPILOT ON;
GO

SELECT  rowguid
FROM    AdventureWorks2014.Person.Person
WHERE   rowguid = 'A948E590-4A56-45A9-BC9A-160A1CC9D990';
GO

SET AUTOPILOT OFF;
GO

USE AdventureWorks2014;

SET AUTOPILOT ON;

SELECT rowguid

FROM AdventureWorks2014.Person.Person

WHERE rowguid = 'A948E590-4A56-45A9-BC9A-160A1CC9D990';

SET AUTOPILOT OFF;

So I ran the test query with AUTOPILOT and see that since I haven’t added the index it’s running with an index scan on the primary key.

Now let’s add the index and run AUTOPILOT again.

-- Add suggested index
USE [AdventureWorks2014];
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_Person_rowguid] 
ON [Person].[Person](
 [rowguid] ASC
)
WITH STATISTICS_ONLY = 0;
GO

-- STATISTICS_ONLY = 0 :: Create an index without computed statistics
-- STATISTICS_ONLY = -1 :: Generate statistics 

USE AdventureWorks2014;
GO

DBCC AUTOPILOT (5, 23, 0, 0, 0);
DBCC AUTOPILOT (0, 23, 1765581328, 4);
GO

SET AUTOPILOT ON;
GO

SELECT rowguid
FROM AdventureWorks2014.Person.Person
WHERE rowguid = 'A948E590-4A56-45A9-BC9A-160A1CC9D990';
GO

SET AUTOPILOT OFF;
GO

DBCC AUTOPILOT (5, 23);
GO

-- Add suggested index

USE [AdventureWorks2014];

CREATE UNIQUE NONCLUSTERED INDEX [AK_Person_rowguid]

ON [Person].[Person](

[rowguid] ASC

)

WITH STATISTICS_ONLY = 0;

-- STATISTICS_ONLY = 0 :: Create an index without computed statistics

-- STATISTICS_ONLY = -1 :: Generate statistics

USE AdventureWorks2014;

DBCC AUTOPILOT (5, 23, 0, 0, 0);

DBCC AUTOPILOT (0, 23, 1765581328, 4);

SET AUTOPILOT ON;

SELECT rowguid

FROM AdventureWorks2014.Person.Person

WHERE rowguid = 'A948E590-4A56-45A9-BC9A-160A1CC9D990';

SET AUTOPILOT OFF;

DBCC AUTOPILOT (5, 23);

You can see I start the AUTOPILOT session then tell AUTOPILOT which Table and Index to use by the ID values I found earlier. Now with the magic of AUTOPILOT I’m using the index I created and doing an Index Seek instead of an Index Scan. Hooray…

Now I can cleanup our testing and start the process of implementing this index in production.

Enjoy!

Did someone say SQL Server AUTOPILOT?6 min read

Related

Leave a Reply Cancel reply