On Monday this week I had an interesting exchange on Twitter with a bunch of folks who are die-hard Profiler/Trace users, and have no interest in using Extended Events. To wit:
Now, Denny and I are good friends, and his tweet didn’t upset me in any way, it just got me thinking. Why are DBAs and developers so resistant to using Extended Events? I have some theories, but I realized I should collect some data.
Therefore, whether you:
- Have never tried Extended Events,
- Have tried Extended Events but would rather keep using Profiler/Trace,
- Use Extended Events occasionally but still prefer Profiler/Trace,
I want to hear from you. Whatever the reason, I want to know – so please leave a comment below. My goal is to understand what the main challenges are so that I can then provide options and solutions, or create Connect items for the product team to address any gaps in functionality.
Extended Events *is* the replacement for Profiler/Trace; it’s not going away. I really want people to be prepared for the time when Profiler and Trace are removed from the product. And I want to provide feedback to the SQL Server product team to address limitations that people find in Extended Events. If the feature is lacking something, we need to work together to create change.
Thanks in advance for your help, and if you haven’t tried XE, or are looking for a refresher, then please attend my webinar next Tuesday, April 5th at 12PM EDT: Kicking and Screaming: Replacing Profiler with Extended Events. I’d love to see you there and can help get you started with XE!
EDIT 2:37PM EDT: If you are still running 2008 or 2008R2, then our advice has always been to stick with Trace and Profiler. If you’re running SQL Server 2012 and higher, then I recommended Extended Events. Why? Because it wasn’t until SQL Server 2012 that every event from Trace had a comparable event in Extended Events. So,if your argument is that you don’t want to learn XQuery and XML because you’re on 2008 or 2008R2, I’m right there with you and will tell you that’s fine, wait until 2012 to use XE.
67 thoughts on “Why do YOU avoid Extended Events?”
I have tried to use it, but tend to fall back to profiler often.
Maybe because its different enough, and I simply don’t have experience enough with it on simple things, that I cant get my head wrapped around it.
A coworker of mine always says extended events, then sighs, then we both say, we need to learn this thing better. But…
So…would you say that you just don’t have the extra time to learn it? Or there’s something about it that doesn’t make sense to you?
I’m sure I could find the time if I really wanted to learn it. I just haven’t had the fire lit under me in awhile so I’ve gotten complacent in my learning. I haven’t looked at it in so long that I can’t say if there was anything that didn’t make sense. I don’t remember anything that confused me. I think when I would start to look at it some fire would break out and It would take a while to get back to it.
I saw you present on this at PASS 2013 and again last year in IEPTO2, so hopefully when SQL Server 2020 R2 is released you aren’t still having to evangelize! 🙂
I saw the exchange on twitter and thought about it a bit…some assorted reasons why I will use xevents when it feels justified, but still reach immediately for Profiler more often.
First, I’m still supporting a variety of instances from 2008R2 to 2014 (we’re putting down our last 2005 instance this month, thank God). One strength of Profiler is it is very (seemingly, at least) consistent between versions. Whereas xevents was rolled out more incrementally, with its -very- limited UI in 2008, and a gradually growing collection of events. When my lowest major supported version is 2012, here in a year or two, xevents will be a much more natural choice. There are some things I will use xevents for in 2008R2 (system_health and deadlocks, even though that one’s created for me already) but generally speaking I’m going to have to invest more time than usual to get through the xml fun with it, there.
So, say my lowest version was 2012…or why not have fun and just say every instance is on 2014SP1 just for grins! I still would find myself using Profiler. Extended Events is much more flexible and powerful, I grant you (one could also say, so is T-SQL compared to the SSMS GUI). For times when I “craft” a trace that may be running for a while, like a server side trace trying to catch a specific login, or some sort of bucketizer count for SP executions…extended events will be my thing to be sure. But there are also those heat of the moment troubleshooting traces…the ones where your events and filters are different every time, just something spun up adhoc and ran for a few seconds or minutes to check something. Sure, you have to be careful with it because a bad trace will kill performance, but as long as you ARE careful its a very quick thing to set up.
I tried going through the GUI in 2014 for xevents, seeing how many clicks it would take to do something equivalent to a similar custom trace starting from blank template in Profiler. In terms of time, part of that is me not being as used to it, but it still feels like a lot more clicks getting through that wizard, having to save the session and give it a name, etc. Profiler is the “cheap and easy” solution for that kind of thing. You could argue it is not as efficient as Extended Events, but in some cases like this, it may be more efficient for the SQL Server engine, but not more efficient for the DBA.
As we move to 2014, Extended Events is going to be much more heavily used in our environment for many things, but there still are some use cases where a Profiler-type interface allows me to work a little quicker. I know there are a lot of arguments against a Profiler type shell for xevents (and I got the brunt of them from Jon after making the suggestion, ha) but a simplified interface (even if not a Profiler clone…believe me I have a lot of complaints about the GUI design there, too) might be a nice complement to the full “you can do ANYTHING!!” interface/wizard.
Just my 2c.
Some valid points Nic, thank you for taking the time, I greatly appreciate it. I hear what you’re saying about the UI. Out of curiosity, is it feasible to leave existing event sessions in place (just not started) and then edit and start when needed?
Also, what part of the UI is the most complicated? Is predicate and actions for the events? Or something else?
Again, I really value this information, thank you.
That’s one idea, definitely. Although I like starting from a blank template and just adding what I need in terms of events and filters, so I would have to remove them each time, introducing more margin for error by leaving in filters I had forgotten, so its probably better to create one from scratch for these completely adhoc, unique impromptu traces/sessions.
Some bits of the UI that are a bit foggy for me (I grant you much of this will stem from my lack of familiarity, but I do leave open the possibility that the UI design might share some blame too)…the Events tab. By default they are ordered alphabetically, and there’s a lot of them. You can click Category to sort by category, or filter them with the multiselect lists, but for me, it’s just slower than popping open Profiler, selecting blank template, expanding Stored Procedure and checking RPC Completed and SP Completed, then expanding Errors and Warnings and checking User Error Messages and Exceptions or whatever else, for example. It’s just a bit quicker to get to what you want (if more limited in what you have access to).
The filter/configure side, also a bit foggy there but I could get used to it. To reemphasize that point, I completely could do my homework and become very used to Extended Events “tracing” and know exactly what to do to accomplish the same equivalent results as I would do currently with Profiler. And indeed I will, when they actually remove Profiler in SQL 2030 or whichever version they finally pull the plug in. But, here’s an example of clicks doing something very basic.
*Expand Extended Events
*Right click on Sessions
*Click New Session
*Type something (anything) into Session name
*Click Start the event session immediately after session creation
*Click Watch live data on screen as it is captured
*Click Category heading to sort
*Scroll down massive list to find execution category
*At this point you can see the Execution ones but Name is not sorted…some extra up and down clicks to find what you want, let’s say RPC_completed, and click it.
*Click arrow to add event to right pane
*Click Filter (Predicate)
*Click Click Here to add a clause
*Click Field to get a drop down, scroll down, select object_name
*Click Operator to get a drop down, scroll down, select like_i_sql_unicode_string
*Click Value, type spMyProcedureVersion%
That’s just to get a very, very basic one stood up. And then, to do the equivalent to hitting the Stop button, remember to right click on the session and hit stop, AND then right click and Delete to clean up after yourself.
It’s nice, but it’s like taking a helicopter to work. Maybe more trouble than its worth for simple, quick things.
This is great information – thank you for the detail. This is a point that other individuals have made, so I appreciate you documenting it here.
One thing to note, within the Event library window, you can Search Events, so rather than expanding Stored Procedure and checking RPC Completed and SP Completed, you can just type “completed” or “rpc_comp” and it will list events with only that in the name. Just something to try to save you a step or two?
I’m still a profiler user. I have my templates set up and it takes me a few seconds to get the trace i need running. I tried extended events when they came out but without a gui it seemed a lot of work to replicate what i already had. I know there’s a gui now, so my only excuse for not switching over is time(and laziness). It’s pretty high on my list of things to learn. If only there was someone at SQL Skills that had a month worth of extended events blog posts 😉
Thanks Steve 🙂 Time is a valid reason, that’s what I’m looking to understand. Thanks for the feedback!
I am MCT and my students often respond to this very question: “I love to merge the Profiler Trace with the System Performance Indicators Protocol of the OS. With easiness you get an interactive chart with further options and a table with the overall and current statistics.” And as long as this is not possible with equal or less efforts using EE they will stick to the Profiler. Hope this brings some light to your question. 😉 Best regards from Germany, Michael.
Hi Michael – thanks for the reply! To be clear, you mean the ability to import PerfMon data and look at Trace and PerfMon counter data at the same time? Thanks!
Yes, thats what I meant. 🙂
“Extended Events *is* the replacement for Profiler/Trace; it’s not going away.”
I could say the same about Profiler/Trace – it’s still in SQL Server 2016, which means it will be usable until the year ~2025. It’s not going away either.
Most DBAs still manage a mix of 2008, 2008R2, 2012, and 2014 servers. Profiler is a single tool with a consistent UI and experience across all of the platforms. As a consultant, I want to spend as little time as possible, teaching someone a technique that will work on as many of their servers as possible, and know that they can use it with confidence when I’m not around.
As a blogger/presenter/consultant, sure, I’ll use XE on my own projects – but I’ve got way more free time for learning and improvement than the typical DBA does.
Fair enough – consistent method across multiple versions. So perhaps it won’t gain traction until Profiler is truly gone, and the majority of shops are on 2012 and higher. I can’t think of another example in SQL Server where a feature replaced another, with new UI. System views from 2000 and before compared to DMVs in 2005+ is the closest I could think of – and really, the method (TSQL) was the same.
I was not forced to do anything yet that I cannot do with profiler. I have bunch of server side trace with my filters and fixed duration. Those are set up as SQL agent job. For each collection I have my queries with what I am looking for. Most likely lazy to get out of comfort zone.
I watched your videos few times, found not as simple as profiler.
Thanks for the feedback Taiob! Would you be willing to elaborate on what specifically about XE was not as simple as Profiler? Thanks!
Erin, I am dedicated to weening myself and *all* my company’s DBA’s off of Trace/Profiler. Easy to say; hard to do. I have provided them the signup link to your PASS class on April 5, but the reaction is like today’s Twitter feed: “I don’t wanna and you can’t make me!” lol
When it comes to using XE the inhibitor for me is my ignorance of XML. I know I should just suck it up and become proficient but there’s only so many hours in the day. I wear too many hats and have too many masters, so learning yet another language/technology is not a high priority. My company will not pay for training so I hunt for community events. I use the available free XE tools – JK’s addin for SQL 2008 is a LIFE SAVER (plus his conversion scripts!!!) and I use SSMS 2014 – to create XE sessions, directly view data, or script the code to query the XML.
I wish MS provided a comprehensive free visual (yes lazy person’s) tool with the convenience and backward compatibility of the PROFILER UIX, that works directly and easily with XE from SQL 2008 forward. XE session analysis should be done in the UIX or loadable to SQL localDB, shredable using EXCEL.
Thanks for championing this issue!
Hi Carm – thanks for your comments! To confirm, your challenge is that you’re using 2008/2008R2 so you either have to use XML or Jonathan’s add in, correct? If you’re using 2012 or higher, what are you trying to do with the XML that you cannot do in the UI?
I completely understand XML as a limitation – I loathe XML, I pull scripts from Jonathan’s posts all the time. But I find that most (all?) of the time in 2012 and higher, I can get the data that I need via the UI. Let me know!
Erin, after your class today I’d say with XE on 2012 or greater I am good, just need a bit of hands on with the *new* toolset. I was not aware all the XE UIX capabilities (2012+) as well as the csv and table export capabilities. Funny how a little education can alter one’s perspective. The (XE) earth is no longer flat! I guess my *real* issue was frustration with shredding XML – now there is no reason. Will be watching for your scripts from the session. Let me know if there is anything else I can do. Thanks again. #iwantroshare
Carm – glad there was something in the session to help you, that’s great! And I am not a fan of XML, so I get where you’re coming from 🙂 The scripts will be up on our Resources page soon, keep me posted on how it goes!
The latest SSMS for SQL 2016 is a free download. Does it satisfy this request? “I wish MS provided a comprehensive free visual (yes lazy person’s) tool with the convenience and backward compatibility of the PROFILER UIX, that works directly and easily with XE from SQL 2008 forward. XE session analysis should be done in the UIX or loadable to SQL localDB, shredable using EXCEL.”
I’ve barely started using it. We were on 2005 for quite some time so couldn’t use EE then. Now we’re mostly 2012 or higher, but it’s a big shift. I’ve started creating some EE’s to track basic things and write to files. One issue that I did have was trying to set up some events and triggers to trace mirroring failovers. With profiler that’s apparently a little easier to handle from what I’ve read, but I haven’t managed to get it working yet to send an e-mail on failover through the Extended Events. (Admittedly, I haven’t tried too much lately.)
I appreciate that they’re lighter weight and you can easily save them, start them, and stop them. I mostly need to take the time to become more familiar with them. I’m making an effort to use them instead of traces, but when I need something really quickly it’s easy to just take the default profile, drop a couple of the things I don’t need, add a filter, and start.
Hi Peter – thanks for replying! So it sounds like your main limitation is the time you have available to learn extended events. That’s fair. If you have a trace that works, you can have that running and then use Jonathan’s script (https://www.sqlskills.com/blogs/jonathan/converting-sql-trace-to-extended-events-in-sql-server-2012/) to create the XE session. It may take some tweaking, I’m not sure, I’d need to see the trace definition. Thanks again!
I am not purposely avoiding XEs. I’ve been wanting to seriously play around and get comfortable with them for at least 2 years at this point. Here are the reasons why I haven’t yet:
— Still have servers running SQL 2008 R2
— Already have server side trace setup with queries for quick analysis
–Because I haven’t had the time to get comfortable with XEs I fall back to the profiler when I need to troubleshoot something quickly. And I am VERY comfortable with the profiler.
For me it all comes down to either having a reason to escalate the project of rebuilding monitoring framework to XE/powershell because there are events that I need to look at or having no other option (for example dealing with SQL Azure)
Hi Veronika, thank you for your feedback! This is a great explanation and I completely understand – when you still have 2008R2 instances, it’s a challenge to use XE and so I’d tell you to stick with trace for those instances. I also understand that going back and forth between trace and XE isn’t always easy. Hopefully when all your instances are 2012 and higher you can make that leap over to XE. Thanks again!
I was part of the thread on Twitter, and for me – as others have noted – I have quite a few custom templates configured that I can fire up in a matter of seconds if I need to troubleshoot something (per application, filtered on logins, filtered for certain performance metrics, etc.). While I could likely create similar templates with XEs, I don’t see a compelling reason to switch my approach after so many years of using profiler. Proficiency, time, templates, etc. Plus, I can share my templates (export/import) with developers in the event they too would like to trace their applications – they are familiar with it as well. I just can’t see taking the time to teach them XEs – seasoned DBA’s have a hard enough time trying to use them.
Thanks for your comments Allen, I appreciate you taking the time.
I am a professional tuner consulting on SQL Server for almost 20 years now. For me it isn’t an 80-20 or even 90-10 thing. 99+% of the performance problems I need to find and resolve at clients can be done very simply and efficiently and quickly with a profiler trace. I have run my profiler tuning template to local disk on systems with thousands of hits per second with VERY little overhead. Then I take that run and aggregate it up by normalizing out the parameters and I have incredible ability to find what needs to be tuned based on any of the most important metrics and in either total or average values. I can still see individual calls. I can slice and dice and filter. I can compare one run to another taken after some improvements are put in place.
I still have clients using some instances of SQL 2000. Quite a few on SQL 2005. So that is either 16 or 11 years. Profiler/SQL Trace is still available in SQL Server 2016. So I can GUARANTEE you that like Denny I will still be using it 10-15 years from now on clients using SQL Server 2016 in years 2026 – 2031. 😀
P.S. I do acknowledge that there are indeed systems and problems out there that REQUIRE XEs to find and solve. But in my experience those are very, VERY rare.
Thanks Kevin, I appreciate your feedback and you taking a few minutes to write it up.
I’m in the same boat as Kevin in that I run a SQL Consultancy company who does a lot of tuning. We can find 99% of customers workload issues quickly and efficiently using a trace file to local disk and a rich set of tools like RML to aggregate, normalise and visualise.
Sure I’ve seen demos of how we can convert an extended events back into a trace file by some additional steps.
Sure there are some new events that aren’t covered well like the lighthouse page split demos. The reality is we use trace files for the “general” workload analysis (the 99% use case) and extended events if we really must for heart surgeon stuff.
Maybe this will change in the future as I become less lazy 😉
Event if I’m using more and more the xEvents, I used only one or two few times the profiler during the last 6 months. It was to help the dev team on their dev environment for a quick and dirty analysis. Also, I used it and still use it on SQL Server 2008.
But, now, I’m starting to be used to xEvents and like it. At first, it was really hard to switch from Profiler to xEvents. The GUI is not really the best one, you have to choose all these columns and filter each events one by one (but, I lately discovered you can select the events before choosing the columns or filtering, which is nice). Also, I’m working for few months on Azure SQL database and when using SSMS 2014, the GUI wasn’t useful. I forced myself to script my favourite sessions to quickly deploy them.
Creating the session is, in my opinion, one of the “no go” to xEvents. The other “no go” is querying all the generated XML. Really, it is painful even if I start to be accustomed. On SQL Server, this point is less a problem, as we have the Live Data windows. I use it often and it saves me some query time (and you could export the result in CSV or a table).
xEvents fives more flexibility (with more development efforts) but I really think it worth it.
It’s really good to hear that someone 🙂 is adopting XE AND even better, that you like it. The UI does have its weaknesses, but I think overall it’s a better UI than Profiler. I agree with you about XML, which is why I do everything in the UI. Great feedback, thank you for sharing!!
Most of my issues around XEvents are about consistent user experience. For example, although SSMS gives a nice management GUI for XEvents in SQL2012, XEvents weren’t supported in SSAS at all until SQL2012. Even then, SSAS did not get a GUI for XEvent management in SSMS until CTP2.3 of SQL2016.
So if you’re a DBA or Dev working on both SQL Server relational database and SSAS or work solely on SSAS, there are substantial obstacles to embracing XEvents until sometime later this year when SQL2016 is released.
I think that’s an important point of discussion… That is, is a feature fully realized when there’s only T-SQL interface? I’d argue that several features, like XEvents and Service Broker, are inhibited from wide adoption without good GUIs built into SSMS.
Having said all that, XEvents are an excellent arrow in your quiver of performance troubleshooting tools. And I appreciate your continued evangelism of this feature set. Everyone who’s stuck on Profiler/Trace needs to learn XEvents as soon as practical and your lesson is the one I always direct people to watch. 🙂
Just my two cents…
Thanks for promoting my sessions when the opportunity comes up, I *greatly* appreciate your efforts.
I understand about SSAS…I get asked about that maybe every other time I present (or every third time). And as someone who doesn’t use SSAS, I admit that it’s not something that I miss. But your point is extremely valid.
And, I COMPLETELY agree about the UI. Everyone raves about PowerShell and command line stuff, but in the end, people still really like a UI, and Service Broker is another great example of something that’s missing one.
Good, strong points. Thank you for taking time to comment!
You told me to stay on Trace/Profiler!!!!!!!!
Although we are upgrading to 2014 this FY, so hopefully start to switch over….
I love profiler, even though it has it’s quirks.
It’s handy when I go to another developer’s box and need to do some quick troubleshooting. And by that I mean, very quick.
I don’t have to run scripts to setup events and remember to shut them off.
The extended event system is great for the standard machines you manage, but for many instances I don’t want to hassle with them.\
I agree with Denny — from my cold dead hands
Thanks for the feedback Dennis – I understand the ease of setting up a trace.
Just being Lazy. Profiler has been my friend for the past 16 years.
Thanks for the comment!
Another item to point out Erin, is I can very easily train fellow (non-SQL) employees to use profiler.
It can be used for a multitude of trouble-shooting and analysis problems, from performance to determining what an application is doing.
Plus, in the early days, I used to use it to figure out what Qry Analyzer and SSMS was doing under the covers.
But training other .net developers or QA people to use extended events — that’s a challenge. They won’t want to bother
It’s too complicated for their demanding schedule.
Dennis – thanks for your comments. Would you be able to elaborate on what, exactly, is a challenge about teaching the .NET developers or QA folks to use Extended Events? Which part of it, specifically, is complicated? Thanks!
They don’t want to learn more SQL. Most of them use entity framework or nHibernate and separate themselves from SQL.
extended events is a hasstle to learn and they won’t do it. But they will use profiler as it’s a UI app that’s easy and fast to run
Just being Lazy.
For me, the only place I can play with XE is on my own laptop. We are stuck on 2008R2, and due to the permissions requirement to use XE we are not able to (control server, and yes not even on Dev environments). We also aren’t allowed to have an existing session we can alter.
So this reminds me of something I read a while back, surmising that 2012 had only a 1% share of installations.
Granted this will be inaccurate now but the point stands – is use of XE simply reflective of the version dstribution, specifically new versions that include XE and have permissions and a useful gui? I wonder what the distribution is like now.
There are many things XE can’t do yet (as far as I know):
Replay traces (step by step or in total (run the trace, trace the run))
Join them with perfmon traces to correlate windows events with SQL Server events
Feed them in the database engine tuning advisor
Logging is one thing, and XE seems to be better than profiler for that, but then for using the logs I thing profiler has currently still more options than XE, since for any complex reporting I go via SQL tables anyhow.
You’re correct, currently extended events cannot replay traces, be reviewed side-by-side with perfmon in the same UI, be used with the tuning advisor.
Of your usage with trace/Profiler, what percentage of your time is spent on those three tasks, versus the other tasks you do with trace/Profiler?
Also, can you explain more about the logging you reference in your last sentence? I’m not sure what kind of logging you mean, and I don’t know what you mean by “for any complex reporting I go via SQL tables anyhow.” Thanks for the feedback!
A good place to start with the adoption of XE’s would be MS Premier Support. Since XE has come to light, I’ve probably opened 30+ PSS tickets with MS and not a single engineer has ever asked me to give them XE data. But to this day, nearly every one has asked me to open up Profiler for them. It’s rather sad that the support staff for the product doesn’t embrace and pass on knowledge of the replacement to Profiler. If Premier Support embraced XE and showed customers how they just solved the customers problem using XE, I would think Profiler would go away pretty fast…
That’s a great point and I will make sure to pass along this information to the team at MS. I appreciate the feedback1
I’d like to add my 2 cents to this discussion. I often use SQL Profiler to exactly pinpoint db activity related to a particular action in an application or site (I click a link on a site we’re developing and I want to see exactly what the new page is doing db-wise). For this I need realtime information and I can’t miss a single SQL call.
I tried to get Extended Events to do the same thing for me … ‘Watch Live Data’ is not really behaving the same way as profiler because it seems to have some internal buffers that always (?) appear to hold on to some data before displaying it (more events have to come in before old data is properly displayed).
I just don’t feel I have the pinpoint accuracy I get with SQL Profiler. I love many of the new features of Extended Events but for this particular use SQL Profiler is better suited for now.
Thanks for your feedback! My responses below.
You stated “I need realtime information and I can’t miss a single SQL call.” This suggests two things: 1) that XE doesn’t display information fast enough and 2) you’re missing SQL events. I’ll address the first item in the next answer. In terms of missing SQL events, there is an option in XE that defaults to allow single event loss. The other options are no event loss and buffer-level event loss. You had no control over this in Trace. If you were viewing data in the Profiler UI, you had the potential to lose events. In XE you can configure it. I recommend leaving it at single event loss. I doubt you’re losing events unless you have an extremely high volume system and you haven’t properly configured the internal buffers.
Speaking of internal buffers… you said, “‘Watch Live Data’ is not really behaving the same way as profiler because it seems to have some internal buffers that always (?) appear to hold on to some data before displaying it (more events have to come in before old data is properly displayed).”
You are correct in that there are an internal set of buffers to which events are sent before they are sent to asynchronous targets and the live data viewer. You can configure how quickly events go from those buffers to the target or live viewer (it’s on the Advanced tab for the event session, set the Maximum dispatch latency to 1 (second), the default is 30). The data may not feed into the live viewer quite as quickly as it does in the Profiler UI, but typically, it’s fast enough. You can also configure the size of those internal buffers – perhaps you haven’t allocated enough memory for them based on the volume of events you’re seeing? (Advanced tab, Max memory size and Memory partition node)
So, when you state that you don’t feel you have the pinpoint accuracy that you do with Profiler, it’s because the events don’t show up as quickly and you think you might be losing events? I am hoping the items mentioned above address that. I’d also encourage you to review Jonathan’s post on the overhead of Profiler (http://sqlperformance.com/2012/10/sql-trace/observer-overhead-trace-extended-events) to understand the impact it can have (particularly when compared to XE).
I am a Profiler user now, but will try to study XEvent.
I know XEvent is much more efficient and more powerful, but XEvent doesnot have one that Profiler has: I could define the template on my stage server, then I can use the template for any instances if I connect to these instances using profiler.
whatever, I will try to study XEvent and share the tech to our team.
I have another question, can you help to answer it? very appreciated.
after I connected some trace files, I can use RML ReadTrace tool to summary the statements running on the instances, is there anyone for XEvent? If absolutely yes, I think it will much more usable for our team.
thanks and appreciate your answer.
Extended Events does have templates, just as Profiler did. Jonathan has a post about how to customize them: https://www.sqlskills.com/blogs/jonathan/customizing-extended-events-templates-in-sql-server-2012/ And remember that once you create an Event Session, you can always script it out and then run that script on another instance to create the same Event Session.
As for your other question, the most recent version of RML Utils can be used to process an .xel file from Extended Events. But, depending on what you used ReadTrace for, you may be able to get the same information in the XE UI.
very appreciated. thanks a lot for your quick response.
I think our team will have much more fun for using XEvent in later times.
I use XEvents but the learning curve is steep and the documentation quite poor. The only reason I don’t use Profiler is that we mostly use SQL Azure which doesn’t support it (yet?).
The real time XEvent view in SSMS doesn’t work on SQL Azure, so it’s either Azure Blob Storage or the ring buffer (yuck!).
I wouldn’t expect the SQL Server team to backport Profiler to Azure. I’d work on being more comfortable with XE and (for the moment), using XQuery to read data in the ring buffer (yuck, I agree).
I’m .Net developer and sometimes I just want to see what SQL is being generated from my ORM and hitting SQL Server. In profiler it is super simple and quick.
Forced to use Extended Events due to SQL Azure. I can collect data but its in XML and extremely painful finding ways to view and analyse this data. I am short on time so annoying when you feel like you have to take 10 steps back just to do something that used to be easy.
I guess i have to dedicate time to learn to query the XML data and save my own custom reports. Just seems odd when i am sure many DBAs will be after the same reports but yet we all have to go away and create our own queries to see what queries are hitting our database. Falls short of expectations.
Well, just upgraded my SSMS v17.3 and see this new node appears now – XE Profiler.
OK, tried it.
Honestly – will not use it.
Just waiting for the events to be listed so very sloooowly and in chunks – a NO-GO.
Back to what works fast and is always there for you (and no unnecessary learning of yet another thing with no clear benefit).
So to be clear, in SQL Server you actually use the Profiler UI, and you just collect the events there and then save them to a file when done? You do not use a server-side trace then…
Ok. So there’s a ton of overhead in using Profiler, in case you were not aware:
If you are aware and this doesn’t seem to negatively affect your production environment, then just keep using Profiler.
The XE Profiler dispatches events after 5 seconds, so if that seems slow to you then again, keep using Profiler. Just be aware that if the events can’t be pushed to the UI fast enough, the executing thread has to wait and it’s possible to lose events.
Thanks for the comment!
I’ve looked at extended events, but I use a library of stored procedures and functions that create Profiler traces, and can be called with various canned scripts to generate specific traces; this allows users to run these traces as needed, and be automatically e-mailed the trace output. I just haven’t had the time or inclination to rewrite this library to use extended events.
I probably should do so, though.
Being that it’s been over a year since the last response to this I’m not sure if anyone still checks this but in case they do here goes…
I’m no Extended Events expert, I did look at it and give it a try but that was a while back so things may have changed. Why did the implementation of Extended Events require such a radical change from Profiler? Was it not possible for extended events to be introduced as an expansion to profiler? People are not comfortable with change especially when what they already do/uses does the job. That’s not to say change should not occur only that if you want widespread adoption of the change then said change needs to require a little change or as small a learning curve as possible to the user at least for use at the introductory level. Was there some reason extended events had to be introduced as a replacement to Profiler instead of an extension to it?
If Microsoft wanted widespread adoption of extended events to replace profiler, then it should have been done in a way that allowed existing profiler users to make the switch with as little effort as possible. Requiring users to step away from what they know (i.e. Profiler) instead of making it an option or expansion within Profiler was a mistake. Even if it would have meant more work on Microsoft’s part it would have ben better to expand profiler to be a front end/GUI for using extended events.
I think what irks many who use Microsoft’s products is how too often there is the air of arrogance at Microsoft of “We know what’s best for you even if you don’t” that results in forced changes to users often which are replaced not long after because Microsoft change sit mind. Today they push technology X as being the digital savior of mankind only to phase it outa few years later. Advancements in technology doesn’t mean you have to constantly change how you get something done simply for the sake of changing how its done.
All comments are monitored 🙂 so thanks for taking time to post your feedback.
Profiler could not be modified to do what Extended Events does, therefore it was necessary to “start over”. Profiler is external to the SQL Server engine, the Extended Events engine is part of SQLOS and provides a more flexible implementation that is able to support new events (for new and existing features) as SQL Server changes.
It’s understood that change is hard, but in this case there was no way around it. The tools team has added the XEvent Profiler in SSMS to try and help Profiler/Trace users with XE adoption. So, while I understand the resistance, one of the things to understand about XE is that it is more than a replacement for Profiler, it is also a new way to troubleshoot issues because of the additional options and flexibility it provides.
Hope that helps!
I assume most people do not like Extended Events because they are just not user friendly. Not to mention, the UI for extended events was written be a 3rd grader. Profiler is hardened, intuitive, and user friendly. XE’s are customizable via code, however, doing ANYTHING with the output requires a specialist in coding, unless you use that joke of a UI. There is your typical Microsoft failed attempt at documentation for the VS extensions. The output is good, the tools to work with them are plain crap.
Interesting perspective, thanks for sharing. I’ve found the UI quite easy to use when analyzing the output.
I avoid using them because “There is your typical Microsoft failed attempt at documentation” – I totally agree and subscribe to that. For instance just try to find out anything in Microsoft SQL Docs about filtering these extended events via code by using “like_i_sql_unicode_string”. The only way to find anything related is based on Google searches, stackoverflow and/or MSDN postings or other experts blogs how to use these basic things which is ridiculous in my opinion.
There are definitely ways in which Microsoft can improve documentation. One thing they have done is made it possible for folks to contribute, so if they see a gap in information, they can submit information that can be added.