O índice na coluna computada persistente precisa de pesquisa de chave para obter colunas na expressão computada

Por que uma pesquisa de chave é necessária para obter A, B e C quando eles não são referenciados na consulta? Eu suponho que eles estão sendo usados ​​para calcular o Comp, mas por quê?

As colunas A, B, and C são referenciadas no plano de consulta - elas são usadas pela busca em T2.

Além disso, por que a consulta pode usar o índice em t2, mas não em t1?

O otimizador decidiu que a varredura do índice clusterizado era mais barata do que a varredura do índice não clusterizado filtrado e, em seguida, realizar uma pesquisa para recuperar os valores das colunas A, B e C.

Explicação

A verdadeira questão é por que o otimizador sentiu a necessidade de recuperar A, B e C para a busca de índice. Esperamos que ele leia a Compcoluna usando uma varredura de índice não clusterizado e, em seguida, execute uma busca no mesmo índice (alias T2) para localizar o registro Top 1.

O otimizador de consulta expande as referências de coluna computadas antes do início da otimização, para permitir que ele avalie os custos de vários planos de consulta. Para algumas consultas, expandir a definição de uma coluna computada permite que o otimizador encontre planos mais eficientes.

Quando o otimizador encontra uma subconsulta correlacionada, ele tenta 'desenrolá-la' para uma forma sobre a qual acha mais fácil raciocinar. Se não conseguir encontrar uma simplificação mais eficaz, ele recorre à reescrita da subconsulta correlacionada como um apply (uma junção correlacionada):

Acontece que esse desdobramento de aplicação coloca a árvore de consulta lógica em um formato que não funciona bem com a normalização do projeto (um estágio posterior que procura corresponder expressões gerais a colunas computadas, entre outras coisas).

No seu caso, a forma como a consulta é escrita interage com os detalhes internos do otimizador, de modo que a definição da expressão expandida não corresponda à coluna computada e você acaba com uma busca que faz referência a colunas A, B, and Cem vez da coluna computada, Comp. Esta é a causa raiz.

Gambiarra

Uma ideia para contornar esse efeito colateral é escrever a consulta como uma aplicação manualmente:

SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
CROSS APPLY
(  
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Infelizmente, esta consulta não usará o índice filtrado como esperamos. O teste de desigualdade na coluna Ddentro do apply rejeita NULLs, então o predicado aparentemente redundante WHERE T1.D IS NOT NULLé otimizado.

Sem esse predicado explícito, a lógica de correspondência de índice filtrado decide que não pode usar o índice filtrado. Há várias maneiras de contornar esse segundo efeito colateral, mas a mais fácil é provavelmente alterar a aplicação cruzada para uma aplicação externa (espelhando a lógica da reescrita que o otimizador executou anteriormente na subconsulta correlacionada):

SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
OUTER APPLY
(  
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Agora, o otimizador não precisa usar a própria reescrita de aplicação (portanto, a correspondência de coluna calculada funciona como esperado) e o predicado também não é otimizado, portanto, o índice filtrado pode ser usado para ambas as operações de acesso a dados e a busca usa a Compcoluna em ambos os lados:

This would generally be preferred over adding A, B, and C as INCLUDEd columns in the filtered index, because it addresses the root cause of the problem, and does not require widening the index unnecessarily.

Persisted computed columns

As a side note, it is not necessary to mark the computed column as PERSISTED, if you don't mind repeating its definition in a CHECK constraint:

CREATE TABLE dbo.T 
(   
    ID integer IDENTITY(1, 1) NOT NULL,
    A varchar(20) NOT NULL,
    B varchar(20) NOT NULL,
    C varchar(20) NOT NULL,
    D date NULL,
    E varchar(20) NULL,
    Comp AS A + '-' + B + '-' + C,

    CONSTRAINT CK_T_Comp_NotNull
        CHECK (A + '-' + B + '-' + C IS NOT NULL),

    CONSTRAINT PK_T_ID 
        PRIMARY KEY (ID)
);

CREATE NONCLUSTERED INDEX IX_T_Comp_D
ON dbo.T (Comp, D) 
WHERE D IS NOT NULL;

The computed column is only required to be PERSISTED in this case if you want to use a NOT NULL constraint or to reference the Comp column directly (instead of repeating its definition) in a CHECK constraint.

Although this might be a bit of a co-incidence due to the artificial nature of your test data, being as you mentioned SQL 2012 I tried a rewrite:

SELECT  ID,
        Comp,
        D,
        D2 = LEAD(D) OVER(PARTITION BY COMP ORDER BY D)
FROM    dbo.T 
WHERE   D IS NOT NULL
ORDER BY Comp;

Isso gerou um bom plano de baixo custo usando seu índice e com leituras significativamente mais baixas do que as outras opções (e os mesmos resultados para seus dados de teste).

Suspeito que seus dados reais sejam mais complicados, portanto, pode haver alguns cenários em que essa consulta se comporte semanticamente diferente da sua, mas mostra que às vezes os novos recursos podem fazer uma diferença real.

Eu experimentei alguns dados mais variados e encontrei alguns cenários correspondentes e outros não:

--Example 1: results matched
TRUNCATE TABLE dbo.t

-- Generate some more interesting test data
;WITH cte AS
(
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT T (A, B, C, D)
SELECT  'A' + CAST( a.rn AS VARCHAR(5) ),
        'B' + CAST( a.rn AS VARCHAR(5) ),
        'C' + CAST( a.rn AS VARCHAR(5) ),
        DATEADD(DAY, a.rn + b.rn, '1 Jan 2013')
FROM cte a
    CROSS JOIN cte b
WHERE a.rn % 3 = 0
 AND b.rn % 5 = 0
ORDER BY 1, 2, 3
GO


-- Original query
SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY D
            )
INTO #tmp1
FROM    dbo.T t1 
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;
GO

SELECT  ID,
        Comp,
        D,
        D2 = LEAD(D) OVER(PARTITION BY COMP ORDER BY D)
INTO #tmp2
FROM    dbo.T 
WHERE   D IS NOT NULL
ORDER BY Comp;
GO


-- Checks ...
SELECT * FROM #tmp1
EXCEPT
SELECT * FROM #tmp2

SELECT * FROM #tmp2
EXCEPT
SELECT * FROM #tmp1


Example 2: results did not match
TRUNCATE TABLE dbo.t

-- Generate some more interesting test data
;WITH cte AS
(
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT T (A, B, C, D)
SELECT  'A' + CAST( a.rn AS VARCHAR(5) ),
        'B' + CAST( a.rn AS VARCHAR(5) ),
        'C' + CAST( a.rn AS VARCHAR(5) ),
        DATEADD(DAY, a.rn, '1 Jan 2013')
FROM cte a

-- Add some more data
INSERT dbo.T (A, B, C, D)
SELECT A, B, C, D 
FROM dbo.T
WHERE DAY(D) In ( 3, 7, 9 )


INSERT dbo.T (A, B, C, D)
SELECT A, B, C, DATEADD( day, 1, D )
FROM dbo.T
WHERE DAY(D) In ( 12, 13, 17 )


SELECT * FROM #tmp1
EXCEPT
SELECT * FROM #tmp2

SELECT * FROM #tmp2
EXCEPT
SELECT * FROM #tmp1

SELECT * FROM #tmp2
INTERSECT
SELECT * FROM #tmp1


select * from #tmp1
where comp = 'A2-B2-C2'

select * from #tmp2
where comp = 'A2-B2-C2'

Quando tentei executar as mesmas ações, obtive os outros resultados. Em primeiro lugar, meu plano de execução para tabela sem índices é o seguinte:

Como podemos ver no Clustered Index Scan (t2), o predicado é usado para determinar as linhas necessárias a serem retornadas (por causa da condição):

Quando o índice foi adicionado, não importando se foi definido pelo operador WITH ou não, o plano de execução passou a ser o seguinte:

Como podemos ver, o Clustered Index Scan foi substituído pelo Index Scan. Como vimos acima, o SQL Server utiliza as colunas de origem da coluna computada para realizar o casamento da consulta aninhada. Durante a varredura de índice clusterizado, todos esses valores podem ser adquiridos ao mesmo tempo (sem necessidade de operações adicionais). Quando o índice foi adicionado, a filtragem das linhas necessárias da tabela (na seleção principal) está sendo executada de acordo com o índice, mas os valores das colunas de origem para a coluna computada compainda precisam ser obtidos (última operação Nested Loop) .

Por causa disso, a operação Key Lookup é usada - para obter os dados das colunas de origem do calculado.

PS Parece um bug no SQL Server.