Início / dba / Perguntas / 345630
Accepted
urk_forever
Asked: 2025-03-08 00:27:25 +0800 CST

Varredura de índice quando mais de 35 subconsultas correlacionadas são usadas com estimativa de cardinalidade padrão

  • 772

Recentemente, atualizamos o nível de compatibilidade do nosso SQL Server de 2012 para 2016, mas após atualizar o nível de compatibilidade, tivemos problemas de desempenho quando muitas subconsultas são usadas. Especialmente quando mais de 35 subconsultas são usadas.

Aqui está uma consulta com a qual posso reproduzi-lo:

SELECT 
  [PK_R_ASSEMBLYCOSTING],
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  [PK_R_ASSEMBLYCOSTING]
FROM [R_ASSEMBLYCOSTING]
WHERE [FK_ASSEMBLY] = 309961

Quando há menos de 35 subconsultas, o plano de consulta mostra que está usando buscas de índice para as subconsultas:

índice de plano de consulta busca

Mas para cada subconsulta adicional acima de 35, uma varredura de índice é usada:

índices de plano de consulta

Alguém tem alguma explicação do porquê isso acontece? Se Legacy Cardinality Estimation estiver habilitado, a consulta é rápida e não tem esse problema, mas queremos desabilitar isso.

Já tentei reconstruir os índices e atualizar as estatísticas, mas isso não faz diferença.

sql-server

1 respostas

  1. Best Answer

    Esta é apenas uma escolha de custo irritante do otimizador. Você provavelmente pode obter o formato de plano que deseja com a alteração abaixo em sua consulta. Se você encontrar qualquer Eager Index Spools da reescrita abaixo, pode ser necessário adicionar uma FORCESEEKdica às suas subconsultas, por exemplo

    FROM [R_ASSEMBLYCOSTINGITEM] WITH(FORCESEEK)

    Como você está obtendo um SUMem cada subconsulta escalar, que pode retornar apenas uma linha, já que não há GROUP BY, o TOP (1)parece redundante, mas é bastante útil para influenciar o otimizador a fazer o que você deseja.

    Veja minha postagem aqui:

    Por favor, tenha em mente que reconstruir índices é um esforço totalmente desperdiçado para tarefas como essa. Atualizar estatísticas é suficiente.

    SELECT 
  [PK_R_ASSEMBLYCOSTING],
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  (SELECT TOP (1) SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
  [PK_R_ASSEMBLYCOSTING]
FROM [R_ASSEMBLYCOSTING]
WHERE [FK_ASSEMBLY] = 309961
    • 11

relate perguntas