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

  • Artículo

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

Ordenar columnas en las instrucciones CREATE INDEX

La salida de los componentes de la característica de índices que faltan muestra una lista de las columnas de igualdades, de desigualdades e incluidas.

Por ejemplo, el elemento MissingIndexes del plan de presentación XML indica si una columna de clave de índice se utiliza para búsquedas de igualdad (=) o desigualdad (<, >, etc.) en el predicado de la instrucción Transact-SQL, o si sólo se incluye para cubrir una consulta. Muestra esta información como uno de los siguientes valores del 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 igualdades, desigualdades o incluida. El conjunto de resultados de sys.dm_db_missing_index_details devuelve esta información en las columnas equality_columns, inequality_columns e included_columns. El conjunto de resultados devuelto por sys.dm_db_missing_index_columns devuelve esta información en su columna column_usage.

Use las siguientes directrices para ordenar columnas en las instrucciones CREATE INDEX que se escriben a partir de la salida de la característica de índices que faltan:

  • Enumere primero las columnas de igualdades (extremo izquierdo de la lista de columnas).
  • Enumere las columnas de desigualdades después de las columnas de igualdades (a la derecha de las columnas de igualdades que aparecen en la lista).
  • Enumere las columnas incluidas en la cláusula INCLUDE de la instrucción CREATE INDEX.
  • Para determinar un orden eficaz de las columnas de igualdades, ordénelas según su selectividad; es decir, ponga primero en la lista las columnas más selectivas.

Ejemplos

Usar la salida del elemento MissingIndexes del plan de presentación XML

La característica de índices que faltan utiliza la información que el optimizador de consultas genera de forma automática cuando optimiza una consulta. Sin embargo, primero se deben ejecutar las consultas en la instancia de SQL Server, de modo que el optimizador pueda generar esta información de índices que faltan.

El siguiente ejemplo 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:

  1. Active la característica del plan de presentación XML mediante la opción SET STATISTICS XML ON y ejecute la siguiente consulta en la base de datos de ejemplo 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. Examine la salida devuelta en el elemento MissingIndexes del plan de presentación generado:
    <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 que falta utilizando la información devuelta en los elementos MissingIndex y ColumnGroup para escribir una instrucción CREATE INDEX DDL, de la siguiente forma:

    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 utiliza la base de datos (USE AdventureWorks), el esquema y el nombre de tabla (ON Sales.SalesOrderHeader) que aparecen en la lista del elemento MissingIndex. También utiliza las columnas de la lista para cada subelemento ColumnGroup de 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ámica

Antes de recuperar la información de índices que faltan, debe ejecutar las consultas en la instancia de SQL Server de forma que el optimizador de consultas pueda generar la información de índices que faltan.

El siguiente ejemplo 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 siguiente consulta en la base de datos de ejemplo AdventureWorks:

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

  2. Ejecute la siguiente consulta en la vista de administración dinámica sys.dm_db_missing_index_details:

    SELECT *
FROM sys.dm_db_missing_index_details

    Consultar esta vista de administración dinámica devuelve los siguientes resultados:

index_handle database_id object_id equality_columns inequality_columns included_columns statement

1

6

53575229

[StateProvince]

NULL

[City], [PostalCode]

[AdventureWorks].[Person].[Address]

  1. Una vez que se ha consultado la vista de administración dinámica sys.dm_db_missing_index_details, puede crear el índice que falta utilizando la información devuelta en las columnas equality_columns, included_columns y statement, de la siguiente forma:

    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 ha devuelto ninguna columna inequality_columns en el conjunto de resultados sys.dm_db_missing_index_details. Si se hubiesen devuelto, se pondrían en la lista después de las columnas equality_columns. Las columnas devueltas en included_columns siempre aparecen en la lista de la cláusula INCLUDE de la instrucción CREATE INDEX.

Vea también

Conceptos

Planes de presentación XML
Acerca de la característica de índices que faltan
Índices

Otros recursos

SET STATISTICS XML (Transact-SQL)
CREATE INDEX (Transact-SQL)
sys.dm_db_missing_index_details
sys.dm_db_missing_index_columns

Ayuda e información

Obtener ayuda sobre SQL Server 2005