I know, I know. New habits are hard to learn. Many of us have been using SQL trace and the Profiler GUI for a very long time. And we know that we are supposed to move over to Extended Events (XE), but we postpone it for some later time. And then we give XE a try, and some thing doesn’t work as we want. So we go back to more familiar territories.
But XE has really grown on me over the last few years. I like to share the things that I initially didn’t like with XE, and how I overcame them. And also some other of my tips to make it easier to be productive with XE. I will deliberately minimize showing T-SQL and queries against the XE dynamic management views here. As you use XE more and more, you will probably use T-SQL to a higher degree. But this blog post is for those of you who want to “get into” XE and I find using a GUI is great as a starting point. Assuming the GUI is any good, that is. And I think the SSMS GUI is, for most parts.
Don’t use the ring buffer as target
This is possibly the single most important thing to remember. Some would argue that the limitation for the DMV where you read the data is limited to 4 MB as the reason, and I can appreciate that. But more important is that SSMS won’t parse the data for you. XE is XML based, meaning that when you look at the XE data you get XML. And SSMS does nothing to help you for the ring buffer. It will just show the XML as is!
To the left you have what SSMS show you for the ring buffer and to the right you see what you get when you click the hyperlinked XML: the raw XML.
Use the file as target
So the obvious target would then be to use a file instead:
Note that the wizard to create event session defaults to the ring buffer. So, if you use the wizard, you probably want to change that to a file instead.
Use the targets instead of “watch live data”
Sure, sometimes you want to see things rolling by, as they happens. But saving to a target and looking at that target instead of the live data is more powerful. See the section about sorting etc below.
Show fields as columns
Bu default SSMS only show the name and timestamp fields. But all you have to do is to right-click in a filed at the lower half and select to show that as a column.
Explore sorting, grouping and aggregations
For this you need to look at a trace target, not the live window (except for filtering), but you can display a target even if the trace is currently running. Then just explore and find out that you can easily sort, group and aggregate the data:
Use SSMS to explore events, columns, etc
There are so many things you can capture with XE. But it can also be a but daunting. In my SQL Server 2017 instance, I have 1511 events. Compare that to the 180 you have for the old trace engine! So, XE is definitely richer, but you probably won’t browse through all 1500 events to find whichever could be interesting for the task at hand. So, create an event session using either the wizard to the “real GUI”. Just type letters and the GUI will filter on those, as you type:
Don’t expect events etc to be documented
For the old trace engine, we could read about all events and columns in Books Online. The “documentation” for the XE objects is instead in the DMVs, like for instance sys.dm_xe_objects has a “description” column. You don’t have to use these DMVs directly, though. Follow my above advice and SSMS will pick up those descriptions for you:
Use the “XEvent Profiler” that was added in SSMS 17.3
MS has probably noticed that there is a hesitation to pickup on XE. In SSMS 17.3, they added the XEvent Profiler in SSMS. This is just a quick way to start an XE session with a live data window. Nothing fancy, really, but having it in the “root” of SSMS makes it very snappy to kick of that trace quickly. And you can also customize it. See my earlier blog-post on this.
Explore other targets
The Wizard only allow you to select from two target. The dreaded ring buffer and the file target. But there are other interesting targets. Like histogram, to for instance show how many SQL commands were submitted for each database. Or the event_counter which just counts how any of each events occurred, for instance how many attentions, deadlocks and log escalations have we had. There are other targets as well!
Watch out for that latency
By default you can wait 30 second after an event happened until it has been delivered to the target(s). This is fine for a trace that you run into a target and analyze that target later. But when you want to do something interactively it feels like ages. I’m thinking of the “watch live data” functionality. The Wizard doesn’t let you specify the value for the MAX_DISPATCH_LATENCY parameter (of the CREATE EVENT SESSION command). So, use the “real GUI” or just script for the wizard and add that parameter. Of course, the shorter latency, the higher overhead. So, use with care.
Note that filters and actions are added per event
You can for most cases think of actions as “extra columns” you want to add. Anyhow, XE is flexible in many ways. For instance you might want to filter one event on something, and some other event on something else. Or accept the overhead of collecting one action for one event but not the other. But the wizard doesn’t work this way. The filtering and actions you specify will be applied for all events in the trace. You can of course script from the wizard and modify that script. But the “real GUI” os more true in the sense that you specify filter and actions for each event.
Don’t be afraid to use T-SQL
You can of course automate and customize enormously when you start using T-SQL to a higher degree. As usual, you can script things in SSMS that can serve as a great starting point. Below is an example of a query that “parses” the XML for a ring buffer trace into columns in the result set:
SELECT
event_data.value('(@name)[1]', 'varchar(50)') AS event_name
,event_data.value('(@timestamp)[1]', 'datetime2(0)') AS time_stamp
,event_data.value('(data[@name="statement"]/value)[1]','nvarchar(50)') AS statement_
,event_data.value('(data[@name="duration"]/value)[1]','bigint') / 1000 AS duration_ms
,event_data.value('(data[@name="cpu_time"]/value)[1]','bigint') /1000 AS cpu_ms
,event_data.value('(data[@name="logical_reads"]/value)[1]','bigint') AS logical_reads
,event_data.value('(data[@name="physical_reads"]/value)[1]','bigint') AS physical_reads
,event_data.value('(data[@name="row_count"]/value)[1]','bigint') AS row_count
,event_data.value('(action[@name="database_name"]/value)[1]','nvarchar(128)') AS database_name_
FROM #myXeData AS evts
CROSS APPLY StatementData.nodes('//RingBufferTarget/event') AS XEventData(event_data)
--ORDER BY time_stamp
ORDER BY duration_ms DESC
Note that I have saved the trace into a temp table. If you don’t, those XQuery operations will take ages.
Etc
I feel I could go on and on. But I’ll stop here. I might add something at a later time, if I come to think of something obvious. Feel free to add your tips in the comments.
4 Replies to “Tips for getting started with Extended Events”
Comments are closed.