Introduction

As we know SQL Server uses a cost based optimizer to create the execution plans. That means SQL Server evaluates many possibilities to create an execution plan and chose for the plan with the lower cost.

One of the problems we have today is that if we want to predict how a query will perform adding a new index on the table, we need to wait for the creation of the index.

Sometimes waiting for the creation of the index is a nightmare, especially if the table is too big. Furthermore, after wait for 20 minutes for the creation of the index, when you go look at the query plan it is not using the index .

So the question is, how to create an hypothetically index? Just to test if the index really will be useful for the query.

WITH STATISTICS_ONLY

To create an hypothetically index you could use an undocumented syntax in the create index command.

For instance:

   1: USE AdventureWorksDW

   2: GO

   3: CREATE INDEX ix_FirstName ON DimCustomer(FirstName) WITH STATISTICS_ONLY = -1

   4: GO

This will create the index only with the statistics (density and histogram stuff). You could check the index using the sp_helpindex:

   1: sp_HelpIndex DimCustomer

   1: DBCC SHOW_STATISTICS(DimCustomer, ix_FirstName)

My friend Benjamin Nevares did a very good explanation about this here:

http://sqlblog.com/blogs/ben_nevarez/archive/2009/11/11/database-engine-tuning-advisor-and-the-query-optimizer.aspx

PS: If you create the index using “WITH STATISTICS_ONLY = 0” SQL will not create the statistic for your table. Just the hypothetical index.

DBCC AUTOPILOT and SET AUTOPILOT

Now that we have the hypothetical index, how to use it?

Let’s try to use the “index” hint :

   1: SELECT * FROM DimCustomer WITH(index=ix_FirstName)

   2:  WHERE FirstName = N'Eugene'

Msg 308, Level 16, State 1, Line 1

Index 'ix_FirstName' on table 'DimCustomer' (specified in the FROM clause) does not exist.

What about use the indexid?

   1: SELECT * FROM DimCustomer WITH(index=5)

   2:  WHERE FirstName = N'Eugene'

Msg 307, Level 16, State 1, Line 1

Index ID 5 on table 'DimCustomer' (specified in the FROM clause) does not exist.

So how we can create a new query plan that consider this index?

Now the fun stuff.

The DBCC AUTOPILOT is used to tell to the Optimizer to consider an specific index to the query plan creation. This DBCC plus the SET AUTOPILOT ON it’s the way to allow the use of the index.

Following is the syntax of the commands:

   1: SET AUTOPILOT ON|OFF

   2:  

   3: /*

   4:   DBCC TRACEON (2588)

   5:   DBCC HELP('AUTOPILOT')

   6: */

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

Following is a complete scenario with an sample of the commands:

   1: -- Current Cost = 0,762133

   2: -- Clustered Index Scan on pk

   3: SELECT * FROM DimCustomer

   4:  WHERE FirstName = N'Eugene'

   5: GO

   1: -- creating the index 

   2: -- DROP INDEX ix_FirstName ON DimCustomer

   3: CREATE INDEX ix_FirstName ON DimCustomer(FirstName) WITH STATISTICS_ONLY = -1

   4: GO

   5:  

   6: -- Looking at the info necessary in the DBCC AUTOPILOT comand

   7: SELECT name, id, Indid, Dpages, rowcnt 

   8:   FROM sysindexes

   9:  WHERE id = object_id('DimCustomer')

  10: GO

   1: DBCC AUTOPILOT (5, 9, 0, 0, 0, 0, 0) -- Starting with the TypeID 5

   2: DBCC AUTOPILOT (6, 9, 37575172, 1, 0, 0, 0) -- Clustered Index with TypeID 6

   3: DBCC AUTOPILOT (0, 9, 37575172, 2, 0, 0, 0) -- All other index with TypeID 0

   4: DBCC AUTOPILOT (0, 9, 37575172, 3, 0, 0, 0) -- All other index with TypeID 0

   5: DBCC AUTOPILOT (0, 9, 37575172, 5, 0, 0, 0) -- All other index with TypeID 0

   6: GO

   7: SET AUTOPILOT ON

   8: GO

   9: -- Query to create the estimated execution plan with the cost = 0,0750712

  10: SELECT * FROM dbo.DimCustomer

  11:  WHERE FirstName = N'Eugene'

  12: OPTION (RECOMPILE)

  13: GO

  14: SET AUTOPILOT OFF

  15: GO

I realized that you can fool the Optimizer telling different numbers for the Pages and RowCount parameters of the DBCC AUTOPILOT. If you let it zero it use the Clustered index values.

Conclusion

There is a lot of mystery about this features, but I’m sure this will be a good start of tests for you.

I’m still playing with this, so be confortable to ask something or share a new discovery.

I don’t need to tell you to don’t use this is a production environment do I? This is a undocumented stuff so nobody can guaranty what it is really doing unless MS folks make it officially public and documented.

That’s all folks