It’s a running joke among the more experienced (read, older) Microsoft Data Platform specialists as to whether you’re #teamprofiler or #teamexevents. I’m very much #teamexevents, but I really don’t care that you’re #teamprofiler. If you want to use the old way of doing things, that’s fine. However, I do have a bone to pick.
Why on earth would you recommend to new data professionals working on modern systems, let’s say at least 2016 or better, who are just getting going on this journey, that they should be using Trace?
What are you smoking?
Trace vs. Extended Events
We’re not just talking about where you are comfortable. I get it. You’ve used Trace/Profiler for 20+ years. Getting it going is all muscle memory. That’s fine. As the kids say, you do you.
However, I need to know, why would you recommend this to others?
You know as well as I do that the implementation of Extended Events is superior to that of Trace Events. Extended Events, all things being equal, puts less of a load on the system than Trace Events. Extended Events uses fewer resources. Extended Events has vastly superior filtering. Extended Events has functionality that Trace does not, such as, capturing the object_id of stored procedures so that you can aggregate calls there without having to clean the T-SQL (just one of HUNDREDS of examples). Extended Events can track causality. Every single new piece of functionality added since SQL Server 2008 (which isn’t even in support any more) has been added to Extended Events.
If you choose to inflict the pain of maintaining two different sets of monitoring code on yourself, that’s fine. You know that you’ll have to do it if you want to monitor new functionality like Availability Groups or what have you. That’s your choice.
Unfortunately, you, the older, more experienced data professional, is the person that is going to grow the next set of data pros. Why, oh why, would you burden them with Profiler and Trace?
Just Stop
I am absolutely not suggesting that you stop using Trace. That’s your call, not mine. I have nothing to do with it. I am suggesting (screaming it) that you absolutely stop recommending Trace to new people, running new platforms, that truly do not need to ever know what Trace is. They can start fresh with modern, superior, technology, They don’t need to deal with the shortcomings of Trace. Yes, Extended Events has some shortcomings, but guess what, you’ll have to deal with them regardless. Don’t introduce two things, and two sets of problems, when one will do the job, with a single set of problems.
Stop. Just stop.
GF: Why on earth would you recommend to new data professionals working on modern systems, let’s say at least 2016 or better, who are just getting going on this journey, that they should be using Trace?
Because the older folk have never learnt Extended Events? Trace & Profiler ain’t broken, so why fix it?
A part of the problem comes from the ever-increasing feature set that comes each new Version every two years. There is just too much to learn and very often the new features are disappointing (In-Memory OLTP, for example). It becomes a time-allocation problem. I have my work to do. I have to improve my skills in SQL Server where it will really help my job. I have to learn the extra features in SQL Server. Great. Extended Events do what Profiler & Trace does but better, but I have invested time and resources in Profiler & Traces. I have a script library that can be called upon. I have practical experience of it. I am supposed to dump it for something that Microsoft say is better. Everything they make they say is better. Their Marketing Dept. hates us.
The Microsoft SQL Server Team produces quantity and sometimes quality. It’s so hard to know what to sift through. I still haven’t learnt all of the DMVs yet.
I’m more likely to take your recommendation of it as something worth investing in. I will also wait and see which features are worth learning.
Actually, one could argue that due to the excessive load and lack of filtering, profiler/trace is more than a little broken and has been for years.
However, I hear you. As I said, you’re happy, know what you’re doing, and may not need to learn Extended Events. That’s fine.
I still stand by, I would not support recommending Trace/Profiler to new people and new organizations when they’re on new platforms. Some of their monitoring, if they use any new functionality since 2012, will have to be done using Extended Events anyway. Why not develop skills in that one platform? Why have new people develop skills on the out of date platform in addition to the new one?
I’m not saying you should change. I am saying it’s wrong to push the old technology in where it doesn’t make sense.
One simple reason I would do that (explain profiler) to other people is that it will take me 5mns to explain what they have to do and that’s all they actually need.
I have no doubt extended events is “better” but I don’t feel I need it for what I do…
If you just compare the code necessary to implement Extended Events for capturing one event, rpc_completed, as compared to the exact same code for Trace, it’s about 90 seconds compared to your five minutes.
Plus, I’m still where I was, if all we’re talking about is query performance, sure, trace does the work. As soon as we expand the conversation to a single additional bit of functionality introduced since 2008, we now have to support two different sets of functionality that do the same thing. Move to a single set, especially with new people on new systems. I’m not asking older people doing older work on older systems to change. There isn’t a need there. It’s moving forward that trace makes no sense.
Its been a while since I last looked at Ext Events o this may no longer be applicable but When is Microsoft/SQL Server Team going to free Extended Events from the bondage of XML? Not everyone is a follower of the “Everything is better in XML” religion. If capturing and storing it in XML is faster and more efficient fro SQL Server then fine but don;t force me the user to work with XML. Last I checked if you want to use the info Extended Events has captured like you could by piping a profiler/trace session to a table or file but it too had its quirks and was far more involved (a pain) . If Extended Events are so great and something they want to encourage as many old users as possible to embrace then when are they going to stop trying to force users to change how they like to see information versus how Microsoft wants us to see it? If this has changed in more recent versions since the release of Extended Events then please share this but I just checked and I’m not seeing that it has but I’m happy to concede being wrong about this.
Yep. It’s XML. I’m not a fan of XML. However, there are tons of scripts from smart people like Erin Stellato showing how to deal with it. Also, the GUI has a ton of functionality that most people don’t know about that eliminates the need to use the XML for a lot of work (not all). XML, while not a happy thing, shouldn’t be a deal breaker for using ExEvents.
But again, I’m not trying to encourage people who are currently using profiler/trace happily to switch. I’m trying to understand why anyone would push new people on new technology to have to learn the old tech?
I said in the post, several times, if you are happy with trace, keep using it. That’s fine. I can’t in good conscious stand by and listen to people recommend trace to new people on new systems though. That’s the issue. Not you switching. You stay. Please. Just don’t recommend the old way of doing things on new systems to new people.
Hear hear!
I definitely grew up with profiler/trace. It’s still often my go to.I’ll admit it, I’m comfortable with it. But…
I agree, I’d be loathe to teach new folks to go to it.
And honestly, extended events aren’t as hard as some seem to think.
Two weeks ago I had to setup one to track tempdb growth. I’ll admit I googled for a script, but it was straight-forward and worst case, I could have figured it out on my own since it’s not really that hard.
As for the XML excuse, bah.. it’s a great storage mechanism, but using the GUI or writing a query isn’t all that hard.
I agree… take the time to learn the new stuff, even if you keep using the old fro time to time.
Could not agree more Greg. The one thing I’d like to see done a little better from Microsoft would be documentation on some of the Extended Events. I found out through a ton of experimentation that most of the Extended Events for Query Store only work with undocumented trace flags and/or debug settings.
I can’t argue that Extended Events are perfect. However, try monitoring any of the modern functionality without them.
This is gold! I am a relatively new data professional and have already had several senior coworkers recommend profiler but this article provides me with all of the ammo I need to justify using extended events instead. The “less overhead†factor is so important and consistent with the same reason I prefer Adam Machanic’s sp_whoisactive. Even better than me doing the research on my own and finding out the pros and cons of both tools – I now have a blog post from an experienced data professional with to refer them to if they then question my research! Thanks Grant, this is an awesome post.
I’m happy this helps. Feel free to reach out if you need more. Also, be sure to track down Erin Stellato with SQLSkills.com to get more help. She’s amazing on this topic. Search through my blog for a lot of other tips and tricks with Extended Events.
Hi Grant,
My post above came across a tad angrier than I anticipated. To clarify a point, I don’t recommend the likes of Profiler/Trace to others per se and I certainly don’t disparage newer technologies on the grounds that they are new. However, when time is tight and a problem needs fixing, I fix it the way I know how to, the way I have tools for. If others are working with me and they are new to this, the practical example of my work is more powerful than the simple recommendation of Extended Events.
That being said, I am working my way through the MCP exams and I will surely come across Extended Events and the newer features. I do strongly recommend to all who start working with DBs here that they study for MCPs. Itzak Ben-Gans’ books are not at all bad.
I really don’t expect anyone to change on this (well, if you sat through one of my precons where I spend about 2 hours on Extended Events, you might). You have something that works. That’s great. If it ain’t broke, don’t fix it. I have a pretty simple belief. New technologies require new solutions and new processes. In some cases, that’s not true. In most cases, it is. Frequently, it’s enforced by the technology in question. For example, if you want to monitor Query Store behavior, you have to go to Extended Events. There is simply no way to do this in Trace. But, if you’re just looking to capture a few queries, Trace works. I just don’t want to teach Trace to new people running on new tech and I can’t believe anyone else would either.
Sorry. I made a typo: It’s Itzik Ben-Gan and he is singular, to the best of my knowledge, although he may be clustered or have Availability Groups.
I think we have Itzik’s books as read only copies of Itzik.
Good article and I generally agree. I’m old-school so tend to reach for Profiler when I want something quick and to see the results. But … I’m also mostly on Azure SQL right now so Profiler won’t work for me. With Azure Data Studio, I can at least use their default traces to watch some data as it goes by from the buffer without needing to set up Azure storage. In SSMS, I can still set up a trace to buffer, but I can’t really read it in the nice format that you can w/ an on-prem EE session.
I will say that setting up an EE session has gotten much easier since they were originally introduced. They’re also a lot easier to script and understand than a Profiler session was. If I were starting out today, I’d definitely lean towards EE. As it is, I still lean towards EE, but will reach for Profiler for a quick session if on-prem.
Oh yeah, it’s become much easier. Even though there are ExEvents in 2008/R2, I never recommend using them there because of the shortcomings of that initial release. It didn’t work well, at all. 2012 and up, and certainly Azure, different story.
Ha I’m guilty as charged – I still catch myself running a quickie Profiler when I’m in a hurry and I need to check for a specific RPC call. I’d really like to see an updated SQL Profiler GUI using XEs under the hood, but that might be too much of an effort.
Check out Azure Data Studio for just such a thing. Also, SSMS 18 and up has a XE Profiler. Here’s a blog post on it: https://www.scarydba.com/2017/11/07/extended-events-profiler-xe-profiler/
[…] 2012 just rearranged the landscape. They were awesome. I started using them. Now I’m on #TeamExEvents and I couldn’t be happier about […]