Did someone say SQL Server AUTOPILOT?6 min read


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 (toffice 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.

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.


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.

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.

Now let’s see AUTOPILOT in action.

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.

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.


Leave a Reply

Your email address will not be published. Required fields are marked *