Início / dba / Perguntas / 166773
Accepted
crokusek
Asked: 2017-03-10 19:55:05 +0800 CST

Leia system_health event_file em vez de ring_buffer para eventos estendidos de deadlocks

  • 772

Atualmente, estou usando esse desastre para localizar deadlocks recentes lendo o system_healthbuffer de anel de eventos estendidos.

select top 2000000000
      XEvent.value('@timestamp', 'datetime2(3)') as CreationDateUtc,
      --
      -- Extract the <deadlock>...</deadlock> tag from within the event
      -- Todo: Surely there is a better (xml) way to do this.
      --
      substring(convert(varchar(max), XEvent.query('.')), 
          -- start
          patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))),          
          -- end
          patindex('%</deadlock%', convert(varchar(max), XEvent.query('.'))) -
              patindex('%<deadlock%', convert(varchar(max), XEvent.query('.'))) + 11 -- 11 to include for '</deadlock>'
          ) AS XdlFile
 from 
    (
      select cast (target_data as xml) as TargetData
        from sys.dm_xe_session_targets st with (nolock)
        join sys.dm_xe_sessions s with (nolock)
          on s.address = st.event_session_address
       where [name] = 'system_health'
         and st.target_name = N'ring_buffer'
    ) as Data
 cross apply TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
 order by CreationDateUtc desc

Funciona bem, no entanto, os eventos não parecem durar muito tempo (como 24 horas?) Acho que essa é a parte do "buffer de anel". Agora me deparei com um link que estava lendo o system_health"arquivo" que possui informações semelhantes:

select event_data = CONVERT(XML, event_data) 
from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
where event_data like '%xml_deadlock%'

Este arquivo é o mesmo que o buffer de anel, mas permanece por mais tempo? Alguma desvantagem em usar um arquivo? Alguém com alguma habilidade em XML quer converter o script top?

O objetivo é copiar/colar o campo XdlFile em um novo arquivo e lê-lo diretamente no SSMS ou Sql Sentry Plan Explorer usando "File Open".

Links de buffer de anel:

https://connect.microsoft.com/SQLServer/feedback/details/754115/xevents-system-health-does-not-catch-all-deadlocks#tabs

https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/

http://www.sqlskills.com/blogs/jonathan/multi-victim-deadlocks/

https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/

http://www.mssqltips.com/sqlservertip/1234/capturing-sql-server-deadlock-information-in-xml-format/

http://blogs.msdn.com/b/sqldatabasetalk/archive/2013/05/01/tracking-down-deadlocks-in-sql-database.aspx

O erro de deadlock não está retornando o SQL de deadlock

Arquivo:

https://www.mssqltips.com/sqlservertip/3636/query-data-from-extended-events-in-sql-server/

@@versão = Microsoft SQL Server 2012 (SP3-CU5) (KB3180915) - 11.0.6544.0 (X64)

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Isso parece funcionar:

    with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
   from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where object_name = 'xml_deadlock_report'
) 
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc
    • 5

relate perguntas