Início > Não categorizado > Undocumented,"STATISTICS_ONLY", "DBCC AUTOPILOT" and "SET AUTOPILOT"

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

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:

USE AdventureWorksDW
GO
CREATE INDEX ix_FirstName ON DimCustomer(FirstName) WITH STATISTICS_ONLY = -1
GO

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

sp_HelpIndex DimCustomer

image

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 :

SELECT * FROM DimCustomer WITH(index=ix_FirstName)

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?

SELECT * FROM DimCustomer WITH(index=5)

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:

SET AUTOPILOT ON|OFF

/*

  DBCC TRACEON (2588)

  DBCC HELP(‘AUTOPILOT’)

*/

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

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

– Current Cost = 0,762133

– Clustered Index Scan on pk

SELECT * FROM DimCustomer

WHERE FirstName = N’Eugene’

GO

image

– creating the index

– DROP INDEX ix_FirstName ON DimCustomer

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

GO

 
– Looking at the info necessary in the DBCC AUTOPILOT comand

SELECT name, id, Indid, Dpages, rowcnt

  FROM sysindexes

WHERE id = object_id(‘DimCustomer’)

GO

image

DBCC AUTOPILOT (5, 9, 0, 0, 0, 0, 0) — Starting with the TypeID

DBCC AUTOPILOT (6, 9, 37575172, 1, 0, 0, 0) — Clustered Index with TypeID 6

DBCC AUTOPILOT (0, 9, 37575172, 2, 0, 0, 0) — All other index with TypeID 0

DBCC AUTOPILOT (0, 9, 37575172, 3, 0, 0, 0) — All other index with TypeID 0

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

GO

SET AUTOPILOT ON

GO

– Query to create the estimated execution plan with the cost = 0,0750712

SELECT * FROM dbo.DimCustomer

WHERE FirstName = N’Eugene’

OPTION (RECOMPILE)

GO

SET AUTOPILOT OFF

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

About these ads
CategoriasNão categorizado
  1. Nenhum comentário ainda.
  1. 31 de maio de 2012 às 16:44

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Seguir

Obtenha todo post novo entregue na sua caixa de entrada.

Junte-se a 60 outros seguidores

%d blogueiros gostam disto: