Eu tenho uma consulta problemática que estamos tentando ajustar. Um dos nossos primeiros pensamentos foi pegar uma parte de um plano de execução maior e armazenar esses resultados em uma tabela temporária intermediária e, em seguida, realizar as outras operações.
O que estou observando é que, quando inserimos os dados em uma tabela temporária, o custo do plano de execução aumenta (22 -> 1,1k). Agora, isso tem o benefício de permitir que o plano seja paralelo, o que reduziu o tempo de execução em 20%, mas não vale o uso muito maior de CPU por execução em nosso caso.
Estamos usando o SQL Server 2016 SP2 com o CE legado ativado.
Plano original (custo ~ 20):
https://www.brentozar.com/pastetheplan/?id=ry-QGnkCM
SQL original:
WITH Object1(Column1, Column2, Column3, Column4, Column5, Column6)
AS
(
SELECT Object2.Column1,
Object2.Column2,
Object3.Column3,
Object3.Column4,
Object3.Column5,
Object3.Column6
FROM Object4 AS Object5
INNER JOIN Object6 AS Object2 ON Object2.Column2 = Object5.Column2 AND Object2.Column7 = 0
INNER JOIN Object7 AS Object8 ON Object8.Column8 = Object2.Column9 AND Object8.Column7 = 0
INNER JOIN Object9 AS Object3 ON Object3.Column10 = Object8.Column11 AND Object3.Column7 = 0
INNER JOIN Object10 AS Object11 ON Object2.Column1 = Object11.Column1
WHERE Object8.Column12 IS NULL AND
Object8.Column13 = Object5.Column13 AND
Object3.Column3 = Object5.Column3 AND
Object11.Column14 = Variable1
)
insert Object12
SELECT Object13.Column2,
Object13.Column3,
MIN(Object13.Column4) AS Column15,
MAX(Object13.Column4) AS Column16,
COUNT(DISTINCT (CASE WHEN Object13.Column5 = 1 THEN Object13.Column1 END)) AS Column17,
COUNT(DISTINCT (CASE WHEN Object13.Column6 = 0 THEN Object13.Column1 END)) AS Column18,
COUNT(DISTINCT Object13.Column1) AS Column19
FROM Object1 AS Object13
GROUP BY Object13.Column2, Object13.Column3 OPTION (RECOMPILE)
Novo plano (com a área destacada em azul acima é pré-preparado em uma tabela temporária - Custo ~ 1,1k):
https://www.brentozar.com/pastetheplan/?id=rycqG3JRf
Novo SQL:
SELECT Object1.Column1,
Object1.Column2,
MIN(Object2.Column3) AS Column4,
MAX(Object2.Column3) AS Column5,
COUNT(DISTINCT (CASE WHEN Object2.Column6 = 1 THEN Object1.Column7 END)) AS Column8,
COUNT(DISTINCT (CASE WHEN Object2.Column9 = 0 THEN Object1.Column7 END)) AS Column10,
COUNT(DISTINCT Object1.Column7) AS Column11
from Object3 Object1
join Object4 Object2 on Object2.Column12 = Object1.Column13 and Object2.Column2 = Object1.Column2
where Object2.Column14 = 0
GROUP BY Object1.Column1, Object1.Column2 OPTION (RECOMPILE)
Alguém pode nos ajudar a entender porque o novo plano teria um custo tão maior? Ficarei feliz em fornecer informações adicionais sobre tabelas/índices abaixo, se necessário.
No caso do plano original, percebemos que ele está fazendo uma inserção em vez de uma seleção. Mesmo assim, o select não deveria (na nossa cabeça) ser muito mais caro.
Este é o plano de execução real. É uma preocupação porque, devido ao custo do plano imensamente mais alto, ele é paralelo. Portanto, usando CPU mais alta. Além disso, estamos curiosos para saber por que o custo do plano aumenta tanto para algo como pré-encenar os dados, o que geralmente o aproxima, se não melhor, do custo original.
A tabela temporária é indexada na segunda consulta como um PK clusterizado composto em Object1.Column13 e Object1.Column2. Isso corresponde às colunas (e ordem) do Object4. Adicionar uma MAXDOPdica é uma opção, mas isso também é um exercício acadêmico de 'por que no mundo o custo sobe tanto'?
Adicionar OPTION (ORDER GROUP)à segunda consulta resulta em nenhuma alteração, mesmos operadores/custos.
NOTAS:
- Object9 na primeira consulta é o mesmo objeto que Object4 na segunda.
