Quando os predicados SARGable podem ser inseridos em uma CTE ou tabela derivada?

Saco de areia

Enquanto trabalhava no Top Quality Blog Posts® , me deparei com alguns comportamentos do otimizador que achei muito interessantes. Eu não tenho uma explicação imediatamente, pelo menos não uma com a qual eu esteja feliz, então estou colocando aqui caso alguém inteligente apareça.

Se você quiser acompanhar, você pode pegar a versão 2013 do despejo de dados do Stack Overflow aqui . Estou usando a tabela Comentários, com um índice adicional.

CREATE INDEX [ix_ennui] ON [dbo].[Comments] ( [UserId], [Score] DESC );

Consulta Um

Quando eu consulto a tabela assim, recebo um plano de consulta estranho .

WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score DESC
     )
SELECT *
FROM   x
WHERE  x.Score >= 500;

O predicado SARGable em Score não é inserido no CTE. Está em um operador de filtro muito mais tarde no plano.

O que eu acho estranho, já que o ORDER BYestá na mesma coluna que o filtro.

Consulta Dois

Se eu alterar a consulta, ela é enviada.

WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score >= 500
ORDER BY x.Score DESC;

O plano de consulta também muda e é executado muito mais rápido, sem derramamento no disco. Ambos produzem os mesmos resultados, com o predicado na varredura de índice não clusterizado.

Consulta três

Isso é o equivalente a escrever a consulta assim:

SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score >= 500
ORDER BY c.Score DESC;

Consulta Quatro

O uso de uma tabela derivada obtém o mesmo plano de consulta "ruim" que a consulta CTE inicial

SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score DESC ) AS x
WHERE x.Score >= 500;

As coisas ficam ainda mais estranhas quando...

Eu altero a consulta para ordenar os dados em ordem crescente e o filtro para <=.

Para evitar fazer essa pergunta muito longa, vou colocar tudo junto.

Consultas

--Derived table
SELECT *
FROM   (   SELECT   TOP 101
                    c.UserId, c.Text, c.Score
           FROM     dbo.Comments AS c
           ORDER BY c.Score ASC ) AS x
WHERE x.Score <= 500;


--TOP inside CTE
WITH x
    AS
     (
         SELECT   TOP 101
                  c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
         ORDER BY c.Score ASC
     )
SELECT *
FROM   x
WHERE  x.Score <= 500;


--Written normally
SELECT   TOP 101
         c.UserId, c.Text, c.Score
FROM     dbo.Comments AS c
WHERE c.Score <= 500
ORDER BY c.Score ASC;

--TOP outside CTE
WITH x
    AS
     (
         SELECT   c.UserId, c.Text, c.Score
         FROM     dbo.Comments AS c
     )
SELECT TOP 101 *
FROM   x
WHERE  x.Score <= 500
ORDER BY x.Score ASC;

Planos

Link do plano .

Observe que nenhuma dessas consultas tira proveito do índice não clusterizado -- a única coisa que muda aqui é a posição do operador de filtro. Em nenhum caso o predicado é enviado para o acesso ao índice.

Aparece uma pergunta!

Existe uma razão pela qual um predicado SARGable pode ser empurrado em alguns cenários e não em outros? As diferenças dentro das consultas classificadas em ordem decrescente são interessantes, mas as diferenças entre aquelas e as que são ascendentes são bizarras.

Para quem estiver interessado, aqui estão os planos com apenas um índice em Score:

• DESC
• ASC