O índice de data SEEK é escolhido ao usar OPTION RECOMPILE, mas não com OPTION OPTIMIZE FOR

Eu tenho uma tabela contendo 10 anos de 'varreduras de pacotes'. Alguém verifica um pacote e registra a data e o nome de usuário. Vamos fingir por enquanto que reter 10 anos de dados realmente tem um propósito.

Eu tenho uma página para mostrar um resumo da semana passada, então claramente quero ler apenas 1 semana de dados.

Aqui está a consulta, a ser executada no SSMS duas vezes, uma com uma data recente codificada e novamente com uma data antiga em 2013 . É originalmente uma consulta parametrizada, mas no SSMS estou substituindo @p0pela data:

SELECT [t0].[VerifyDate], [t0].[PackageId], [t0].[Username]
FROM [dbo].[PackageVerification] AS [t0]
INNER JOIN [dbo].[Package] AS [t1] ON [t1].[PackageId] = [t0].[PackageId]

WHERE ([t1].[PackageStatus] <> 99) AND ([t0].[VerifyDate] > @p0)   
ORDER BY [t0].[VerifyDate] DESC

Antes de executá-lo, gostaria de apresentar meu índice de datas.

Agora, meu índice de data não está na minha PackageVerificationtabela, mas em uma 'visualização auxiliar' que executa a mesma junção vista acima. A consulta acima é capaz de usar magicamente essa exibição indexada porque tenho SCHEMABINDING ativado.

CREATE NONCLUSTERED INDEX [IX_Helper_PackageVerification_USER_SCAN_HISTORY] ON [dbo].[Helper_PackageVerification]
(
    [VerifyDate] DESC,
    [PackageStatus] ASC
)
INCLUDE (
    [VerifyDateDate],
    [Username]
) 

Quando executo a consulta no SSMS com uma data antiga e nova, ele usa a varredura ou busca conforme o esperado. O limite parece estar em algum lugar por volta de 2015. Portanto, qualquer coisa remotamente recente deve definitivamente estar usando uma busca. Aqui estão os resultados disso:

Quando executo como uma consulta parametrizada do meu aplicativo , sempre recebo uma varredura completa , que por algum motivo usa um plano paralelizado.

Pelo menos está usando meu índice auxiliar.

Na verdade, não sei por que não recebo sniffing de parâmetros para isso. Eu sempre passo uma data muito recente, então eu teria pensado que poderia ter preferido uma varredura, mas estou bem com isso escolhendo o plano acima, dadas as circunstâncias. Há mais de um milhão de linhas e leva cerca de 150 ms.

Aliás, este é um banco de dados SQL Azure com 2vCores. A detecção de parâmetros é habilitada e a parametrização é definida como simples .

Se eu alterar a consulta e executar meu aplicativo usando ,OPTION (RECOMPILE) recebo o SEEK desejado e um desempenho muito bom de apenas alguns ms. O tempo de recompilação parece ser insignificante e, francamente, este é um desempenho perfeitamente bom que posso usar.

Quando procuro na loja de consultas, posso verificar que a OPÇÃO RECOMPILE usa a busca por uma data recente e a varredura de uma data antiga! Incrível.

No entanto, e eu nunca tentei isso antes - pensei em melhorar ainda mais com o OPTION (OPTIMIZE FOR @p0 = '4/1/2021').

Eu esperava que isso também usasse a busca, mas sem a necessidade de recompilação toda vez. Eu apenas alteraria periodicamente a data passada para OPTIMIZE FOR - talvez para o início do mês anterior.

No entanto, esta é a consulta no repositório de consultas.

E ele faz uma varredura completa de mais de 1 milhão de linhas ao definir o parâmetro de data para 7/4/21!

Então agora estou perdido. Eu tentei ler sobre tudo o que posso sobre o assunto, mas não encontrei esse problema. RECOMPILE funciona, mas OPTIMIZE FOR não parece fazer nada quando espero que simule efetivamente a execução da consulta no SSMS com valores codificados.

Planos de consulta

Este primeiro plano é o único plano inesperado - é uma varredura e eu quero uma busca.

OTIMIZE PARA @p1 = '2021/4/1' - https://www.brentozar.com/pastetheplan/?id=H1JB43AUu OTIMIZE PARA AMBOS OS PARAM - https://www.brentozar.com/pastetheplan/?id=rkV9U3AUu OPÇÃO RECOMPILAR - https://www.brentozar.com/pastetheplan/?id=SJ5cS3CUd

Estes são para provar que o otimizador sabe que datas recentes devem ser procuradas!

HARDCODED 2013 - SCAN - https://www.brentozar.com/pastetheplan/?id=BkeA42RLu HARDCODED 2015 - BUSCA - https://www.brentozar.com/pastetheplan/?id=S1c8r3R8O

Estou começando a me perguntar se esta versão não suporta OPTIMIZE FOR, mesmo que não encontre nada dizendo que não seria

Edit: (Após a resposta de Paul)

Eu tentei algumas coisas adicionais. Primeiro aqui está a definição de VIEW que não incluí antes. Isso faz um JOIN e, como usa SCHEMABINDING, o otimizador pode substituí-lo:

CRIAR VISUALIZAÇÃO [dbo].[Helper_PackageVerification] COM SCHEMABINDING AS

SELECIONAR

-- Colunas de verificação do pacote [t0].PackageVerificationId, [t0].Verfied, -- erro de ortografia de muito tempo atrás! [t0].VerifyDate, -- isso não é anulável em [t0] btw [t0]. Nome de usuário,

-- Colunas do pacote [t1].PackageId, [t1].PackageStatus, [t1].PackedOnDate

FROM [dbo].[PackageVerification] AS [t0]
INNER JOIN [dbo].[Package] AS [t1] ON [t1].[PackageId] = [t0].[PackageId]

WHERE (Verfied = 1 E VerifyDate NÃO É NULO E PackageStatus <> 99) GO

O índice CLUSTERED está ativado PackageVerificationIde o índice principal NON CLUSTERED é mostrado acima. Na verdade, criei meia dúzia de índices de conversão para ver qual escolheria.

1. Eu codifiquei PackageStatus <> 99. Originalmente era um parâmetro.

2. Tentei adicionar NOT NULL ao filtro na exibição para ver o que aconteceria. Isso realmente me deu um SEEK, mas um inútil, já que o predicado SEEK estava realmente em VerifyDate IS NOT NULL.

https://www.brentozar.com/pastetheplan/?id=r1HlgF1Dd

Você não pode adicionar um índice filtrado a uma exibição indexada, portanto, mesmo que a exibição filtre as datas NOT NULL, provavelmente não poderá ser correspondida. Então essa poderia ser a razão principal pela qual eu não consegui que minha data fosse usada para o predicado SEEK?

2. Nesse caso, não tentei usar o índice auxiliar diretamente na consulta, mas esperaria que funcionasse com NOEXPAND, pois estou fazendo isso em outro lugar.