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
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.