Início / user-332338

urk_forever's questions

Martin Hope
urk_forever
Asked: 2025-03-08 00:27:25 +0800 CST
  • 9

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