Blog Archive

 

 Fabiano N. Amorim

 

  

 

 

SolidQ Courses
 
Fabiano Amorim

Undocumented,"STATISTICS_ONLY", "DBCC AUTOPILOT" and "SET AUTOPILOT"

2011-02-03 19:34:13 por Fabiano Amorim

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 Smiley triste.

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

image

   1: DBCC SHOW_STATISTICS(DimCustomer, ix_FirstName)

image

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. Alegre

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

image

 

   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

image

   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

 

image

image

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. Alegre

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

Comments

#re: Undocumented,"STATISTICS_ONLY", "DBCC AUTOPILOT" and "SET AUTOPILOT"
segunda-feira, fevereiro 7, 2011 - 12:29 by Felipe Ferreira
NICE! very good info.. this can help me a lot in the fasttrack environments.. it became a favorite right now! will test it later.. cheers
#re: Undocumented,"STATISTICS_ONLY", "DBCC AUTOPILOT" and "SET AUTOPILOT"
quarta-feira, março 9, 2011 - 10:10 by Luti
Muito legal! Eu sei onde toda essa história começou hahahahaha! []s Luti
Leave a Comment
(*) Title:
(*) Name:
Your URL:
(*) Comments:
Follow us on: