Ajuste de índices no agrupados con sugerencias de índices que faltan

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

La característica de índices que faltan es una herramienta ligera para encontrar aquellos índices que faltan que podría mejorar significativamente el rendimiento de las consultas. En este artículo se describe cómo usar las sugerencias de índices que faltan para ajustar los índices de forma eficaz y mejorar el rendimiento de las consultas.

Limitaciones de la característica de índices que faltan

Cuando el optimizador de consultas genera un plan de consulta, analiza cuáles son los mejores índices para una condición de filtro concreta. Si no existen los mejores índices, el optimizador de consultas sigue generando un plan de consulta mediante los métodos de acceso menos costosos disponibles, pero también almacena información sobre estos índices. La característica de índices que faltan le permite tener acceso a la información acerca de los mejores índices posibles para poder decidir si deberían implementarse.

La optimización de consultas es un proceso que depende del tiempo, por lo que hay limitaciones en la característica de índices que faltan. Entre las limitaciones se incluyen:

• Las sugerencias de índices que faltan se basan en las estimaciones realizadas durante la optimización de una sola consulta, antes de la ejecución de la consulta. Las sugerencias de índices que faltan no se prueban ni actualizan después de la ejecución de la consulta.
• La característica de índices que faltan sugiere solo índices de almacén de filas no agrupados basados en disco. No se sugieren índices únicos ni filtrados.
• Se sugieren columnas de clave, pero la sugerencia no especifica un orden para esas columnas. Para obtener información sobre cómo ordenar columnas, consulte la sección Aplicar sugerencias de índices que faltan de este artículo.
• Se sugieren columnas incluidas, pero SQL Server no realiza ningún análisis de costo-beneficio con respecto al tamaño del índice resultante cuando se sugiere un gran número de columnas incluidas.
• Las solicitudes de índices que faltan pueden ofrecer variaciones similares de índices en la misma tabla y columna en todas las consultas. Es importante revisar las sugerencias de índices y combinarlas siempre que sea posible.
• No se realizan sugerencias para planes de consulta triviales.
• En las consultas que implican solo predicados de desigualdad, la información de costos es menos precisa.
• Se recopilan las sugerencias para un máximo de 600 grupos de índices que faltan. Una vez alcanzado este umbral, no se recopilan más datos del grupo de índices que faltan.

Debido a estas limitaciones, las sugerencias de índices que faltan se tratan mejor como uno de varios orígenes de información al realizar análisis de índices, diseño, ajuste y pruebas. Las sugerencias de índices que faltan no son recomendaciones para crear índices exactamente como se sugiere.

Nota:

Azure SQL Database ofrece el ajuste automático de índices. El ajuste automático de índices usa el aprendizaje automático para aprender horizontalmente de todas las bases de datos de Azure SQL Database a través de la inteligencia artificial y mejorar dinámicamente sus acciones de ajuste. El ajuste automático de índices incluye un proceso de comprobación para asegurarse de que haya una mejora positiva en el rendimiento de la carga de trabajo de los índices creados.

Visualización de las recomendaciones de índices que faltan

La característica de índices que faltan consta de dos componentes:

• El elemento MissingIndexes del XML de los planes de ejecución. Esto le permite correlacionar los índices que el optimizador de consultas considera que faltan con las consultas para las que faltan.
• Un conjunto de vistas de administración dinámica (DMV) que se pueden consultar para devolver información acerca de los índices que faltan. Esto le permite ver todas las recomendaciones de índices que faltan para una base de datos.

Visualización de sugerencias de índices que faltan en los planes de ejecución

Los planes de ejecución de consultas se pueden generar u obtener de varias maneras:

• Al escribir u optimizar una consulta, puede usar SQL Server Management Studio (SSMS) para mostrar el plan de ejecución estimado sin ejecutar la consulta, o ejecutar la consulta y mostrar un plan de ejecución real.
• El Almacén de consultas, cuando se habilita, recopila planes de ejecución.
• Puede identificar los planes de ejecución almacenados en caché consultando DMV como sys.dm_exec_text_query_plan.

Por ejemplo, puede usar la consulta siguiente para generar solicitudes de índices que faltan en la base de datos de ejemplo AdventureWorks.

SELECT City, StateProvinceID, PostalCode  
FROM Person.Address as a
JOIN Person.BusinessEntityAddress as ba on
    a.AddressID = ba.AddressID
JOIN Person.Person as  p on
    ba.BusinessEntityID = p.BusinessEntityID
WHERE p.FirstName like 'K%' and
    StateProvinceID = 9;  
