No momento, estou enfrentando um problema com consultas parametrizadas no SQL Server e não entendo onde ele está enraizado.
Eu resumi tudo em um exemplo simples:
Vamos supor uma tabela que contém dados sobre alguma entidade filha, bem como o parent_ide um índice correspondente no parent_id. Os dados são acessados com base nisso, parent_idmas por meio de uma visualização que, adicionalmente aos dados da tabela, contém uma coluna que calcula um row_numbersobre todas as entradas particionadas pelo parent_id.
Configuração reproduzível
Crie a tabela, o índice e a visualização da seguinte forma:
CREATE TABLE dbo.test (id BIGINT IDENTITY(1,1), text NVARCHAR(255), parent_id BIGINT);
GO
CREATE NONCLUSTERED INDEX idx_test_parent_id
ON dbo.test (parent_id);
GO
CREATE VIEW dbo.test_view
AS
SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id) AS row_num
FROM dbo.test
GO
Agora coloque alguns dados na tabela:
DECLARE @i BIGINT = 0
WHILE @i < 200000
BEGIN
SET @i = @i + 1
INSERT INTO dbo.test (text, parent_id)
VALUES ('test 1', @i), ('test 2', @i), ('test 3', @i);
END
A questão
Ao acessar os dados por meio de uma consulta parametrizada a partir da exibição, o SQL Server fará uma varredura completa na tabela.
DECLARE @parent_id BIGINT = 123456
SELECT *
FROM dbo.test_view
WHERE parent_id = @parent_id
Ao acessar os dados diretamente (sem usar um parâmetro), obteremos o índice de busca esperado.
SELECT *
FROM dbo.test_view
WHERE parent_id = 123456
O que eu tentei
Pesquisando em diferentes fóruns, não entendi realmente o que está acontecendo aqui. Encontrei problemas semelhantes em que o parâmetro tinha o tipo de dado errado e, portanto, o desempenho era ruim, mas isso não é um problema no meu caso. Também li sobre problemas com sniffing de parâmetros, mas também não acho que isso seja um problema aqui, pois não acesso dados por meio de procedimentos ou funções armazenados.
Além disso, quando estou acessando os dados diretamente da tabela com uma consulta parametrizada, o problema não ocorrerá. Uma busca de índice é feita mesmo com os parâmetros.
O mesmo acontece quando adiciono o OPTION (RECOMPILE)à consulta acessando a exibição com uma consulta parametrizada, o SQL Server acabará fazendo uma busca de índice.
Pergunta
Alguém pode explicar qual é o problema aqui? Como isso é um problema para a view, mas não para a tabela em si? Eu realmente preciso me livrar da view calculando isso row_numberde forma diferente durante inserções/exclusões?
Configurar
- SQL Server 2022 v16.0.4165 em execução em um contêiner docker
- Imagem do Docker: mcr.microsoft.com/mssql/server:2022-latest
A tabela real tem uma chave primária, é claro. Mas também tem muito mais colunas do que apenas a textcoluna. Incluir todas essas colunas no índice seria uma possibilidade. O problema, no entanto, não está ocorrendo ao selecionar da tabela em si, então não parece ser um problema do índice para mim.
Eu não sabia que estava executando o banco de dados em um modo de compatibilidade. No ambiente produtivo, estou até recebendo CardinalityEstimationModelVersion="140". Não acho que o tenha configurado em algum lugar de propósito.
Planos de execução
- seleção direta com busca de índice
- seleção parametrizada com varredura completa da tabela
- Verificação completa da tabela com
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150