Por que o SQL Server usa um plano de execução melhor quando inline a variável?

No SQL Server, existem três formas comuns de predicado sem associação:

Com um valor literal :

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = 1;

Com um parâmetro :

CREATE PROCEDURE dbo.SomeProc(@Reputation INT)
AS
BEGIN
    SELECT COUNT(*) AS records
    FROM   dbo.Users AS u
    WHERE  u.Reputation = @Reputation;
END;

Com uma variável local :

DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

Resultados

Quando você usa um valor literal , e seu plano não é a) Trivial eb) Simples Parametrizado ou c) você não tem a Parametrização Forçada ativada, o otimizador cria um plano muito especial apenas para esse valor.

Quando você usa um parâmetro , o otimizador criará um plano para esse parâmetro (isso é chamado de sniffing de parâmetro ) e, em seguida, reutilizará esse plano, dicas de recompilação ausentes, despejo de cache do plano etc.

Quando você usa uma variável local , o otimizador faz um plano para... Algo .

Se você fosse executar esta consulta:

DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

O plano ficaria assim:

E o número estimado de linhas para essa variável local ficaria assim:

Mesmo que a consulta retorne uma contagem de 4.744.427.

Variáveis ​​locais, sendo desconhecidas, não usam a parte 'boa' do histograma para estimativa de cardinalidade. Eles usam um palpite baseado no vetor de densidade.

SELECT 5.280389E-05 * 7250739 AS [poo]

Isso lhe dará 382.86722457471, que é o palpite que o otimizador faz.

Essas suposições desconhecidas geralmente são suposições muito ruins e muitas vezes podem levar a planos ruins e escolhas de índice ruins.

Consertando-o?

Suas opções geralmente são:

• Dicas de índice frágeis
• Dicas de recompilação potencialmente caras
• SQL dinâmico parametrizado
• Um procedimento armazenado
• Melhorar o índice atual

Suas opções são especificamente:

Melhorar o índice atual significa estendê-lo para cobrir todas as colunas necessárias para a consulta:

CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp, SomeTimestamp, SomeInt)
WITH (DROP_EXISTING = ON);

Supondo que Idos valores sejam razoavelmente seletivos, isso lhe dará um bom plano e ajudará o otimizador, fornecendo a ele um método de acesso a dados 'óbvio'.

Mais leitura

Você pode ler mais sobre a incorporação de parâmetros aqui:

• Parameter Sniffing, Embedding, and the RECOMPILE Options , por Paul White
• Por que você está ajustando os procedimentos armazenados de forma errada (o problema com variáveis ​​locais) , Kendra Little

Vou supor que você tenha dados distorcidos, que não deseja usar dicas de consulta para forçar o otimizador a fazer o que fazer e que precisa obter um bom desempenho para todos os valores de entrada possíveis de @Id. Você pode obter um plano de consulta garantido para exigir apenas algumas leituras lógicas para qualquer valor de entrada possível se estiver disposto a criar o seguinte par de índices (ou seu equivalente):

CREATE INDEX GetMinSomeTimestamp ON dbo.MyTable (Id, SomeTimestamp) WHERE SomeBit = 1;
CREATE INDEX GetMaxSomeInt ON dbo.MyTable (Id, SomeInt) WHERE SomeBit = 1;

Abaixo estão meus dados de teste. Coloquei 13 M linhas na tabela e fiz metade delas ter um valor de '3A35EA17-CE7E-4637-8319-4C517B6E48CA'para a Idcoluna.

DROP TABLE IF EXISTS dbo.MyTable;

CREATE TABLE dbo.MyTable (
    Id uniqueidentifier,
    SomeTimestamp DATETIME2,
    SomeInt INT,
    SomeBit BIT,
    FILLER VARCHAR(100)
);

INSERT INTO dbo.MyTable WITH (TABLOCK)
SELECT NEWID(), CURRENT_TIMESTAMP, 0, 1, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

INSERT INTO dbo.MyTable WITH (TABLOCK)
SELECT '3A35EA17-CE7E-4637-8319-4C517B6E48CA', CURRENT_TIMESTAMP, 0, 1, REPLICATE('Z', 100)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

Esta consulta pode parecer um pouco estranha no início:

DECLARE @Id UNIQUEIDENTIFIER = '3A35EA17-CE7E-4637-8319-4C517B6E48CA'

SELECT
  @Id,
  st.SomeTimestamp,
  si.SomeInt
FROM (
    SELECT TOP (1) SomeInt, Id
    FROM dbo.MyTable
    WHERE Id = @Id
    AND SomeBit = 1
    ORDER BY SomeInt DESC
) si
CROSS JOIN (
    SELECT TOP (1) SomeTimestamp, Id
    FROM dbo.MyTable
    WHERE Id = @Id
    AND SomeBit = 1
    ORDER BY SomeTimestamp ASC
) st;

Ele foi projetado para aproveitar a ordenação dos índices para encontrar o valor mínimo ou máximo com algumas leituras lógicas. O CROSS JOINexiste para obter resultados corretos quando não há linhas correspondentes para o @Idvalor. Mesmo se eu filtrar o valor mais popular na tabela (correspondendo a 6,5 ​​milhões de linhas), recebo apenas 8 leituras lógicas:

Tabela 'MinhaTabela'. Contagem de varredura 2, leituras lógicas 8

Aqui está o plano de consulta:

Ambas as buscas de índice encontram 0 ou 1 linhas. É extremamente eficiente, mas criar dois índices pode ser um exagero para o seu cenário. Você pode considerar o seguinte índice em vez disso:

CREATE INDEX CoveringIndex ON dbo.MyTable (Id) INCLUDE (SomeTimestamp, SomeInt) WHERE SomeBit = 1;

Agora, o plano de consulta para a consulta original (com uma MAXDOP 1dica opcional) parece um pouco diferente:

As pesquisas de chave não são mais necessárias. Com um caminho de acesso melhor que deve funcionar bem para todas as entradas, você não precisa se preocupar com o otimizador escolhendo o plano de consulta errado devido ao vetor de densidade. No entanto, essa consulta e índice não serão tão eficientes quanto os outros se você procurar um @Idvalor popular.

Tabela 'MinhaTabela'. Contagem de varredura 1, leituras lógicas 33757

Não posso responder por que aqui, mas a maneira rápida e suja de garantir que a consulta seja executada da maneira que você deseja é:

DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'
SELECT 
  Id,
  MIN(SomeTimestamp),
  MAX(SomeInt)
FROM dbo.MyTable WITH (INDEX(IX_MyTable_Id_SomeBit_Includes))
WHERE Id = @Id
  AND SomeBit = 1
GROUP BY Id

Isso incorre no risco de que a tabela ou os índices possam mudar no futuro, de modo que essa otimização se torne disfuncional, mas está disponível se você precisar. Espero que alguém possa oferecer uma resposta de causa raiz, conforme solicitado, em vez desta solução alternativa.