GO 

Para generar y ver las solicitudes de índices que faltan:

1. Abra SSMS y conecte una sesión a la copia de la base de datos de ejemplo AdventureWorks.

2. Pegue la consulta en la sesión y genere un plan de ejecución estimado en SSMS para la consulta seleccionando el botón de la barra de herramientas Mostrar plan de ejecución estimado. El plan de ejecución se mostrará en un panel de la sesión actual. Aparecerá una instrucción de índice que falta verde cerca de la parte superior del plan gráfico.

   

   Un solo plan de ejecución puede contener varias solicitudes de índices que faltan, pero solo se puede mostrar una solicitud de índices que faltan en el plan de ejecución gráfico. Una opción para ver una lista completa de los índices que faltan para un plan de ejecución es ver el XML del plan de ejecución.

3. Haga clic con el botón derecho en el plan de ejecución y seleccione Mostrar XML del plan de ejecución... en el menú.

   

   El XML del plan de ejecución se abrirá como una nueva pestaña dentro de SSMS.

   Nota:

   Solo se mostrará una única sugerencia de índices que faltan en la opción de menú Detalles de índices que faltan..., incluso si hay varias sugerencias en el XML del plan de ejecución. La sugerencia de índices que faltan que se muestra puede no ser la que tenga la mejora estimada más alta para la consulta.

4. Muestre el cuadro de diálogo Buscar mediante el método abreviado CTRL+f.

5. Busque MissingIndex.

   

   En este ejemplo, hay dos elementos MissingIndex.

   • El primer índice que falta sugiere que la consulta podría usar un índice en la tabla Person.Address que admita una búsqueda de igualdad en la columna StateProvinceID, que incluye dos columnas más, City y PostalCode". En el momento de la optimización, el optimizador de consultas consideró que este índice podría reducir el costo estimado de la consulta en un 34,2737 %.
   • El segundo índice que falta sugiere que la consulta podría usar un índice en la tabla Person.Person que admita una búsqueda de desigualdad en la columna FirstName. En el momento de la optimización, el optimizador de consultas consideró que este índice podría reducir el costo estimado de la consulta en un 18,1102 %.

Cada índice no agrupado basado en disco de la base de datos ocupa espacio, agrega sobrecarga para inserciones, actualizaciones y eliminaciones, y puede requerir mantenimiento. Por estos motivos, es un procedimiento recomendado revisar todas las solicitudes de índices que faltan para una tabla y los índices existentes en una tabla antes de agregar un índice basado en un plan de ejecución de consultas.

Visualización de sugerencias de índices que faltan en DMV

Puede recuperar la información acerca de los índices que faltan consultando los objetos de administración dinámica que aparecen en la tabla siguiente.

Vista de administración dinámica	Information entregada
sys.dm_db_missing_index_group_stats (Transact-SQL)	Devuelve información de resumen sobre los grupos de índices que faltan, por ejemplo, las mejoras de rendimiento que se pueden obtener mediante la implementación de un grupo específico de índices que faltan.
sys.dm_db_missing_index_groups (Transact-SQL)	Devuelve información sobre un grupo específico de índices que faltan, como el identificador de grupo y los identificadores de todos los índices que faltan que se encuentran en ese grupo.
sys.dm_db_missing_index_details (Transact-SQL)	Devuelve información detallada sobre un índice que falta; por ejemplo, devuelve el nombre y el identificador de la tabla donde falta el índice, y las columnas y los tipos de columna que deben componer el índice que falta.
sys.dm_db_missing_index_columns (Transact-SQL)	Devuelve información sobre las columnas de la tabla de la base de datos que no tienen índice.

La consulta siguiente usa las DMV de índices que faltan para generar instrucciones CREATE INDEX. Las instrucciones de creación de índices aquí están diseñadas para ayudarle a crear su propio DDL después de examinar todas las solicitudes de la tabla junto con los índices existentes en la tabla.

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1), 
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) 
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' + 
        CONVERT (varchar, mig.index_group_handle) + '_' + 
        CONVERT (varchar, mid.index_handle) + ' ON ' + 
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') + 
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' + 
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON 
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON 
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

Esta consulta ordena las sugerencias por una columna denominada estimated_improvement. La mejora estimada se basa en una combinación de:

• El costo estimado de consulta de las consultas asociadas a la solicitud de índices que faltan.
• El impacto estimado de agregar el índice. Se trata de una estimación de cuánto reduciría el costo de la consulta el índice no agrupado.
• La suma de ejecuciones de operadores de consulta (búsquedas y exámenes) que se han ejecutado para las consultas asociadas a la solicitud de índices que faltan. Como se describe en Conservar los índices que faltan con Almacén de consultas, esta información se borra periódicamente.

