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.
If you’ve been paying attention, you’ll have noticed that I’ve done the rownumbering in reverse order, and added a dummy (RowNum 0) field at the top of the list – this is to make sure that, if the most recent record is a RESOURCE_MEMPHYSICAL_LOW record, that we can get results that include that value.
This all looks OK in theory. But we’re still getting stupidly high values for the SecondsPressure field, and wait – what’s this? Multiple ring buffer records with the same ID?
More importantly, he shows us how bad the situation is: is this something that happened for a couple of seconds, or is it persistent? This is a great walkthrough.
You receive the error message similar to the following:
Msg 2552, Level 16, State 1, Line 1 The index “blah” (partition 1) on table “blah_blah_blah” cannot be reorganized because page level locking is disabled
Immediately, you start double-checking yourself and verifying that it worked the previous night. You even go so far as to confirm that the same index was previously reorganized. How is it possible that it is failing now on this index. What has changed? Has something changed?
There’s an interesting troubleshooting story, but the important message is about setting up a good set of Extended Events so that you can troubleshoot these types of problems.