Otimizando planos de execução para consultas T-SQL parametrizadas contendo funções de janela
EDIT: eu atualizei o código de exemplo e forneceu tabela completa e exibir implementações para referência, mas a questão essencial permanece inalterada.
Eu tenho uma visão bastante complexa em um banco de dados que estou tentando consultar. Quando tento recuperar um conjunto de linhas da visualização codificando a cláusula WHERE para valores de chave estrangeira específicos, a exibição é executada muito rapidamente com um plano de execução ideal (os índices são usados corretamente, etc.)
SELECT *
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = 20
No entanto, quando tento adicionar parâmetros à consulta, de repente meu plano de execução se desfaz. Quando executo a consulta abaixo, estou obtendo verificações de índice em vez de procurar em todo o lugar e o desempenho da consulta é muito ruim.
DECLARE @ForeignKeyCol int = 20
SELECT *
FROM dbo.ViewOnBaseTable
WHERE ForeignKeyCol = @ForeignKeyCol
Estou usando o SQL Server 2008 R2. O que dá aqui? O que é sobre o uso de parâmetros que está causando um plano sub-ótimo? Qualquer ajuda seria muito apreciada.
Para referência, aqui estão as definições de objeto para as quais estou recebendo o erro.
CREATE TABLE [dbo].[BaseTable]
(
[PrimaryKeyCol] [uniqueidentifier] PRIMARY KEY,
[ForeignKeyCol] [int] NULL,
[DataCol] [binary](1000) NOT NULL
)
CREATE NONCLUSTERED INDEX [IX_BaseTable_ForeignKeyCol] ON [dbo].[BaseTable]
(
[ForeignKeyCol] ASC
)
CREATE VIEW [dbo].[ViewOnBaseTable]
AS
SELECT
PrimaryKeyCol,
ForeignKeyCol,
DENSE_RANK() OVER (PARTITION BY ForeignKeyCol ORDER BY PrimaryKeyCol) AS ForeignKeyRank,
DataCol
FROM
dbo.BaseTable
Estou certo de que a função de janela é o problema, mas estou filtrando minha consulta por um único valor pelo qual a função de janela é particionada, portanto, esperaria que o otimizador filtrasse primeiro e depois executasse a função de janela. Isso é feito no exemplo codificado, mas não no exemplo parametrizado. Abaixo estão os dois planos de consulta. O plano de cima é bom e o plano de fundo é ruim.