Artículo
03/30/2012

Utilizar información de índices que faltan para escribir instrucciones CREATE INDEX

Este tema contiene directrices y ejemplos de uso de la información devuelta por la característica de los componentes de índices que faltan para escribir instrucciones CREATE INDEX para los índices que faltan.

Ordenar las columnas en instrucciones CREATE INDEX

Los componentes de la característica de índices que faltan muestran las columnas de igualdad, desigualdad e incluidas en su salida.

Por ejemplo, el elemento MissingIndexes del plan de presentación XML indica si una columna de clave de índice se utiliza para igualdad (=) o desigualdad (, <, >, etc.) en el predicado de la instrucción de Transact-SQL, o simplemente se incluye para cubrir una consulta. Muestra esta información como uno de los valores siguientes para el atributo Usage del subelemento ColumnGroup:

Los objetos de administración dinámica sys.dm_db_missing_index_details y sys.dm_db_missing_index_columns devuelven resultados que indican si una columna de clave de índice es una columna de igualdad, desigualdad o incluida. El conjunto de resultados de sys.dm_db_missing_index_details devuelve esta información en las columnas equality_columns, inequality_columns y included_columns. El conjunto de resultados devueltos por sys.dm_db_missing_index_columns devuelve esta información en su columna column_usage.

Siga las directrices siguientes para ordenar las columnas en las instrucciones CREATE INDEX que escribe desde la salida de componentes de la característica de índices que faltan:

Haga una lista primero (del extremo izquierdo en la lista de columnas) de las columnas de igualdad.
Haga una lista de las columnas de desigualdad después de las columnas de igualdad (a la derecha de columnas de igualdad mostrada).
Enumere las columnas incluidas en la cláusula INCLUDE de la instrucción CREATE INDEX.
Para determinar un orden efectivo para las columnas de igualdad, ordénelas en función de su selectividad; es decir, mostrando primero las columnas más selectivas.

Ejemplos

Usar la salida del elemento XML Showplan MissingIndexes

La característica de los índices que faltan aprovechan la información que el optimizador de consultas genera automáticamente cuando optimiza una consulta. Sin embargo, las consultas se deben ejecutar primero en la instancia de SQL Server para que el optimizador pueda generar esta información de índice que falta.

El ejemplo siguiente muestra cómo crear una instrucción del lenguaje de definición de datos (DDL) a partir de la información devuelta por el elemento MissingIndexes:

Active la característica del plan de presentación de XML usando la opción SET STATISTICS XML ON y ejecute la consulta siguiente con la base de datos de ejemplo de 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

Vea la salida devuelta en el elemento MissingIndexes del plan de presentación que se genera:

<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>
Cree el índice perdido con la información devuelta en los elementos MissingIndex y ColumnGroup para escribir una instrucción CREATE INDEX de la siguiente manera:
```
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
```
Esta instrucción CREATE INDEX usa la base de datos (USE AdventureWorks2008R2), el esquema y el nombre de tabla (ON Sales.SalesOrderHeader) que se muestran en el elemento MissingIndex. También usa las columnas mostradas para cada subelemento ColumnGroup para las columnas de clave (TerritoryID, ShipMethodID, SubTotal, Freight) y columnas sin clave (INCLUDE (SalesOrderNumber, CustomerID)).

Usar los resultados devueltos por un objeto de administración dinámico

Para poder recuperar información de índice que falta, debe ejecutar consultas en la instancia de SQL Server para que el optimizador de consultas pueda generar la información de índice que falta.

El ejemplo siguiente muestra cómo crear una instrucción DDL a partir de la información devuelta por la vista de administración dinámica sys.dm_db_missing_index_details:

Ejecute la consulta siguiente con la base de datos de ejemplo de AdventureWorks2008R2:

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

Ejecute la siguiente consulta con la vista de administración dinámica sys.dm_db_missing_index_details:
```
SELECT *
FROM sys.dm_db_missing_index_details
```
Al consultar esta vista de administración dinámica, se devuelven los resultados siguientes:

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

Después de consultar la vista de administración dinámica sys.dm_db_missing_index_details, puede crear el índice que falta usando la información devuelta en las columnas equality_columns, included_columns y statement de la siguiente manera:

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

En este ejemplo, no se devolvió inequality_columns en el conjunto de resultados de sys.dm_db_missing_index_details. Si se hubieran devuelto, haría una lista de esas columnas después de equality_columns. Las columnas que se devuelven en included_columns siempre se enumeran en la cláusula INCLUDE de la instrucción CREATE INDEX.