Tenho uma consulta SQL que passei os últimos dois dias tentando otimizar usando tentativa e erro e o plano de execução, mas sem sucesso. Por favor, perdoe-me por fazer isso, mas vou postar todo o plano de execução aqui. Fiz um esforço para tornar genéricos os nomes das tabelas e colunas na consulta e no plano de execução, tanto para concisão quanto para proteger o IP da minha empresa. O plano de execução pode ser aberto com o SQL Sentry Plan Explorer .
Já fiz bastante T-SQL, mas usar planos de execução para otimizar minha consulta é uma área nova para mim e realmente tentei entender como fazer isso. Portanto, se alguém puder me ajudar com isso e explicar como esse plano de execução pode ser decifrado para encontrar maneiras de otimizá-lo na consulta, ficaria eternamente grato. Tenho muito mais consultas para otimizar - só preciso de um trampolim para me ajudar com esta primeira.
Esta é a consulta:
DECLARE @Param0 DATETIME = '2013-07-29';
DECLARE @Param1 INT = CONVERT(INT, CONVERT(VARCHAR, @Param0, 112))
DECLARE @Param2 VARCHAR(50) = 'ABC';
DECLARE @Param3 VARCHAR(100) = 'DEF';
DECLARE @Param4 VARCHAR(50) = 'XYZ';
DECLARE @Param5 VARCHAR(100) = NULL;
DECLARE @Param6 VARCHAR(50) = 'Text3';
SET NOCOUNT ON
DECLARE @MyTableVar TABLE
(
B_Var1_PK int,
Job_Var1 varchar(512),
Job_Var2 varchar(50)
)
INSERT INTO @MyTableVar (B_Var1_PK, Job_Var1, Job_Var2)
SELECT B_Var1_PK, Job_Var1, Job_Var2 FROM [fn_GetJobs] (@Param1, @Param2, @Param3, @Param4, @Param6);
CREATE TABLE #TempTable
(
TTVar1_PK INT PRIMARY KEY,
TTVar2_LK VARCHAR(100),
TTVar3_LK VARCHAR(50),
TTVar4_LK INT,
TTVar5 VARCHAR(20)
);
INSERT INTO #TempTable
SELECT DISTINCT
T.T1_PK,
T.T1_Var1_LK,
T.T1_Var2_LK,
MAX(T.T1_Var3_LK),
T.T1_Var4_LK
FROM
MyTable1 T
INNER JOIN feeds.MyTable2 A ON A.T2_Var1 = T.T1_Var4_LK
INNER JOIN @MyTableVar B ON B.Job_Var2 = A.T2_Var2 AND B.Job_Var1 = A.T2_Var3
GROUP BY T.T1_PK, T.T1_Var1_LK, T.T1_Var2_LK, T.T1_Var4_LK
-- This is the slow statement...
SELECT
CASE E.E_Var1_LK
WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
WHEN 'Text3' THEN T.TTVar2_LK
END,
T.TTVar4_LK,
T.TTVar3_LK,
CASE E.E_Var1_LK
WHEN 'Text1' THEN F.F_Var1
WHEN 'Text2' THEN F.F_Var2
WHEN 'Text3' THEN T.TTVar5
END,
A.A_Var3_FK_LK,
C.C_Var1_PK,
SUM(CONVERT(DECIMAL(18,4), A.A_Var1) + CONVERT(DECIMAL(18,4), A.A_Var2))
FROM #TempTable T
INNER JOIN TableA (NOLOCK) A ON A.A_Var4_FK_LK = T.TTVar1_PK
INNER JOIN @MyTableVar B ON B.B_Var1_PK = A.Job
INNER JOIN TableC (NOLOCK) C ON C.C_Var2_PK = A.A_Var5_FK_LK
INNER JOIN TableD (NOLOCK) D ON D.D_Var1_PK = A.A_Var6_FK_LK
INNER JOIN TableE (NOLOCK) E ON E.E_Var1_PK = A.A_Var7_FK_LK
LEFT OUTER JOIN feeds.TableF (NOLOCK) F ON F.F_Var1 = T.TTVar5
WHERE A.A_Var8_FK_LK = @Param1
GROUP BY
CASE E.E_Var1_LK
WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
WHEN 'Text3' THEN T.TTVar2_LK
END,
T.TTVar4_LK,
T.TTVar3_LK,
CASE E.E_Var1_LK
WHEN 'Text1' THEN F.F_Var1
WHEN 'Text2' THEN F.F_Var2
WHEN 'Text3' THEN T.TTVar5
END,
A.A_Var3_FK_LK,
C.C_Var1_PK
IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
DROP TABLE #TempTable
END
IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
DROP TABLE #TempTable
END
O que descobri é que a terceira instrução (comentada como lenta) é a parte que está demorando mais. As duas declarações anteriores retornam quase instantaneamente.
O plano de execução está disponível em XML neste link .
É melhor clicar com o botão direito do mouse, salvar e abrir no SQL Sentry Plan Explorer ou em algum outro software de visualização, em vez de abrir no navegador.
Se você precisar de mais informações minhas sobre as tabelas ou dados, não hesite em perguntar.
Antes de chegar à resposta principal, há dois softwares que você precisa atualizar.
Atualizações de software necessárias
O primeiro é o SQL Server. Você está executando o SQL Server 2008 Service Pack 1 (compilação 2531). Você deve ser corrigido pelo menos para o Service Pack atual (SQL Server 2008 Service Pack 3 - build 5500). A versão mais recente do SQL Server 2008 no momento da escrita é o Service Pack 3, atualização cumulativa 12 (compilação 5844).
O segundo software é o SQL Sentry Plan Explorer . As versões mais recentes têm novos recursos e correções significativas, incluindo a capacidade de carregar diretamente um plano de consulta para análise especializada (não é necessário colar XML em qualquer lugar!)
Análise do Plano de Consulta
A estimativa de cardinalidade para a variável de tabela está exatamente correta, graças a uma recompilação em nível de instrução:
Infelizmente, as variáveis de tabela não mantêm estatísticas de distribuição, portanto, tudo o que o otimizador sabe é que existem seis linhas; ele não sabe nada sobre os valores que podem estar nessas seis linhas. Essa informação é crucial, pois a próxima operação é uma junção com outra tabela. A estimativa de cardinalidade dessa junção é baseada em um palpite do otimizador:
A partir daí, o plano escolhido pelo otimizador é baseado em informações incorretas, então não é de admirar que o desempenho seja tão ruim. Em particular, a memória reservada para classificações e tabelas de hash para junções de hash será muito pequena. No tempo de execução, as classificações de estouro e as operações de hash serão espalhadas para o disco tempdb físico .
O SQL Server 2008 não destaca isso nos planos de execução; você pode monitorar os derramamentos usando eventos estendidos ou avisos de classificação de perfil e avisos de hash . A memória é reservada para classificações e hashes com base em estimativas de cardinalidade antes do início da execução e não pode ser aumentada durante a execução, independentemente da quantidade de memória sobressalente que seu SQL Server possa ter. Estimativas precisas de contagem de linhas são, portanto, cruciais para qualquer plano de execução que envolva operações que consomem memória do espaço de trabalho.
Sua consulta também é parametrizada. Você deve considerar adicionar
OPTION (RECOMPILE)à consulta se valores de parâmetros diferentes afetarem o plano de consulta. Você provavelmente deve considerar usá-lo de qualquer maneira, para que o otimizador possa ver o valor de@Param1no momento da compilação. No mínimo, isso pode ajudar o otimizador a produzir uma estimativa mais razoável para a busca de índice mostrada acima, dado que a tabela é muito grande e particionada. Também pode permitir a eliminação de partições estáticas.Tente a consulta novamente com uma tabela temporária em vez da variável de tabela e
OPTION (RECOMPILE). Você também deve tentar materializar o resultado da primeira junção em outra tabela temporária e executar o restante da consulta nela. O número de linhas não é tão grande (3.285.620), então isso deve ser razoavelmente rápido. O otimizador terá então uma estimativa de cardinalidade exata e estatísticas de distribuição para o resultado da união. Com sorte, o resto do plano se encaixará perfeitamente.Trabalhando a partir das propriedades mostradas no plano, a consulta de materialização seria:
Você também pode
INSERTem uma tabela temporária predefinida (os tipos de dados corretos não são mostrados no plano, então não posso fazer essa parte). A nova tabela temporária pode ou não se beneficiar de índices clusterizados e não clusterizados.Percebo que deve haver um PK em @MyTableVar e concordo que #MyTableVar geralmente tem melhor desempenho (especialmente com maior número de linhas).
A condição dentro da cláusula where
deve ser movido para a junção interna A AND'ed. O otimizador não é inteligente o suficiente em minha experiência para fazer isso (desculpe, não olhei no plano) e pode fazer uma grande diferença.
Se essas alterações não mostrarem melhorias, eu criaria outra tabela temporária de A e todas as coisas que ela une para serem restritas (bem?) por A.A_Var8_FK_LK = @Param1 se esse agrupamento fizer sentido lógico para você.
Em seguida, crie um índice clusterizado nessa tabela temporária (antes ou depois da criação) para a próxima condição de junção.
Em seguida, junte esse resultado às poucas tabelas (F e T) que restam.
Bam, que precisa de um plano de consulta ruim quando as estimativas de linha estão erradas e às vezes não são facilmente melhoráveis de qualquer maneira). Presumo que você tenha índices adequados, que é o que eu verificaria primeiro no plano.
Um rastreamento pode mostrar os derramamentos de tempdb que podem ou não ter um impacto drástico.
Outra abordagem alternativa - pelo menos mais rápida de experimentar - é ordenar as tabelas do menor número de linhas (A) para o maior e, em seguida, começar a adicionar a mesclagem, o hash e o loop às junções. Quando dicas estão presentes, a ordem de junção é fixada conforme especificado. Outros usuários evitam sabiamente essa abordagem porque pode prejudicar a longo prazo se as contagens de linhas relativas mudarem drasticamente. Um número mínimo de dicas é desejável.
Se você estiver fazendo muitos desses, talvez valha a pena tentar (ou testar) um otimizador comercial e ainda é uma boa experiência de aprendizado.