Início / dba / Perguntas / 345103
Accepted
jkohl
Asked: 2025-02-05 20:11:47 +0800 CST

Problemas de desempenho em consultas parametrizadas acessando uma exibição no SQL Server

  • 772

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

plano de consulta varredura completa da tabela

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

índice de plano de consulta procurar

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

sql-server

3 respostas

  1. Best Answer

    Seu plano de execução mostra que você está executando a CardinalityEstimationModelVersion150 (equivalente ao SQL Server 2019).

    E você diz que seu plano de produção está usando 140 (SQL Server 2017)

    Também posso reproduzir isso no SQL Server 2022 definindo o nível de compatibilidade do banco de dados como 140 ou 150.

    Parece que você está enfrentando o SelOnSeqPrjproblema descrito nesta resposta do Stack Overflow e The Problem with Window Functions and Views , ambos de Paul White.

    O problema desaparece no nível de compatibilidade (CL) 140 e 150 ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON(o que faz sentido, pois foi corrigido no CU30 para SQL Server 2017 e no CU17 para SQL Server 2019 ) e não é reproduzido no CL 160, independentemente dessa opção de configuração.

    insira a descrição da imagem aqui

    Se você estiver na versão 2022 e estiver usando apenas uma CL mais antiga sem querer, considere alterar isso para obter a funcionalidade mais recente e melhor e a correção para esse problema como padrão - sem precisar habilitar QUERY_OPTIMIZER_HOTFIXES.

    Alterar o nível de compatibilidade precisa ser testado, pois às vezes há mudanças comportamentais de ruptura entre os níveis e diferentes modelos de estimativa de cardinalidade podem afetar os planos de execução de forma positiva ou negativa. Você pode usar o query store para ajudar a mitigar o risco desse segundo problema.

    • 5

  2. Você está usando uma variável local. Uma variável local, diferente de um valor codificado ou um parâmetro em uma consulta parametrizada (declaração preparada ou procedimento armazenado), não usa o valor fornecido para olhar as estatísticas ao compilar o plano. Em vez disso, ela usa uma média dos valores nas estatísticas para chegar ao plano. EXCETO quando há uma situação de recompilação. Então, o valor na variável local pode ser usado para olhar as especificidades das estatísticas, não médias, para chegar a uma contagem de linhas diferente.

    Isso descreve praticamente todos os comportamentos que você está vendo. Teste criando um procedimento armazenado a partir da sua consulta e, em seguida, passando o mesmo valor.

    Agora, dito isso, o inverso disso é que a amostragem de um valor específico, também conhecido como sniffing de parâmetros, pode fornecer uma contagem de linhas mais precisa e, portanto, um melhor plano de execução. Até que você descubra que alguns parâmetros estão retornando mais linhas (ou menos) do que o especificado que foi usado para criar o plano. Então, o desempenho pode ser ruim porque você precisa de um plano baseado em uma média das linhas ou precisa de planos específicos para cada valor possível. É aqui que você se verá lidando com dicas de consulta OPTIMIZE FOR ou OPTIMIZE FOR UNKNOWN ou adicionando dicas RECOMPILE para obter planos específicos sempre. O SQL Server 2022 e superior tem até o que é chamado de otimização de plano sensível a parâmetros para ajudar a lidar com esse cenário.

    Resumindo, tudo isso fica difícil.

    • 3

  3. Embora você tenha identificado o problema de sniffing de parâmetros, a solução real provavelmente não é adicionar dicas à sua consulta. Problemas de sniffing de parâmetros acontecem quando você dá ao servidor uma escolha entre um plano ruim e um plano ainda pior, e ele não escolhe o certo. Em vez disso, você precisa dar a ele uma escolha tão boa que ele sempre escolha o plano correto.

    Então você precisa melhorar a indexação. Primeiro, você está perdendo uma Chave Primária, o que é um grande não-não em um banco de dados corretamente normalizado.

    ALTER TABLE dbo.test
  ADD PRIMARY KEY (id);

    Então, para corrigir seu problema real, você precisa "cobrir" as colunas da consulta com o índice. Então você precisa INCLUDEde colunas.

    CREATE NONCLUSTERED INDEX idx_test_parent_id
       ON dbo.test (parent_id, id) INCLUDE (text)
  WITH (DROP_EXISTING = ON);

    Agora seu plano de consulta está bonito e organizado, mesmo com a variável local.

    plano de consulta

    db<>violino

    • 1

relate perguntas