Organizando planos de execução gigantes
Neste post quero mostrar uma forma de organizar um plano de execução gigante.
Muitas vezes quando trabalhamos com bancos de dados antigos e complexos, acabamos lidando com problemas de performance e o plano de execução da algumas dicas por onde começar o trabalho de otimização.
O problema é quando o próprio plano de execução é grande e complexo, dificultando a leitura e tornando o simples trabalho de achar os pontos críticos em uma tarefa entediante.
O primeiro passo pra isso é salvar o plano de execução em XML. No Sql Management Studio, basta clicar com o botão direito sobre o Plano de Execução e selecionar "Save Execution Plan As". Após isso é necessário abrir o plano de execução no bloco de notas e re-salvar em Unicode, pois o XML está marcado como Unicode, mas o Management Studio salva em condificação ANSI, o que gera um problema no próximo passo.
Assim que temos o arquivo XML preparado, basta fazer alguns SELECT´s no arquivo XML, onde podemos ordenar por tempo estimado de CPU, IO ou a diferença entre o número de linhas esperados e o número atual:
DECLARE @xml XML
SELECT @xml =(SELECT * FROM OPENROWSET (BULK 'C:\Users\Usuario\Desktop\Exec.txt', SINGLE_BLOB) XMLShowPlan)
SELECT RelOp.op.value('@NodeId', 'int') AS NodeId,
RelOp.op.value('@PhysicalOp', 'NVARCHAR(75)') AS Operation,
RelOp.op.value('@EstimateCPU', 'float') AS EstimatedCPU,
RelOp.op.value('@EstimateIO', 'float') AS EstimatedIO
FROM @xml.nodes('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//RelOp') AS RelOp ( op )
ORDER BY [EstimatedCPU] DESC
SELECT RelOp.op.value('@NodeId', 'int') AS NodeId,
RelOp.op.value('@PhysicalOp', 'NVARCHAR(75)') AS Operation,
RelOp.op.value('@EstimateCPU', 'float') AS EstimatedCPU,
RelOp.op.value('@EstimateIO', 'float') AS EstimatedIO
FROM @xml.nodes('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//RelOp') AS RelOp ( op )
ORDER BY [EstimatedIO] DESC;
WITH ExPlan AS(
SELECT RelOp.op.value('@NodeId', 'int') AS NodeId,
RelOp.op.value('@PhysicalOp', 'NVARCHAR(75)') AS Operation,
RelOp.op.value('@EstimateCPU', 'float') AS EstimatedCPU,
RelOp.op.value('@EstimateIO', 'float') AS EstimatedIO,
RelOp.op.value('@EstimateRows','float') AS EstimatedRows,
RelOp.op.value('(./*:RunTimeInformation/*:RunTimeCountersPerThread)[1]/@ActualRows','int') AS ActualRows
FROM @xml.nodes('declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//RelOp') AS RelOp ( op )
)
SELECT e.NodeId
,e.Operation
,e.EstimatedRows
,e.ActualRows
,(e.EstimatedRows/ActualRows) AS EstActualRatio
FROM [ExPlan] e
WHERE e.EstimatedRows > 0
and e.ActualRows > 0
ORDER BY [EstActualRatio] ASCVocê pode criar outros selects para diversos casos, mas com isso já temos uma base.