Nota:

El script de creación de índices del cuadro de herramientas Tiger de Microsoft examina las DMV de índices que faltan y quita automáticamente los índices sugeridos redundantes, analiza los índices de bajo impacto y genera scripts de creación de índices para su revisión. Como en la consulta anterior, NO ejecuta comandos de creación de índices. El script de creación de índices es adecuado para SQL Server y Azure SQL Managed Instance. Para Azure SQL Database, considere la posibilidad de implementar el ajuste automático de índices.

Revise Limitaciones de la característica de índices que faltan y cómo aplicar sugerencias de índices que faltan antes de crear índices y modifique el nombre del índice para que coincida con la convención de nomenclatura de la base de datos.

Conservar los índices que faltan con Almacén de consultas

Las sugerencias de índices que faltan en las DMV se borran mediante eventos como reinicios de instancias, conmutaciones por error y establecimiento de una base de datos sin conexión. Además, cuando cambian los metadatos para una tabla, se elimina toda la información acerca de los índices que faltan en la tabla de estos objetos de administración dinámica. Los cambios en los metadatos de tabla pueden producirse cuando se agregan o quitan columnas de una tabla, por ejemplo, o cuando se crea un índice en una columna de una tabla. Al realizar una operación ALTER INDEX REBUILD en un índice de una tabla también se borran las solicitudes de índices que faltan para esa tabla.

Del mismo modo, los planes de ejecución almacenados en la caché de planes se borran mediante eventos como reinicios de instancias, conmutaciones por error y establecimiento de una base de datos sin conexión. Los planes de ejecución se pueden quitar de la memoria caché debido a la presión de memoria y las recompilaciones.

Las sugerencias de índices que faltan en los planes de ejecución se pueden conservar en estos eventos habilitando el Almacén de consultas.

La consulta siguiente recupera los 20 planes de consulta principales que contienen solicitudes de índices que faltan del Almacén de consultas en función de una estimación aproximada del total de lecturas lógicas de la consulta. Los datos se limitan a las ejecuciones de consultas en las últimas 48 horas.

