Every time I talk about migrating from Profiler and Trace to Extended Events (XE), I seem to add something to my presentation. It’s not always intentional, and I don’t know if it’s because I just can’t stop tweaking my demos and contents, or something else, but here in London at IEPTO2 this week, I added a query that helps you see what event in Extended Events is comparable to the event you’re used to using in Trace/Profiler. While most of the events in XE have a name similar to the event in Trace (e.g. sp_statement_completed for SP:StmtCompleted), some mappings aren’t so intuitive. For example, SP:Starting in Trace is module_start in XE, and SP:Completed in Trace is module_end in XE. That’s not so bad, but if you’re ever monitoring file growths, note that the database_file_size_change event in XE is the event for the following four events in trace: Data File Auto Grow, Data File Auto Shrink, Log File Auto Grow, and Log File Auto Shrink.
This is a helpful query to keep around until you get really familiar with Extended Events.
…but what does this have to do with Extended Events?
As I started out, I was looking for something slick I do with a trace that I could replicate in Extended Events, and this was a great candidate.
The catch as I discovered, is that while file growths are captured in the default trace, they are *not* in the system health session…
Fortunately, you can create your own XEvent and Andy gives us the details.
I haven’t gotten a ton of comments, but I did get a few (thank you to those have responded!), and I decided to take one of them and create a Trace and create an Extended Events session and see how long it took for each. Jonathan has mentioned before that he can create an XE session as fast as a Trace, and I’ve been thinking that I can as well so I thought I’d test it. It’s a straight-forward Trace versus Extended Events test. Want to see what’s faster? Watch the video here.
I love the “I would pop up the timer on the screen but I don’t know how to do that” bit; very Friday afternoonish.
Back in April I wrote a post asking why people tend to avoid Extended Events. Many of you provided feedback, which I greatly appreciated. A common theme in the responses was time. Many people found that creating an event session in Extended Events that was comparable to one they would create in Trace took longer. Ok, I get that…so as a follow up I am interested in knowing how you typically use Profiler and Trace. If you would be willing to share that information, I would be extremely grateful.
Head over there and let her know. For science!
SQL Server 2016 improves the XEvent Linq reader scalability and performance. The XEvent UI in SQL Server Management Studio uses the XEvent Linq reader to process the events for display. Careful study of the XEvent Linq reader revealed opportunities for scalability and performance improvements.
I don’t know if this will push anyone in the direction of using Extended Events who isn’t already doing so, but I like the performance improvement here.
In SQL Server 2014 we have had 18 Extended Events and with Service Pack 1 we have received 1 more to be a total of 19 Extended Events for studying the Columnstore Indexes and the Batch Mode processing. In SQL Server 2016 that number has been greatly increased – there are whooping 61 Extended Events, that will give us an important insight into the Columnstore Indexes.
Even more important, Sunil & his team have given an own category inside the Extended Events – a category that is named Columnstore, which will ease the search for the basic columnstore events. Be aware though not all Extended Events related to Columnstore Indexes are included in that category – even including all channels will give you 41 Extended Events, while hiding the other 20 Extended Events, which are sometimes not categorised at all and at other times are stored under different categories, such as Execution or Error, for example. I believe the reason behind not changing the old Extended Events category is quite simple – Microsoft always looks for avoiding breaking existing applications.
There’s a lot here to digest, so read the whole thing.
Extended Event (XEvent) feature is available as public preview in Azure SQL Database as announcedhere. XEvent supports 3 types of targets – File Target (writes to Azure Blob Storage), Ring Buffer and Event Counter. Once we’ve created an event session, how do we inspect the event session target properties? This blog post describes how to do this in 2 ways: using the User Interface in SSMS and using T-SQL.
It’s nice to see Extended Events making their way into Azure SQL Database.
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.
Read this in conjunction with Erin’s request.
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!
Leave a comment on Erin’s post and let her know.
Very quick and simple and hopefully of use to people, this could easily be turned into a function. The full script is below and also available here on the Powershell gallery or by running Save-Script -Name Set-ExtendedEventsSessionstoAutoStart -Path <path>
This is indeed a quick and easy script, and quite useful when checking across a large number of instances.