Índice SEEK não é usado a menos que OPTION (RECOMPILE)?

Resumindo alguns dos principais pontos de nossa discussão na sala de bate-papo :

De um modo geral, o SQL Server armazena em cache um único plano para cada instrução . Esse plano deve ser válido para todos os possíveis valores de parâmetros futuros .

Não é possível armazenar em cache um plano de busca para sua consulta, porque esse plano não seria válido se, por exemplo, @productid fosse nulo.

Em alguma versão futura, o SQL Server pode oferecer suporte a um único plano que escolhe dinamicamente entre uma varredura e uma busca, dependendo dos valores dos parâmetros de tempo de execução, mas isso não é algo que temos hoje.

Classe de problema geral

Sua consulta é um exemplo de um padrão conhecido como consulta "pega tudo" ou "pesquisa dinâmica". Existem várias soluções, cada uma com suas vantagens e desvantagens. Nas versões modernas do SQL Server (2008+), as principais opções são:

• IFblocos
• OPTION (RECOMPILE)
• SQL dinâmico usandosp_executesql

O trabalho mais abrangente sobre o assunto é provavelmente o de Erland Sommarskog, incluído nas referências no final desta resposta. Não há como fugir das complexidades envolvidas, por isso é necessário investir algum tempo experimentando cada opção para entender os trade-offs em cada caso.

IFblocos

Para ilustrar uma IFsolução de bloco para o caso específico da questão:

IF @productid IS NOT NULL AND @priceid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid
        AND T.priceID = @priceid;
END;
ELSE IF @productid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid;
END;
ELSE IF @priceid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.priceID = @priceid;
END;
ELSE
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T;
END;

Isso contém uma instrução separada para os quatro possíveis casos nulos ou não nulos para cada um dos dois parâmetros (ou variáveis ​​locais), portanto, há quatro planos.

Há um problema potencial com a detecção de parâmetros, que pode exigir uma OPTIMIZE FORdica em cada consulta. Consulte a seção de referências para explorar esses tipos de sutilezas.

recompilar

Conforme observado acima e na pergunta, você também pode adicionar uma OPTION (RECOMPILE)dica para obter um novo plano (busca ou varredura) em cada invocação. Dada a frequência relativamente lenta de chamadas no seu caso (uma vez a cada dez segundos, em média, com um tempo de compilação abaixo de um milissegundo), parece provável que esta opção seja adequada para você:

SELECT
    T.productID,
    T.priceID
FROM dbo.Transactions AS T
WHERE
    (T.productID = @productid OR @productid IS NULL)
    AND (T.priceID = @priceid OR @priceid IS NULL)
OPTION (RECOMPILE);

Também é possível combinar recursos das opções acima de forma criativa, para aproveitar ao máximo as vantagens de cada método, minimizando as desvantagens. Realmente não há atalho para entender essas coisas em detalhes e, em seguida, fazer uma escolha informada apoiada por testes realistas.

Leitura adicional

• Parâmetro Sniffing, Incorporação e as RECOMPILEOpções
• Condições de pesquisa dinâmica em T-SQL por Erland Sommarskog
• Procedimentos de alta performance por Kimberly Tripp