Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - Codeberg Dave Mason - Counter Social

Transitioning From SQL Server Profiler To Extended Events

My first introduction to Extended Events happened a couple of years ago when I first learned SQL Server Profiler was deprecated. I'm not sure when Microsoft made the announcement, but by the time I found out, I think it had been known for quite a while. I opened SSMS, delved down to the Extended Events node, and fired up the New Session Wizard. I'm sure I fumbled around awkwardly for a while, did some Googling, fumbled around some more, and eventually cobbled together a session that kinda sorta mimicked a common Profiler trace I'd use for performance.

Thinking back, my initial reaction was probably something like "I don't get it.". XEvents just seemed like a new dog performing an old trick. Even though I concluded most of the SQL pros had already made the transition to Extended Events (XEvents), and that I'd be living in the past if I didn't do the same, I continued to use Profiler. It's too bad--my current employer at the time had SQL 2012 across the board. I missed a great opportunity there. Before I left that job, I stumbled upon this Paul Randal post. It helped me trouble-shoot a performance issue with a stored proc. "Ok, so there's one thing XEvents can do that Profiler can't do" I thought to myself. (Note: I don't know if that's a true sentiment or not--I never investigated further.) I also recall reading at least one of Jonathon Kehayias' (b|t) posts about XEvents, but I don't remember which one. Profiler would remain my go-to tool for the remainder of my time with that employer.

When I came on board with my current employer, I continued to use Profiler. There wasn't a good reason to use XEvents when I started--the newest version of SQL we were running was 2008 R2. Despite our use of "older" SQL versions, I continued to invest time in my career development and learn about the more recent SQL releases. I found myself reading more technical articles than I ever had before. I joined PASS. I found the treasure trove of videos there, including "Making the Leap from Profiler to Extended Events" by Erin Stellato (b|t) from PASS 2013. That is what changed things for me. Erin presents a compelling case for XEvents over Profiler. But I'll let you decide for yourself.


Extended Events - Recent Experience

A few months back, I got the green light to upgrade all of our SQL hosts. The oldest version I support will soon be SQL 2012. When I started upgrading the SQL 2008 R2 instances, I made the commitment to only use XEvents on SQL 2012+. I don't have the need to run a trace very often. When I do, it's almost always some derivative of a "performance" template. There is a session I created with the Session Wizard, which I've saved as a tsql script. I use it to create an identical XEvent session on every 2012+ instance. I only start the session as needed, which is not very often. If I need to tweak the session, I make whatever changes are needed using the UI in SSMS. After I'm done with the session, I'll either rename it and save it for later or delete it. Then I'll re-create my "standard" performance XEvent session from script again. Pretty easy.


What I Like

  • Session Persistance: sessions don't have to be recreated after SQL is restarted. Pretty self-explanatory.
  • No Separate Tool Required: everything session-related can be handled from SSMS. Create/modify/delete a session, stop/start a session, watch live data in GUI, query session data via tsql, etc.
  • Event Library Grid in Session Properties: I find it a little easier to find the correct event in XEvents than in Profiler. This is just a minor thing. Finding the correct event in Profiler was not a burden.
  • Unique Filters: you can specify a different filter (or no filter) for each event.
  • Tsql: when you script out an XEvent session, it's a lot more intuitive than the tsql of a scripted Profiler trace. There are no "magic number" parameter values for stored procs, object.property notation is used throughout, events and filters are easy to identify, etc.
  • Observer Overhead: XEvents are said to have significantly less overhead than Profiler.


    • What I Don't Like

  • Querying Session Event Data: sys.fn_xe_file_target_read_file is not as easy to use as sys.fn_trace_gettable. Ugh! XML! It's not rocket science, but I doubt I'll ever commit the proper syntax to memory. I'll forever be reliant on Google for this.
  • Duration Event Field: it's measured in microseconds. This is a nit-pick. I wish the unit of measurement could be changed to milliseconds. Or barring that, I'd like the option to have commas within the Int/UInt values for ease of readability.


    • Hits And Misses

    1. Live Data Grid
      • I like that the Live Data Grid is highly configurable. You can pick which columns to show in the table, search within columns, filter by column values, copy cell/row values to the copy/paste buffer, bookmark one or more rows for ease of navigation within the grid, sort by column, group by column, etc.
      • When there's a huge number of results, the grid feels really sluggish and slow. Maybe SSMS is to blame, not XEvents per se. Perhaps this is simply the reality of a UI element holding a huge cache of data.
    2. Selecting Multiple Events
      • In XEvents Session Properties, you can select multiple events for bulk selection/de-selection of Global Fields. Notice the check boxes have three check states: checked, unchecked, and indeterminate. I like this.
      • With multiple events selected, click the Filter tab. You can apply a filter to any of the Global Fields for multiple events. This is good too. But where are the Event Fields? If the selected events have common Event Fields (ie Duration), I'd like to see them available in the drop-down list so I only have to specify the filter once.
      • Again, with multiple events selected, click the Event Fields tab. Poof! They all disappeared. If the selected events have common Event Fields (ie Duration), I'd like to see them listed so I can select/deselect them en masse.
      		 SQL Server Extended Events Session Properties
      SQL Server Extended Events Session Properties - Filter Tab
      SQL Server Extended Events Session Properties - Event Fields Tab


    What's The Point?

    XEvents is pretty good, but it seems there is a significant group among us that continues to use and prefer SQL Profiler. A Tweet promoting Erin Stellato's webinar on XEvents led to a Profiler vs XEvents TwitterStorm. A few days after the dust settled, Erin asked Why do YOU avoid Extended Events? I felt I had too much to say within the constraints of the comments section, thus this post. If you have some input, I'd encourage you to add to the comments section on Erin's site.

    Comments

    Post comment