Organizando planos de execução gigantes

7/25/2011 9:16:00 AM By Felipe Pessoto

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] ASC

Você pode criar outros selects para diversos casos, mas com isso já temos uma base.