O SQL Server é uma ferramenta poderosa que muitas vezes é subutilizada. Muitos usuários desconhecem os recursos já disponíveis no SQL Server, como o Extended Events. Neste artigo, exploraremos como usar o SQL Server Extended Events para monitorar e analisar deadlocks.

Deadlocks são ocorrências comuns em ambientes de produção e é importante entender suas causas raiz. Felizmente, o SQL Server fornece uma solução integrada para capturar e analisar informações de deadlock usando o Extended Events.

Para começar, precisamos consultar a visualização do sistema “sys.fn_xe_file_target_read_file” para acessar o rastreamento de evento estendido “system_health”, que está em execução por padrão. Esse rastreamento captura uma riqueza de informações, incluindo eventos de deadlock.

O script fornecido abaixo demonstra como recuperar dados de deadlock usando o Extended Events:

/* Declaração das variáveis #DeadLockXMLData para armazenar cada XML de deadlock do evento estendido
#DeadLockDetails para armazenar informações de processo, vítima e aplicativo de deadlock
@GetDeadLocksForLastMinutes Para quantos minutos observar */

SET NOCOUNT ON ;

CREATE TABLE #DeadLockXMLData(
    DeadLockXMLData XML,
    DeadLockNumber INT
)

CREATE TABLE #DeadLockDetails(
    ProcessID nVARCHAR(50),
    HostName nVARCHAR(50),
    LoginName nVARCHAR(100),
    ClientApp nVARCHAR(100),
    Frame nVARCHAR(MAX),
    TSQLString nVARCHAR(MAX),
    DeadLockDateTime DATETIME,
    IsVictim TINYINT,
    DeadLockNumber INT
)

DECLARE @DeadLockXMLData AS XML,
        @DeadLockNumber INT,
        @getInputBuffer CURSOR,
        @Document AS INT,
        @SQLString NVARCHAR(MAX),
        @GetDeadLocksForLastMinutes INT;

SET @GetDeadLocksForLastMinutes = 5;

/*INSERIR OS DEADLOCKS DO EXTENDED EVENTS EM TABELAS TEMPORÁRIAS E FILTRAR APENAS OS DEADLOCKS*/
INSERT INTO #DeadLockXMLData(DeadLockXMLData, DeadLockNumber)
SELECT CONVERT(XML, event_data) DeadLockXMLData,
       ROW_NUMBER() OVER (ORDER BY Object_name) DeadLockNumber
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME = 'xml_deadlock_report'

/*INICIAR UM CURSOR PARA PERCORRER TODOS OS DEADLOCKS, POIS VOCÊ PODE OBTER MÚLTIPLOS DEADLOCKS EM PRODUÇÃO E DESEJAR TODOS ELES*/
SET @getInputBuffer = CURSOR FOR
    SELECT DeadLockXMLData, DeadLockNumber
    FROM #DeadLockXMLData

OPEN @getInputBuffer

FETCH NEXT FROM @getInputBuffer INTO @DeadLockXMLData, @DeadLockNumber

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Document = 0
    SET @SQLString = ''

    EXEC sp_xml_preparedocument @Document OUTPUT, @DeadLockXMLData

    /*INSERIR DADOS DO DOCUMENTO ANALISADO DO XML PARA TABELA TEMPORÁRIA PARA LEGIBILIDADE*/
    INSERT INTO #DeadLockDetails(ProcessID, HostName, LoginName, ClientApp, Frame, TSQLString, DeadLockDateTime, DeadLockNumber)
    SELECT ProcessID,
           HostName,
           LoginName,
           ClientApp,
           Frame,
           TSQL AS TSQLString,
           LastBatchCompleted,
           @DeadLockNumber
    FROM OPENXML(@Document, 'event/data/value/deadlock/process-list/process')
    WITH (ProcessID [varchar](50) '@id',
          HostName [varchar](50) '@hostname',
          LoginName [varchar](50) '@loginname',
          ClientApp [varchar](50) '@clientapp',
          CustomerName [varchar](100) '@clientapp',
          TSQL [nvarchar](4000) 'inputbuf',
          Frame nVARCHAR(4000) 'executionStack/frame',
          LastBatchCompleted nVARCHAR(50) '@lastbatchcompleted')

    /*ATUALIZAR O SPID DA VÍTIMA PARA DESTACAR DUAS CONSULTAS SEPARADAS, O PROCESSO (QUE CRIOU O DEADLOCK) E A VÍTIMA*/
    UPDATE #DeadLockDetails
    SET IsVictim = 1
    WHERE ProcessID IN (
        SELECT ProcessID
        FROM OPENXML(@Document, 'event/data/value/deadlock/victim-list/victimProcess')
        WITH (ProcessID [varchar](50) '@id',
              HostName [varchar](50) '@hostname',
              LoginName [varchar](50) '@loginname',
              ClientApp [varchar](50) '@clientapp',
              CustomerName [varchar](100) '@clientapp',
              TSQL [nvarchar](4000) 'inputbuf',
              Frame nVARCHAR(4000) 'executionStack/frame',
              LastBatchCompleted nVARCHAR(50) '@lastbatchcompleted')
    )

    EXEC sp_xml_removedocument @Document

    FETCH NEXT FROM @getInputBuffer INTO @DeadLockXMLData, @DeadLockNumber
END

CLOSE @getInputBuffer
DEALLOCATE @getInputBuffer

/*OBTER TODOS OS DEADLOCKS COMO RESULTADO EM FORMATO DE TABELA DE LEITURA FÁCIL E ANALISÁ-LOS PARA OTIMIZAÇÃO ADICIONAL*/
SELECT DeadLockDateTime,
       HostName,
       LoginName,
       ClientApp,
       ISNULL(Frame, '') + ' **' + ISNULL(TSQLString, '') + '**' VictimTSQL,
       (
           SELECT ISNULL(Frame, '') + ' **' + ISNULL(TSQLString, '') + '**' AS TSQLString
           FROM #DeadLockDetails
           WHERE DeadLockNumber = D.DeadLockNumber
             AND ISNULL(IsVictim, 0) = 0
       ) ProcessTSQL
FROM #DeadLockDetails D
WHERE DATEDIFF(MINUTE, DeadLockDateTime, GETDATE()) <= @GetDeadLocksForLastMinutes
  AND IsVictim = 1
ORDER BY DeadLockNumber

DROP TABLE #DeadLockXMLData, #DeadLockDetails

Depois de ter o script, você pode executá-lo para recuperar as informações de deadlock. O script captura as colunas mais importantes do gráfico de deadlock, incluindo o ID do processo, nome do host, nome de login, aplicativo cliente, buffer de entrada, pilha de execução e o horário do deadlock.

Ao analisar as informações de deadlock capturadas, você pode obter insights sobre as causas dos deadlocks e tomar medidas para otimizar seu ambiente do SQL Server. É importante observar que a resolução de deadlocks requer um entendimento mais profundo das consultas subjacentes e do design do banco de dados.

Em conclusão, o SQL Server Extended Events fornece uma ferramenta poderosa para monitorar e analisar deadlocks. Ao utilizar o script fornecido neste artigo, você pode facilmente recuperar e analisar informações de deadlock em seu ambiente do SQL Server.