SELECT TOP 20
    qsq.query_id,
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,    
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on 
    qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on 
    qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE    
    qsp.query_plan like N'%<MissingIndexes>%'
    and qsrsi.start_time >= DATEADD(HH, -48, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC;
GO

Aplicar sugerencias de índices que faltan

Para usar eficazmente las sugerencias de índices que faltan, siga las directrices para diseñar índices no agrupados. Al ajustar los índices no agrupados con sugerencias de índices que faltan, revise la estructura de la tabla base, combine cuidadosamente los índices, considere el orden de las columnas de clave y revise las sugerencias de columna incluidas.

Revisión de la estructura de la tabla base

Antes de crear índices no agrupados en una tabla en función de las sugerencias de índices que faltan, revise el índice agrupado de la tabla.

Una manera de comprobar un índice agrupado es mediante el procedimiento almacenado del sistema sp_helpindex. Por ejemplo, podemos ver un resumen de los índices de la tabla Person.Address mediante la ejecución de la instrucción siguiente:

exec sp_helpindex 'Person.Address';
GO

Revise la columna index_description. Una tabla solo puede tener un índice agrupado. Si se ha implementado un índice agrupado para la tabla, index_description contendrá la palabra "agrupado".

Si no hay ningún índice agrupado, la tabla es un montón. En este caso, revise si la tabla se creó intencionadamente como un montón para resolver un problema de rendimiento específico. La mayoría de las tablas se benefician de los índices agrupados: a menudo, las tablas se implementan como montones por accidente. Considere la posibilidad de implementar un índice agrupado basado en las directrices para diseñar índices agrupados.

Revise los índices que faltan y los índices existentes para la superposición.

Los índices que faltan pueden ofrecer variaciones similares de índices no agrupados en la misma tabla y columna en todas las consultas. Los índices que faltan también pueden ser similares a los índices existentes en una tabla. Para obtener un rendimiento óptimo, es mejor examinar los índices que faltan y los índices existentes para la superposición y evitar la creación de índices duplicados.

Creación de scripts de índices existentes en una tabla

Una manera de examinar la definición de los índices existentes en una tabla es crear scripts de los índices con Detalles del Explorador de objetos:

1. Conecte el Explorador de objetos a la instancia o base de datos.
2. Expanda el nodo de la base de datos en cuestión en el Explorador de objetos.
3. Expanda la carpeta Tablas .
4. Expanda la tabla para la que desea generar scripts de índices.
5. Seleccione la carpeta Índices.
6. Si el panel Detalles del Explorador de objetos no está abierto, en el menú Ver, seleccione Detalles del Explorador de objetos o presione F7.
7. Seleccione todos los índices enumerados en el panel Detalles del Explorador de objetos con el método abreviado CTRL+a.
8. Haga clic con el botón derecho en la región seleccionada y elija la opción de menú Crear script de índice como y, después, CREATE To y Nueva ventana del Editor de consultas.

Revisión de índices y combinación siempre que sea posible

Revise las recomendaciones de índices que faltan para una tabla como grupo, junto con las definiciones de índices existentes en la tabla. Recuerde que, al definir índices, las columnas de igualdad generalmente deben colocarse delante de las columnas de desigualdad y, juntos, deben formar la clave del índice. Para determinar un orden efectivo para las columnas de igualdad, ordénelas en función de su selectividad, mostrando primero las columnas más selectivas (en la parte izquierda de la lista de columnas). Las columnas únicas son más selectivas, mientras que las columnas con muchos valores repetidos son menos selectivas.

Las columnas incluidas deben agregarse a la instrucción CREATE INDEX mediante la cláusula INCLUDE. El orden de las columnas incluidas no afecta al rendimiento de las consultas. Por lo tanto, al combinar índices, las columnas incluidas se pueden combinar sin preocuparse por el orden. Obtenga más información en las directrices de columnas incluidas.

Por ejemplo, puede tener una tabla, Person.Address, con un índice existente en la columna de clave StateProvinceID. Es posible que vea recomendaciones de índices que faltan para la tabla Person.Address para las columnas siguientes:

• Filtros EQUALITY para StateProvinceID y City
• Filtros EQUALITY para StateProvinceID y City, INCLUDE PostalCode

Al modificar el índice existente para que coincida con la segunda recomendación, es probable que un índice con claves en StateProvinceID y City, incluyendo PostalCode, satisfaga probablemente las consultas que generaron ambas sugerencias de índice.

Las compensaciones son comunes en el ajuste de índices. Es probable que para muchos conjuntos de datos, la columna City sea más selectiva que la columna StateProvinceID. Sin embargo, si el índice existente en StateProvinceIDse usa mucho y otras solicitudes buscan principalmente en StateProvinceID y City, generalmente supone una sobrecarga menor para la base de datos tener un índice único con ambas columnas en la clave, iniciando en StateProvinceID, aunque no es la columna más selectiva.

Los índices se pueden modificar de varias maneras:

• Puede usar la instrucción CREATE INDEX con la cláusula DROP_EXISTING. Es posible que desee cambiar el nombre de los índices después de la modificación para que el nombre describa con precisión la definición del índice, en función de la convención de nomenclatura.
• Puede usar la instrucción DROP INDEX (Transact-SQL) seguida de una instrucción CREATE INDEX.

El orden de las claves de índice es importante al combinar las sugerencias de índice: City como columna inicial es diferente de StateProvinceID como columna inicial. Obtenga más información en las directrices para diseñar índices no agrupados.

Al crear índices, considere la posibilidad de usar operaciones de índice en línea cuando estén disponibles.

Aunque los índices pueden mejorar considerablemente el rendimiento de las consultas en algunos casos, también tienen costos de administración y sobrecarga. Revise las directrices generales para el diseño de índices para ayudar a evaluar las ventajas de los índices antes de crearlos.

Compruebe si el cambio de índice se ha realizado correctamente.

Es importante confirmar si los cambios de índice se han realizado correctamente: ¿el optimizador de consultas usa los índices?

Una manera de validar los cambios de índice es usar el Almacén de consultas para identificar las consultas en las que faltan solicitudes de índice. Tenga en cuenta el query_id para las consultas. Use la vista Consultas con seguimiento del Almacén de consultas para comprobar si los planes de ejecución han cambiado para una consulta y si el optimizador usa el índice nuevo o modificado. Obtenga más información sobre las Consultas con seguimiento en el inicio con la solución de problemas de rendimiento de consultas.

Contenido relacionado

Obtenga más información sobre el ajuste de índices y del rendimiento en los siguientes artículos:

• Guía de diseño y de arquitectura de índices de SQL Server y Azure SQL
• sys.dm_db_missing_index_details (Transact-SQL)
• Supervisión del rendimiento mediante el Almacén de consultas