Problemas de performance con los Hypothetical Indexes (índices hipotéticos)

Los índices hipotéticos (Hypothetical Indexes) son creados por el asistente para la optimización de índices (Index tuning wizard o Database Engine Tuning Advisor) cuando intenta determinar que tablas y que columnas se pueden utilizar en un índice para optimizar las consultas.

Por lo general, se borran todos los índices hipotéticos cuando se sale del asistente para optimización de índices. Esto sucede sin problemas cuando termina limpiamente, pero si falla, cancela, o no termina correctamente, entonces estos índices hipotéticos pueden quedar en la base de datos.

El problema de rendimiento originado por los índices hipotéticos.

Cuando se realiza una consulta o se ejecuta un procedimiento almacenado existe normalmente una compilación inicial que permite determinar un plan de ejecución optimizado de acuerdo a la distribución y las características de los datos y la consulta utilizada. Como los índices hipotéticos no se actualizan, existe una gran posibilidad de que los índices creados luego de un tiempo contengan información desactualizada que no representa fielmente los datos. Como el optimizador utiliza parte de la información de estos índices hipotéticos, incorrectamente determina que se requiere una recompilación. Durante los siguientes recompilaciones, la información de los índices hipotéticos nunca se actualiza, por lo que el optimizador permanece recompilando el plan de ejecución y usando información estadística vieja.

Los índices hipotéticos pueden dificultar la generación de un plan de ejecución optimizado para la consulta. Por lo tanto, la eliminación de los índices hipotéticos es la mejor alternativa.

Como eliminar los índices Hipotéticos

Para determinar si un índice es hipotético se puede usar la función INDEXPROPERTY(<table_id>,<índ_name>,IsHypothetical) que devuelve 1 si el índice es hipotético.

El siguiente query genera un script para eliminar los índices hipotéticos.

SET NOCOUNT ON
DECLARE @sSql NVARCHAR(1024)
DECLARE @objid INT
DECLARE @indid TINYINT
DECLARE HypIndexCursor CURSOR FOR 
    SELECT id, indid 
    FROM sysindexes 
    WHERE 
            INDEXPROPERTY(id, name, 'IsHypothetical') = 1
            --AND name LIKE 'hind_%'         
    ORDER BY name

OPEN HypIndexCursor
FETCH NEXT FROM HypIndexCursor INTO @objid, @indid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    SELECT @sSql = (
            SELECT 
                CASE WHEN INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 
                    THEN 'DROP STATISTICS ' 
                ELSE 'DROP INDEX ' 
                END 
                + QUOTENAME(OBJECT_NAME(i.id)) + '.' + QUOTENAME(i.name)
            FROM sysindexes i 
            JOIN sysobjects o 
                ON i.id = o.id
            WHERE     i.id = @objid 
            AND     i.indid = @indid 
            )
    
    --EXEC(@sSql)
    PRINT @sSql
    FETCH NEXT FROM HypIndexCursor INTO @objid, @indid
END
CLOSE HypIndexCursor
DEALLOCATE HypIndexCursor