3

I am querying system_health session and noticed that not all of the events are being returned. There were definitely few deadlocks today but these aren't in the output.

I came across this connect filing but from the comments the issue has been addressed in SQL Server 2008 SP2. But my version is SQL Server 2008 SP3 Standard. Here are some more details.

So is this a Bug or am I querying it wrong?

<RingBufferTarget truncated="1" eventsPerSec="31291" processingTime="209" totalEventsProcessed="6540" eventCount="1947" droppedCount="0" memoryUsed="4193813">


SELECT  TOP 1 e.event.value('(@timestamp)[1]','datetime') AS MaxDate, GETDATE() Today
FROM 
( SELECT ( SELECT 
        CONVERT(xml, target_data) 
        FROM sys.dm_xe_session_targets st 
        JOIN sys.dm_xe_sessions s ON 
        s.address = st.event_session_address 
        WHERE s.name = 'system_health' 
        AND st.target_name = 'ring_buffer' 
        )AS [x] 
        FOR XML PATH(''), TYPE 
) AS the_xml(x) 
CROSS APPLY x.nodes('x/RingBufferTarget/event') e (event) 
ORDER BY MaxDate DESC

MaxDate                     Today
2013-01-09 20:05:31.853     2013-01-11 15:22:37.887
Afroz
  • 1,017
  • 2
  • 12
  • 24

1 Answers1

5

It's possibly neither, since you could still hit the 8MB XML limitation with a 4MB ring_buffer configuration for max_memory which is the default. You also might be hitting a different bug in Extended Events that makes the timestamp get reported incorrectly that I blogged about here:

Incorrect Timestamp on Events in Extended Events

The only way to know which scenario you are running into is to add the action for collect_system_time to the events in the session and also add a file target to the session to capture all of the events in a media that isn't limited like the DMV is. I would point out that in SQL Server 2012, the system_health session has the file_target configured by default, so doing this in 2008 would be advisable IMO.

Jonathan Kehayias
  • 3,402
  • 1
  • 23
  • 23