There’s a war on in the SQL Server world. On the one side is Profiler (although, really, everyone uses Trace Events). On the other, the “new” (they came out in 2008 with a full GUI in 2012, so…) Extended Events. Lots of people have picked sides on this, including Microsoft.
New Trace Events
There are none.
All the new functionality of every sort from Availability Groups to Query Store to R & Python, have Extended Events created for them. Trace Events, and the technologies supporting them in the form of Profiler, are a dead end.
Don’t fear. While Trace is on the deprecation list, there doesn’t appear to be any fear of that technology being removed completely. At least it won’t be removed in the foreseeable future. A future which, when one considers annual releases, or faster, isn’t that far away.
All this means that if you wish to monitor any new functionality in any instance using 2012 or greater, then you have to use Extended Events. However, if you’re still in love with the layout provided for consuming Trace Events by the Profiler, you still get to use it.
Microsoft (and I) really wish you would move to Extended Events though. That’s why they introduced the XE Profiler in the release of SQL Server Management Studio 17.3.
XE Profiler
If you check out the Object Explorer pane in SSMS, down at the bottom, you’ll see a new folder, XE Profiler, as shown here:
Double clicking one of these shows you something that looks very familiar to those of us who have been using SQL Server since 2000 was released:
Click on that image. It looks a whole lot like the good old Profiler output that you’re used to.
WHOOP!
This means that Microsoft has finally ported Profiler and Trace Events into the modern SSMS.
Yeah, well, no. They haven’t. What they have done is something you could have done for yourself since SQL Server 2012. They created a Session for Extended Events that looks exactly like the Profiler stuff you’re used to. Then, they used the ability of the Live Data window, Extended Events GUI, to add columns from the Event package to the grid, so that the XE Profiler output looks like what you’re used to from Profiler. That’s all it is. They did some formatting for you.
End of the War?
So is this it? Is the war over? Is all that was really needed a GUI that emulated Profiler?
In my opinion, no. There is still one giant issue outstanding around the war between Trace Events (Profiler) and Extended Events. XML. See, the output of Extended Events, if you capture them to a file, is XML. You can use fn_xe_file_target_read_file to query the XML, but you still have to use XQuery to shred the output. Although you can use the Live Data to explore data, including grouping, filtering and some other really cool functionality, lots of people, myself included, would prefer to query that data directly. As long as it’s somewhat difficult to access the data from Extended Events, there’s going to be a tendency to fall back on Trace Events.
Conclusion
Microsoft is trying hard to meet us in the middle with the addition of the XE Profiler output to SSMS in 17.3. Those of us who have already picked the Extended Event side of things are probably not going to be overwhelmed by this addition. Those of us still focused on the functionality that Profiler provides may be partially swayed by this new GUI that resembles the views they hold so dear. Unfortunately, there’s still the issue of querying the data. I believe that will prevent a complete armistice from being signed, so the war is likely to continue.
Agreed. Not only is it difficult for most DBAs to query the XML, but it’s extremely slow. If i get an xEvent file that’s any decent size at all it takes forever to load into a table. So doing this at an enterprise level still isn’t what it should be.
And of course, the format of xEvents has always held back even experienced DBAs. It’s just not presented as well as trace was. Or maybe we’re just all more used to trace by now.
I think this is a place where we embrace the healing power of “and”. ExEvents, especially the XML is not presented well AND we’re used to trace.
I’m still whole hog on Team ExEvents, but I can be honest about it’s shortcomings.
My biggest issue with XE is the lack of ability to create a replay workload. In so many situations I want to be able to test new hardware, or a new VM configuration, or a new version, and replay is the only reliable way to compare apples to apples. Add that functionality to XE and Trace can go right in the trashcan.
There are mechanisms to get the distributed replay to use extended events. I’ll be pursuing that for the update on the query tuning book. Watch this space.
Will do, but I was just referring to replay itself, not necessarily distributed.
Good points about the XML format. I’ve tried using Extended Events to look at what’s going on in real-time, but it’s much harder to use when looking at a real-time stream that’s all XML instead of a grid that I can read easily. At least we can see it after the fact if we capture to a file, but it would definitely help for cases when you just need a quick look at what’s going on and want to see the details.
But the Live Data gui will show real time information in a grid. You don’t have to look at XML. I only do that when I want to query the information through aggregates, standard deviation, etc. If you just want to either explore data you collected, or watch it real time, the GUI works great, better than Profiler I’d say.
Tested it out locally and it does work pretty well. I don’t have similar options for Azure SQL Databases, though. I think I got the two confused. When I live stream from Azure SQL, all I see in SSMS is the XML. That makes it quite a bit harder to do basic traces like “what is the app actually doing when I click this button?” Locally, the XE Profiler works great for a quick view of what’s going on and is definitely easier to jump into than building a new XE Session.
I think once someone demonstrated how to get similar functionality to profiler for just watching things on a local SQL instance by choosing the template and selecting the appropriate items, that made a big difference in usability. I seem to recall that it wasn’t always that easy in the early days so we didn’t tend to try to use them as much as our old, familiar Profiler. 🙂
I haven’t tested or tried this with Azure SQL Database, so thanks for the input. As far as capturing Extended Events goes within Azure, yeah, you have to output to XML, but, that XML can be consumed by your SSMS Live Data window.
There’s a blog post. Thanks!
I’m excited about XEProfiler, _if_ Microsoft continues to invest in it. Give me the ability to save to a .trc file or trace table and most of my remaining needs are met. The problem with extended events is that while the underlying infrastructure is far better than traces ever were, the user experience is horrible and has not received attention from MS since introduction.
I seriously doubt we’ll see a .trc file. Since Extended Events can already write to a table or a file, where’s the need. Now, better ways to consume the XML, that’s possible. I actually really like the GUI. It took a little while, but I now think it’s better than Profiler.
There is another issue with eXtended Events vs. Trace Events: Event Notifications (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-event-notification-transact-sql). Still no options to trigger Event Notivications based on XE (or service broker as target for XE)?
I haven’t done it, but I know you can send XE to Service Broker. There are a couple of articles on that. However, I’d like to see a more solid method built into XE, I agree.
The SB target was added in 2017 and is (afaik) a viable option now. Downside is you need to upgrade to 2017 (which really is a good thing but it will take time and effort).
The XML issues raised in this article are really not much of an issue unless using TSQL. Use powershell and something magical happens – it runs many many times faster.
As for various notification methods via XE, true we are constrained in the methods available via a straight SQL Server method. Powershell works fabulously to read the XE Session live stream and send notifications/alerts in near real time. It will also prove to be much more reliable than other plausible methods.
[…] Read More (Community content) […]
Wrote my own in C# with memory optimized partition tables for write throughput.. I don’t think microsoft did that ? 😉
XEvents are great – this addition to SSMS helps to make it accessible to profiler lovers but if sql trace was just deprecated, everyone would be forced down the new path which would be better in the long run
They’re doing it, but sort of in a back door fashion. Azure SQL Database and Azure SQL Data Warehouse only support extended events. Managed instances looks like will only support extended events. The new tool, SQL Operations Studio is only going to have extended events.
I have written an article on sqlservercentral on how to load extended events into sql server quickly and possibly automate it if required : https://www.sqlservercentral.com/articles/load-extended-events-via-powershell-1