Problemas con las DMV de Indices

Deseo compartir con ustedes este excelente artículo publicado por Paul Randall, http://www.sqlskills.com/BLOGS/PAUL/post/Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx

Existe un detalle con las DMV de índices no existentes que podría causarle muchos problemas cuando está analizando consultas con el plan de ejecución. Este es el problema: el código de índice faltante podría recomendar un índice non clustered que ya existe, una y otra vez. Además podría recomendar un índice que no ayuda en la consulta. Este caso ya ha sido reportado  (Connect item #416197) y será corregido en SQL11 pero no en versiones anteriores.

Este es un ejemplo utilizando SQL Server 2008 SP1.

Estos son los pasos para reproducirlo:

CREATE TABLE t1 (
    c1 INT IDENTITY,
    c2 AS c1 * 2,
    c3 AS c1 + c1,
    c4 CHAR (3000) DEFAULT 'a');
GO
CREATE UNIQUE CLUSTERED INDEX t1_clus ON t1 (c1);
GO

SET NOCOUNT ON;
GO
INSERT INTO t1 DEFAULT VALUES;
GO 100000

Esto crea una tabla con muchas filas, y ahora ejecutamos la siguiente consulta: 

SELECT COUNT (*) FROM t1
    WHERE c2 BETWEEN 10 AND 1000
    AND c3 > 1000;
GO

Se despliega el plan de ejecusión

...Me recomienda crear un índice:

Ahora creamos este índice:

CREATE NONCLUSTERED INDEX [_missing_c2_c3] ON [dbo].[t1] ([c2],[c3]);
GO

Ahora creamos este código de ejemplo para fines ilustrativos, es cierto no debería utilizar cursores pero es solo para fines ilustrativos.

DECLARE testcursor CURSOR FOR
    SELECT c1 FROM t1
    WHERE
        c2 BETWEEN 10 AND 1000
        AND c3 > 1000;

DECLARE @var BIGINT;

OPEN testcursor;

FETCH NEXT FROM testcursor INTO @var;

WHILE (@@fetch_status <> -1)
BEGIN
    -- empty body
    FETCH NEXT FROM testcursor INTO @var;
END

CLOSE testcursor;
DEALLOCATE testcursor;

Se despliega el plan de ejecución 

Hmmm, parece que me está solicitando el crear el mismo índice anterior (aunque está solicitando que c1 debe ser incluido, aunque ya existe un índice non cluster con  c1 como cluster key, el cual es incluído de forma automática). Bueno, voy a crear el índice que solicita:

CREATE NONCLUSTERED INDEX [_missing_c2_c3_inc_c1] ON [dbo].[t1] ([c2],[c3]) INCLUDE ([c1]);
GO

Y no cambia nada, siempre continúa solicitando el índice.  El índice no es utilizado por el *Key Lookup* en el plan de ejecución – pero el missing index code piensa que sería útil y lo sugiere. Este índice no ayudará en el Key Lookup, además ya existe!

Si utiliza una consulta que sumariza la salida del missing index DMV (tal y como lo hace Bart Duncan's excellent script) y si usted tiene consultas comunes en su sistema que se encuentran con esta características de las DMV entonces sus agregaciones podrían estar incorrectas.

 

Saludos,

 

Ing. Eduardo Castro, SQL Server MVP

Note: Cross posted from Eduardo Castro.

Permalink