Artigo
04/01/2012

Usando informações de índice ausente para gravar instruções CREATE INDEX

Este tópico contém diretrizes e exemplos sobre a utilização de informações retornadas pelos componentes de recurso de índices ausentes para gravar instruções CREATE INDEX para os índices ausentes.

Ordenando colunas em instruções CREATE INDEX

Os componentes do recurso de índices ausentes listam colunas incluídas, de igualdade e de desigualdade em sua saída.

Por exemplo, o elemento MissingIndexes do plano de execução XML indica se uma coluna de chave de índice é usada por igualdade (=) ou desigualdade (<, > e assim por diante) no predicado da instrução Transact-SQL ou se foi incluída apenas para abranger uma consulta. Ele exibe essas informações como um dos seguintes valores para o atributo Usage do subelemento ColumnGroup:

Os objetos de gerenciamento dinâmico sys.dm_db_missing_index_details e sys.dm_db_missing_index_columns retornam resultados que indicam se uma coluna de chave de índice é uma coluna incluída, de igualdade ou de desigualdade. O conjunto de resultados do sys.dm_db_missing_index_details retorna essas informações nas colunas equality_columns, inequality_columns e included_columns. O conjunto de resultados retornado por sys.dm_db_missing_index_columns retorna essas informações em sua coluna column_usage.

Use as seguintes diretrizes para solicitar colunas nas instruções CREATE INDEX gravadas usando a saída de componente do recurso de índices ausentes:

Liste as colunas de igualdade primeiro (na extremidade esquerda da lista de colunas).
Liste as colunas de desigualdade depois das colunas de igualdade (à direita das colunas de igualdade listadas).
Liste as colunas incluídas na cláusula INCLUDE da instrução CREATE INDEX.
Para determinar uma ordem efetiva para as colunas de igualdade, ordene-as com base em sua seletividade; ou seja, liste as colunas mais seletivas primeiro.

Exemplos

Usando a saída do elemento MissingIndexes do plano de execução XML

O recurso de índices ausentes se beneficia de informações que o otimizador de consulta gera automaticamente ao otimizar uma consulta. Contudo, as consultas devem ser executadas primeiro na instância do SQL Server, para que o otimizador possa gerar as informações de índice ausente.

O seguinte exemplo mostra como criar uma instrução DDL (linguagem de definição de dados) das informações retornadas pelo elemento MissingIndexes:

Ative o recurso Plano de execução XML usando a opção SET STATISTICS XML ON e execute a seguinte consulta no banco de dados de exemplo AdventureWorks2008R2:

USE AdventureWorks2008R2;
GO
SET STATISTICS XML ON;
GO
SELECT CustomerID, SalesOrderNumber, SubTotal
FROM Sales.SalesOrderHeader
WHERE ShipMethodID > 2
AND SubTotal > 500.00
AND Freight < 15.00
AND TerritoryID = 5;
GO

Exiba a saída retornada no elemento MissingIndexes do plano de execução produzido:

<MissingIndexes>

  <MissingIndexGroup Impact="95.8296">

    <MissingIndex Database="[AdventureWorks2008R2]" Schema="[Sales]" Table="[SalesOrderHeader]">

      <ColumnGroup Usage="EQUALITY">

        <Column Name="[TerritoryID]" ColumnId="14" />

      </ColumnGroup>

      <ColumnGroup Usage="INEQUALITY">

        <Column Name="[ShipMethodID]" ColumnId="17" />

        <Column Name="[SubTotal]" ColumnId="21" />

        <Column Name="[Freight]" ColumnId="23" />

      </ColumnGroup>

      <ColumnGroup Usage="INCLUDE">

        <Column Name="[SalesOrderNumber]" ColumnId="8" />

        <Column Name="[CustomerID]" ColumnId="11" />

      </ColumnGroup>

    </MissingIndex>

  </MissingIndexGroup>

</MissingIndexes>
Crie o índice ausente usando informações retornadas nos elementos MissingIndex e ColumnGroup, para gravar uma instrução CREATE INDEX DDL, como se segue:
```
USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
           WHERE name = N'IX_SalesOrderHeader_TerritoryID')
     DROP INDEX IX_SalesOrderHeader_TerritoryID ON Sales.SalesOrderHeader;
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_TerritoryID
     ON Sales.SalesOrderHeader (TerritoryID, ShipMethodID, SubTotal, Freight)
     INCLUDE (SalesOrderNumber, CustomerID);
GO
```
Essa instrução CREATE INDEX usa o banco de dados (USE AdventureWorks2008R2), o esquema e o nome de tabela (ON Sales.SalesOrderHeader) listados no elemento MissingIndex. Ela também usa as colunas listadas para cada subelemento ColumnGroup para as colunas de chaves (TerritoryID, ShipMethodID, SubTotal, Freight) e colunas não chave (INCLUDE (SalesOrderNumber, CustomerID)).

Usando resultados retornados por um objeto de gerenciamento dinâmico

Antes de recuperar informações de índice ausente, você deve executar consultas na instância do SQL Server, de forma que o otimizador de consulta possa gerar as informações de índice ausente.

O seguinte exemplo mostra como criar uma instrução DDL a partir das informações retornadas pela exibição de gerenciamento dinâmico sys.dm_db_missing_index_details:

Execute a seguinte consulta no banco de dados de exemplo AdventureWorks2008R2:

USE AdventureWorks2008R2;
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 9;
GO

Execute a seguinte consulta na exibição de gerenciamento dinâmico sys.dm_db_missing_index_details:
```
SELECT *
FROM sys.dm_db_missing_index_details
```
A consulta dessa exibição de gerenciamento dinâmico retorna os seguintes resultados:

index_handle	database_id	object_id	equality_columns	inequality_columns	included_columns	statement
1	6	53575229	[StateProvince]	NULL	[City], [PostalCode]	[AdventureWorks].[Person].[Address]

Depois de consultar a exibição de gerenciamento dinâmico sys.dm_db_missing_index_details, você pode criar o índice ausente usando as informações retornadas nas colunas equality_columns, included_columns e instrução, como se segue:

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
           WHERE name = N'IX_PersonAddress_StateProvinceID')
     DROP INDEX IX_PersonAddress_StateProvinceID ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_PersonAddress_StateProvinceID
     ON Person.Address (StateProvinceID)
     INCLUDE (City, PostalCode);
GO

Nesse exemplo, não foi retornada nenhuma inequality_columns no conjunto de resultados sys.dm_db_missing_index_details. Se as colunas tivessem sido retornadas, você as listaria após as equality_columns. As colunas retornadas em included_columns sempre são listadas na cláusula INCLUDE da instrução CREATE INDEX.