• Artículo

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 índices que faltan incluyen la lista igualdad, desigualdad y las columnas incluidas en su salida.

Por ejemplo, el elemento MissingIndexes de 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 Transact-SQL, o simplemente está incluido para cubrir una consulta. Muestra esta información como uno de los valores siguientes sobre el atributo Usage del subelemento ColumnGroup:

<ColumnGroup Usage="EQUALITY">

<ColumnGroup Usage="INEQUALITY">

<ColumnGroup Usage="INCLUDE">

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 Data Definition Language (DDL) a partir de la información devuelta por el elemento MissingIndexes:

  1. Active la característica del Plan de presentación de XML usando la opción SET STATISTICS XML ON y ejecute la consulta siguiente contra la base de datos de ejemplo de AdventureWorks:

    USE AdventureWorks;
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

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

    <MissingIndexes>

      <MissingIndexGroup Impact="95.8296">

        <MissingIndex Database="[AdventureWorks]" 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>

  3. Cree el índice perdido usando información devuelta en los elementos MissingIndex y ColumnGroup para escribir una instrucción CREATE INDEX DDL de la siguiente manera:

    USE AdventureWorks;
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 AdventureWorks), 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 clave (TerritoryID, ShipMethodID, SubTotal, Freight) y columnas sin clave (INCLUDE (SalesOrderNumber, CustomerID)).

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

  1. Ejecute la consulta siguiente contra la base de datos de ejemplo de AdventureWorks:

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

  2. Ejecute la consulta siguiente contra 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]

  1. 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_columnsy statement de la siguiente manera:

    USE AdventureWorks;
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.