You may not see the data you expect in Extended Event Ring Buffer Targets….
Not sure how many of you have started using the new Extended Events feature of SQL Server 2008. For those who have not, there are several good resources out there already for you mostly done by the community:
https://technet.microsoft.com/en-us/magazine/2009.01.sql2008.aspx
I also have a few blog posts on our CSS blog that may interest you as well:
So enough plugging, what about this problem I refer to in the title? As we in CSS have used in XEvent (can’t help myself this is what we call this feature internally) and seen feedback from others in the community (See Jonathan’s Connect post at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=432548), a limitation has been found with the “Ring Buffer” target.
If you have an event session setup for a ring_buffer target and the data you feed the target exceeds 4Mb, you may not be able to retrieve all XML nodes from the target data. This behavior can look like “torn XML” where some of the pieces of a captured extended event cannot be seen.
Let me be more specific about this problem and what I mean by the 4Mb limit. Data is retrieved for “in-memory” targets such as ring_buffer from querying the column target_data from sys.dm_xe_session_targets. An example query for the installed system_health session looks like this:
select s.name, CAST(t.target_data as xml)
from sys.dm_xe_session_targets t
join sys.dm_xe_sessions s
on t.event_session_address = s.address
and s.name = 'system_health'
If you look at the definition of the sys.dm_xe_session_target DMV in our documentation, the target_data column is defined as nvarchar(max). But in reality this column this column is limited to 4Mb of formatted XML text.
The data produced for an in-memory target is binary as it is produced and consumed within the engine. But when you query the DMV we format the internal data into XML. It is the formatted, expanded XML data that is limited to 4Mb. Here is where it might be confusing. An XEvent target has a “max memory” for its buffering but that is for the binary version of the data. By default, this value is 4Mb, but you can change this when you create an event session. However, for “in-memory” targets you will never be able to see more than 4Mb of formatted XML. It is very possible for the binary data collected in an in-memory target to be less than 4MB but the formatted version of the XML exceeds 4Mb.
You are more likely to see an issue like this when setting up an event session where a lot of text is formatted from the target (Some possible examples are events like xml_deadlock_report, sql_statement_starting,…) or if you use actions such as sql_text.
There are a few solutions here should you encounter the problem.
- Use a file target such as asynchronous_file_target or etw_classic_sync_target
- Set the MAX_MEMORY of your target for the event session lower than 4Mb. This second solution is not something that will guarantee you won’t hit the problem because it is impossible to determine what is the right size to avoid the 4Mb limit of the formatted XML You will need to experiment with whatever events and actions you are using for your session. When using this method (and even if you didn’t limit your memory), you can query the DMV and save off the results to a file periodically.
This problem can occur for any “in-memory” target including ring_buffer, synchronous_bucketizer, asynchronous_bucketizer, pair_matching, and synchronous_event_counter since all of these targets require you to query the sys.dm_xe_session_target DMV to retrieve your data. Note that the probabilities are higher for a ring_buffer target since this is just a “raw feed” of the event data while the others listed here are aggregations of events.
Bob Ward
Microsoft