Episode 182: XEvents

Episode 182: XEvents

Episode 182: XEvents 560 420 Carlos L Chacon

The very first podcast episode I recorded was about Extended Events. We’ve had several new versions of SQL Server since that time, so I thought it was time to bring up the subject again. We are joined by MVP Jason Brimhall and he shares his insights on Extended Events, what has changed, and how to get started.

Jason Brimhall

Our Guest

Jason Brimhall

As a Microsoft Certified Master/Microsoft Certified Solutions Master and SQL Server MVP, I have over 20 years’ experience in the technology industry, including more than 10 with SQL Server. I also earned a Bachelor’s Degree in Business Information Systems from Utah State University. One of the highlights of my career was co-authoring multiple volumes in the SQL Server T-SQL Recipes series of books. I am a frequent presenter at SQL Server events worldwide, which includes SQLSaturdays and User Groups.

I love finding that magic turbo button to make queries go faster which ultimately leads to huge relief and satisfaction for the client. I have done that sort of work for clients of all sizes throughout the world. I have experience with multi-TB databases as well as environments with thousands of SQL Server instances to manage.

Never Miss An Episode

Subscribe to get podcast notifications by email.

“Imagine, if you will, a spaghetti colander utilized in this case, where a spaghetti colander captures all the spaghetti, but lets the water and the other things run off that you don’t need. That’s really essentially what Extended Events does.”

Jason Brimhall

Episode 182: XEvents
  •  Carlos:             Welcome to another edition of the SQL Data Partners Podcast. I am Carlos L Chacon, your host, and this is Episode 182. I am joined today by our good friend Jason Brimhall. Hello, sir.

    Jason:              Hello, how are you doing?

    Carlos:             I am well, thanks, and we are joined as well, by Kevin Feasel.

    Kevin:              Howdy.

    Carlos:             Who hopefully won’t have to run to his other house and fix the power during this episode.

    Kevin:              Again.

    Carlos:             That’s right. So, our topic is Extended Events. Now, Jason is a friend of the program, having been here in Episode–

    Jason:              A long time ago.

    Carlos:             Yes, I was going to say Episode 40, is was I was thinking. And it is Episode 40, talking about compression. But, in today’s episode, we want to talk about Extended Events, so I think we’re going to give– history might be a strong word, but we want to talk a little bit about how they came about, and then get into what they’re for and how we use them. But before we do that, I do have a couple of shout-outs that I want to give. I want to give a shout-out to Mica Scalin, Ahmed Hohammed, Stacey Hamilin, Armado Rosas, Adam Quane, and then we have Rafa from Mexico, not the one from Charlotte. So not Rafa from– all of a sudden, I forgot Rafa’s last name. Oh, that’s horrible.

    Kevin:              Salas?

    Carlos:             Yes, there we go, Salas, there we go. And last but not least, Kristi Decker. So thanks, everybody, for connecting with us or interacting with us on social media in some way. We do appreciate it and wanted to give you a shout-out. So, our show notes for today’s episode will be at sqldatapartners.com/xevents, with an ‘s’, or at sqldatapartners.com/182. Okay, so Microsoft defines, if you will, if that’s the right word, Extended Events as a lightweight performance monitoring system that uses very few performance resources. So that, ultimately, I guess kind of helps us identify what Extended Events is all about. Now, Extended Events is somewhat of an evolution, and so what I wanted to do, first, was maybe talk about some of the predecessors that we had in previous versions of SQL Server, when it came to being able to monitor some of these things. Because in some cases, we actually still go it. Where I’m going here, or what I’m looking for here, is the Trace information, so there’s default Trace, and some of you may be familiar with configuring Server Side Traces and whatnot. So, maybe Jason, take us through a little bit about– again, history is not the right word, but maybe some of the genesis of the Trace and then how Extended Events came into play.

    Jason:              Yeah, no problem there. Let’s back up just a little bit, back to the definition of Extended Events. It’s not entirely just a performance monitoring system, it’s more or less environment monitoring system that allows you to monitor internals for SQL Server as well as performance, but a lot of times it’s a lot more than just performance that you might be monitoring. When you have everything from errors to being able to generate depots or stack traces with Extended Events, so it’s rather comprehensive at this juncture, and that’s kind of speaking to the evolution of it. If we start back with Server Side Trace or Profiler, the old tools from SQL 2000 days, there’s been quite a need for something that was far more comprehensive and capable of getting down into the nitty gritty details from the internal to SQL Server, monitoring far more features than, say, the Server Side Trace, and overall, the clunkiness or the heavy-handed approach that Server Side Trace and Profiler would apply to your server. And so what they’ve done is with the first iteration with SQL Server 2008, they released this thing called Extended Events at the same time Profiler and Server Side Trace actually became deprecated. It was announced as deprecated back then, but Microsoft’s updated the documentation since. Ssh, don’t tell anyone. But, at that time, what Microsoft did do was they flipped the whole monitoring paradigm on its heels. They did a complete reverse of their methodology and where the Server Side Trace method is going ahead and capturing everything and get it into some sort of memory bucket, then we’ll go ahead and filter out from there what we need, which goes into another memory bucket, and now we can go ahead and display it, which adds quite a performance hit to your system.

    Carlos:             Many pieces are touching all of that data, right?

    Jason:              Yeah, so they’re just capturing way too much data that just really was un-pertinent. They flipped that on its heels, so rather than capturing all of the data, they only capture what you truly need. So, imagine, if you will, a filter, or maybe a spaghetti colander utilized in this case, where a spaghetti colander captures all the spaghetti, but lets the water and the other things run off that you don’t need. That’s really essentially what Extended Events does. Only a small sub-set of what you really want is captured there, into this memory bucket for further evaluation, and then as more conditions are applied, the filter gets more and more condensed and difficult to pass through, per say, allowing you to just– or get through what you really want out of the whole thing. And so, that’s kind of where we stand with Extended Events now. In the early days, SQL 2008– I say early days, because at this point, that’s been 11 years.

    Carlos:             Yeah, that’s crazy. That’s when it was introduced, 2008, which we can now hang our hats on, because I’m sure none of our listeners are still running 2008.

    Jason:              Mmhmm, sure. I’m working right now to get some 2008’s moved and I’ve got some 6.5’s out there as well. Ssh, again, quiet.

    Carlos:             If it ain’t broke…

    Jason:              Yeah, something like that. But, what you do have to understand is 11 years old in SQL Server terms is like trying to fly a 500 year, 700 year old 747. Do you want to get on that plane? Do you really want to?

    Carlos:             Yeah, so ultimately, Extended Events provides us the ability to capture information about what’s going on in the SQL Server, and I think to your point, part of what we’re seeing is that there continues to be additional features added to SQL Server. So, one of the big ones, for example, was availability groups. So now, the question is, “okay, well, how are we going to collect information about the health of our availability group? I mean, yes, we have the dashboard, but are we going to continue to build for the Trace detail, or are we going to put that investment into the Extended Events?

    Jason:              That’s actually a very good question, because, where do we capture that information? Well, as of 2008, where we did not have availability groups, and if you guys can recall what was the major release feature back then, it was basically compression. It had some other (?) things, but basically it was compression. As of 2008, no new feature has Trace events being added into the Profiler or server side code set. It was deprecated 11 years ago. Microsoft has not added anything to that.

    Carlos:             I thought they’ve added a couple of things. I don’t think it’s quite–

    Jason:              Nope.

    Carlos:             No?

    Jason:              Nothing’s been added to Trace. Since 2008 you have 182 Trace events. As of 2019, you have 182 Trace events. It’s the same episode as well. So Trace has had nothing new added to it in 11 years. Four years from when 2008 was released, in 2012 when we got availability groups, now we have the next major feature added to SQL Server. They put it into Extended Events. We talk about any new feature that was released after 2008, all feature traceability comes through Extended Events, and that’s why– so if we can back up just a little bit to 2008, Extended Events had rough– what was it? It was about 350 events total in Extended Events and Trace had 182-ish. 2008, we added a few more events. 2012, it bumps up to about 700. In 2014, we bumped it up to about 1100, and then 1300 and now it’s 1700 and change. So they continue to add onto that service.

    Carlos:             Right. To tangent for a second, at 1700, and before we started recording, I actually had a number that was more than that, but 1700’s still a lot of events. Is that too many events?

    Jason:              Oh heavens, no, it’s not enough.

    Carlos:             Not enough.

    Jason:              No, we’re not even close to having– so, well I have experienced (?) maybe the entire saltlick into that, for those of you that are familiar with what horses sit there and nibble on. So, yes, for some people, if we’re just trying to get used to Extended Events, it may seem like it’s a daunting number, 1700 events.

    Carlos:             You’ve got filters going on stuff now, you’re like, I– and admittedly, that first time, it’s kind of like going into the performance monitor metrics, and you see all of this stuff and you’re like, “I have no idea what this means. Do I even–”

    Jason:              Right, you go onto the performance monitor, you see 10 different metrics and they all look very similar and yeah, they’re all grouped together, and maybe 10 different monitoring– or one-way related metrics. “Oh, that all looks good, but let’s go ahead and trace them all, (?).” Well, with Extended Events, you kind of have a little bit better information about that, because they do have, inside Extended Events, the description of what the event does, and then they have each of the events also grouped together and related to each other. And as of 2012, when they added the GUI, you can kind of search through the GUI for some of those things, but in addition, on my blog, for instance, I have various different tools to help you weed out a lot of that noise, so you can focus down in on what really you want to start trying to use to focus on your issues. And let’s be real, they are issues and complications inside of SQL Server, otherwise we wouldn’t trace for them, right? Air quotes around that, because I use Extended Events for a little bit more than just tracing for problems.

    Carlos:             Sure, you want to know what’s going on, stay in the know. Okay, so from a ‘Reasons to use Extended Events’, I think we talked a little bit about the roadmap, so I guess I was referring to a roadmap you mentioned. So basically, the number of events that are available, and that through the Trace we’re no longer getting new events. You also mentioned that in 2012, now we have a GUI, so now it’s a little bit easier, that approachability is there. I think in 2012, I felt like it was still a little bit more like Performance Monitor. Yeah, you could kind of go and click, but I think it’s gotten better in the subsequent versions.

    Jason:              Well, it’s also kind of, you know, step back and think about how useful Profiler was.

    Carlos:             Oh sure.

    Jason:              You know, we look at the GUI in Profiler and unless you are using those– the various events in there on a daily basis, you can’t really tell me that you know what all of those events really are related to, or which ones group together, and there’s not very good description, there’s not very good data about those, except what people have written outside of Microsoft documentation. So, Extended Events kind of started along in that same vein. It started out rough, because they had Profiler as their baseline, so everything from that point is better. I mean, everybody that reasonably used Profiler at this point, should they have ever used it? I’d daresay no.

    Carlos:             I don’t know, I suppose once you get your templates, you get your scripts, you feel a little bit more comfortable with that, but if you aren’t getting to that point, being able to kind of figure out, “okay, hey, I need to capture some information. Let me turn this on really quickly and figure out what’s going on.” It was always kind of handy for that, assuming you could take the load, etc, etc.

    Jason:              I always thought Profiler was very good at one thing and that one thing was bringing down your server.

    Carlos:             Yes, I’ve seen a couple of servers go down. Well, and in fairness, we’ve said Server Side Trace a couple of times.

    Jason:              Yes, Server Side Trace is less prone to that problem, but–

    Carlos:             When you open up Profiler and try to run it, which is what we call the client side trace, that’s where you get into a problem.

    Jason:              Then you have–

    Carlos:             The machine will come down.

    Jason:              A couple of different ways people like to go about the Server Side Trace, I’ve seen some of these (?) Profiler, you have an issue so you have ten different people open up a Profiler session to figure out why the server is going so slow and can’t figure out why the Profiler is on and all of them running their own configuration of a Profiler session on the server. (?) experience there, I’ve seen it happen time and again, people opening up multiple Profiler sessions. Or Server Side Trace where people are writing the data to a table on the same server that’s experiencing problems. It’s got its problems, but Server Side Trace, and I’ve been on some of these metrics and there’s plenty of blogposts out there. If you want to look at a performance impact, when I do comparisons, I’ve seen profiler with up to a 50% negative impact on your server, so 50% reduction in transactions per second throughput, with a Server Side Trace at 10-15% reduction of transactions per second, and Extended Events at 1-2% reduction.

    Carlos:             Right, which is then one of the third reasons to seriously consider Extended Events, and that is the low-impact nature of it.

    Kevin:              With that said, are there Extended Events which are called an unduly heavy?

    Jason:              There is basically one Extended Event event– let’s actually state that correctly, Extended Events event that is unduly heavy, and that is being rectified with the SQL Server 2019. Not necessarily the event, but a similar event is being created without the undue heaviness, and that event is captured in the actual execution plan. The same problem existed with Profiler, they just basically took that same event and they ported that over to Extended Events. No matter what your filtering conditions are, for this type of event, or the actual execution plan event, is going to revert back to the Profiler methodology, which will impact your server. Now, that said, running that on a million transactions a minute system, I was capturing every execution plan, I only saw a 5% degradation in transactions per second.

    Carlos:             Oh, wow.

    Jason:              That’s unduly heavy, but in those kinds of cases, dare I say, you use those for very explicit purposes and for explicit time frames and most of your end users are not going to notice that and you can get away with it.

    Carlos:             Right, so to jump in kind of the weeds here for a second, in that scenario, then, you don’t maybe quite have the same benefit, you have to do a little bit of matching, potentially, but would capturing like with a query store those execution plans a way to get around some of that?

    Jason:              If you want to try to jump around capturing that, query store is not a magic bullet. You have to understand that query store is going to cause a performance impact on your server, just as well. It’s not suited for every situation. I’ve got multiple clients where they turned on Query Store, and they saw greater degradation of performance than just running Extended Events for the time period that we needed to run it for.

    Carlos:             Oh, interesting, okay.

    Jason:              Query Store, actually in two client cases, rendered the server and the applications that were hitting the server unusable for long durations of time, until we turned off Query Store. As soon we turned off Query Store, they were able to resume their high level of transaction throughput. So, it’s one of those that Query Store is a great feature, it’s one that you have to judiciously use, and you can’t just blanketly leave it on all the time.

    Carlos:             Sure, and that’s fair.

    Jason:              Nothing comes for free, everything has a cost.

    Carlos:             That’s fair. Okay, so with that, we’re on the board, we have jumped on the Extended Events bandwagon and we are ready to rock and roll. Are there any must-have sessions, ala when I’m setting up a new server, sessions that I would want to put in almost by default?

    Jason:              So, the answer to that is two words. Can you guess them? It depends.

    Carlos:             It depends.

    Jason:              And I have myself sessions that I like to put onto every server. In fact, a pro-tip that I have is you create a session, you put it on all your servers, and just leave it in a stop state. Microsoft has put multiple sessions, default sessions, out on servers with good reason. They have sessions for trapping availability groups, they have a telemetry session, they also have a session specifically if you have clustering running, it’s a hidden session that most people can’t– you won’t see it in Management Studio. Most people are unaware of it. You can open it up if you drive down into the SQL logging directories, or if you have, for instance, Smart Backup enabled to where you’re backing up to an Azure Blog Store, that event session will also automatically track your backups to Azure. It is also a hidden session. There’s the system health session, and there’s a couple of other hidden sessions that you can actually find out about on my blog, I’ve documented all of them, and so those sessions by themselves give you a nice view. System health, for instance, gives you a nice general overview, kind of like default trace did. Availability groups is a very narrow look into how well the availability group trace is performing. Your telemetry lets Microsoft, yes, and I chose those words specifically, it lets Microsoft know what features you are using or if you’re having errors related to those features, because the telemetry service actually reports home to Microsoft with aggregated information and in some environments, you actually want to work to disable that because of various compliance reasons. And then, I have a various number of, somewhere in the neighborhood of 60 or 70 sessions that I might put onto client servers and leave them in a stop state. Client calls up with an issue, I will go through and enable the specific session that I believe is related to this specific issue and run it and have them try to replicate the issue, and then report back to them what the Extended Events session has found with it. So, I mean, I have various things from (?) for STIGs or for the SQL Server feature for auto-tune or for memory pressure CHECKDB performance, exchange spills or default text object changes, hash and sleep related warnings, implicit conversion errors, I mean, just odd issues where your plans might be getting flushed, preemptive or external wait sessions. So, I have a whole bunch that I have deployed to the server, including some that you shouldn’t necessarily use Extended Events to do, like audit database accesses with Extended Events. You don’t have to use Extended Events to do that anymore, because as of SP1 SQL 2016, SQL Audit became a standard edition feature. But in 2012, 2014, 2016 without SP1, you have to have Enterprise Edition in order to use SQL Audit. And SQL Audit is actually built on Extended Events, so a lot of the same functionality is available on Extended Events, so I can use that with kind of an out-of-the-box thinking to capture the same audit information that I would need, or that I could create in SQL Audit. So, if there’s a lot of things– oh, and another one that I love to throw out there is, by default, I love to throw out database file changes, so if a file is growing or shrinking, or if a database is getting dropped or created, I have those sessions out there so I can track who did it, when they did it, what query is actually related to the file growth or the database getting dropped or created. Things like that.

    Carlos:             Oh, there we go. I feel like for a lot of folks that database file growth is kind of like the silent, I don’t want to say killer, but it’s like that, “oh hey, there was a blip, what’s wrong?”

    Jason:              Why did my database grow 200GB overnight, and now how is it magically back to 2MB on the log file? What happened there? Who did that? You know, so having that inside an Extended Events session, I have it on disc and I have it on file, and I have a PowerShell script that comes through and sweeps those files through to a central location so those people can’t necessarily change those audits, so it’s going to be secure for each client, or the DBAs and maybe the security team can review it. But, we can see, “oh, this is Charlie down in the BI group who decided to– sorry, Kevin, you know it’s always BI.

    Kevin:              That’s alright, I’m not really BI.

    Jason:              But Charlie down in the BI group, he ran this massive query with all sorts of triangular drawings that just blew out TempDB and it caused it to grow 2TB. Okay, well, let’s take a look at that query and let’s try and train him up.

    Carlos:             So I’m curious, Kevin, so I guess getting into that, some of the examples that Jason gave are very DBA-centric. I know we’ve talked a little bit about PolyStore. The big data clusters episode actually will air next week.

    Kevin:              You mean PolyBase?

    Carlos:             Oh my gosh.

    Kevin:              Denture Grip? One of these episodes.

    Carlos:             One of these episodes.

    Jason:              So it’s QueryBase and PolyStore.

    Kevin:              QBase and DBase2.

    Carlos:             Oh gosh, compañeros, I guess I’m having one of those days. So I guess my question is ultimately, are you using Extended Events for anything outside of the normal DBA stuff, and is that something that you’ve, in your research, for again, some of these other technologies that you’re paying attention to at all?

    Kevin:              Please Jason, go ahead and answer, because my answer is like, “no, not really, because I pretend to be a developer.”

    Carlos:             Gotcha. No, that’s fine, that’s kind of what I expected, I just thought I’d give you the option.

    Jason:              I would say Extended Events are highly useful for a developer and DBA. Close side of that, for something such as PolyStore. It’s going to be a long running one. At least through the end of this episode. No, for PolyBase, we don’t have Extended Events for PolyBase yet. But if we look at Analysis Services or Reporting Services or AzureDB, again, we’re looking for the traditional Microsoft products, Extended Events is out there. For big data type of stuff or JSON stores or data lakes, we don’t have it yet, but there are trace utilities that can be used for that at the moment.

    Carlos:             So I am curious, Azure SQL Managed Instance, have you used that at all? Extended Events there, is that–?

    Jason:              I haven’t had anybody actually need to use Azure Managed Instance yet, at support. So, this is where things get a little bit iffy, because working back with Microsoft, all of the Extended Events events are not available in Azure SQL DB. You only have about 300 of those events total available for Azure SQL DB. So if you’re looking at it at an on-premises implementation, you have 1700 in SQL Server 2017, you’re about 1900/2000 range for SQL Server 2019, which is still, you know, someday (?), and Azure SQL DB, as we well know, is somewhere in the realm of using the code bases closer to the next release of SQL Server. When we’re saying that we should have somewhere between 1700 and 1900 in on-premises implementation and we look at Azure SQL DB and see that it only has 300 events, there’s obviously a disparity. Now, let’s take that to Managed Instance, it’s somewhere in between, still, that 300 and 1700.

    Carlos:             They’re limiting your ability to get to the operating system and the instance-level pieces, because your world is much more narrow.

    Jason:              Not everything is going to apply. You don’t have various different events there, like the log in events, (?) use that. There are various different features that are just not enabled or applicable to an Azure SQL DB, where you may want to say, “okay, I really, truly need to go with an on-premises solution, because I need feature X, Y or Z, and in the current state, it just doesn’t work the same in Azure SQL DB or Managed Instance. Managed Instance does give you a better feel of an on-premises situation, just not all the way there.

    Carlos:             Sure, okay.

    Kevin:              So, we’ve talked a lot about “hey, here are the sessions, perhaps you want to script out. Here are some things that you want to see, some of the interesting events.” Once you have those sessions created, how do you actually use the data? What am I using for this?

    Jason:              That’s a good question. There are a couple of different ways of doing it. As we’ve mentioned, there is a GUI inside of Extended Events, and that GUI is applicable both in creation of the event, or you have that fabulous grid view layout that we had with Profiler. They basically gave us that grid view that is available in Extended Events for viewing the data. So we can either right-click the event session and watch it livestream the data, or we can go down into the event target, which would allow us, if we right-click the event target, we can go ahead and look at the event data that has been captured to date. So live will give us whatever events have occurred since the opening of the viewing panel, or view target data on the target file or memory target, whatever the target may be, will give us all of the historical stuff up to the point of opening up that view, so it’s available there. That’s one way of doing it, and that gives you some power to be able to group and aggregate and filter the events right from the GUI if you are weak in T-SQL skills, or just looking for a quick solution. Of course, I also have scripts out that will help you parse any event session you have on the server and quickly dump that into, say, a temporary table, and then you can parse the data out of there and group and aggregate any which way you want via T-SQL. So you can do it either way, T-SQL, it’s easy enough to do in T-SQL and it’s easy enough to do from the GUI.

    Kevin:              Excellent.

    Carlos:             Yeah, I have to say, it’s easy enough to do in T-SQL once you feel a little bit more comfortable with parsing through some of that and knowing the events that you’d want to get out.

    Jason:              Creating a session, you know, that’s why I’ve gone out and created all of these tools. I’ve got tools to help you find out what events are going to be applicable for your situation, and I’ve got scripts that I’ve created that will go out and script out the query that you need to run in order to parse your event session data. So, you don’t need to know any of that, you just need to know how to download and click Run.

    Carlos:             Well, yeah, but from a getting up to speed perspective, you have to be able to– so downloading code from the internet, “trust me, kids, I’ve got candy in the van.”

    Jason:              Yes, it’s okay. I don’t carry candy in the van. I have much more attractive things in the van.

    Carlos:             There is some learning curve of being able to take a peek at that and kind of figure out what’s going on. Take your lumps, and then–

    Jason:              That’s an important thing you just said, “take your lumps.” Take the time and learn it. You’ve got to spend some time to learn it and, in my opinion, you have to learn it from the T-SQL way, because once you learn it from the T-SQL way, the GUI way is going to be so much easier, and you’ll be able to appreciate both methods much better.

    Carlos:             Sure, that’s fair. Last thoughts on Extended Events, then, Jason?

    Jason:              I’m going to put a plug in for a couple of things. If people go to xevents.jasonbrimhall.info, it’ll actually go up to my massive library of Extended Events articles.

    Carlos:             And we’ll have that on the show notes today. The show notes will link to your blog, as well.

    Jason:              And then one more thing is I’m actually still working on finalizing the graphical display of a new website. It will be called sqlxevents.com. So, all of the Extended Events articles and everything will just be in one place, so it’ll be easier for people just to look there. Just go to sqlxevents.com, whatever you need, you’ll be able to find there, and at some point there will be guest columnists besides myself. All things XE.

    Carlos:             There you go. Very good. Okay, so shall we do SQL Family?

    Jason:              Let’s jump to it.

    Carlos:             Your all-time favorite movie?

    Jason:              This is probably the hardest question of the whole bunch.

    Carlos:             We do specify, this could be today. It can change tomorrow. But as of this recording right now?

    Jason:              Well, it might actually change from minute to minute, depending on what I’m watching. But, you know, I still like Star Wars Episode 4. That’s probably one of my favorites. I could watch that one or Monty Python’s Search for the Holy Grail.

    Carlos:             There you go.

    Kevin:              There’s surprisingly little controversy in either of those. I’m disappointed.

    Jason:              I just know a lot of Trekkies and they’re against Star Wars and a lot of people in Star Wars against Star Trek. Personally, I dno’t like any of the old Trek movies. Start with some of the newer Trek movies and the newer three, they’re–

    Carlos:             I was going to say, man, you were doing good, there, and then you had to go–

    Kevin:              There we go, there’s the controversy this podcast needs.

    Carlos:             That’s right.

    Jason:              I told you it’s Trekkies versus Star Wars.

    Kevin:              For Episode 183, in the Trekkie versus Star War debate–

    Carlos:             So, the city or place you most want to visit?

    Jason:              Now, that’s the hardest question.

    Carlos:             Man, we just go from hard to hard.

    Jason:              Yes, we just keep getting harder. So, city or place you most want to visit? I’m going to go ahead and kind of envelope this into maybe it’s not just a singular place, but there’s a good reason for it. And I’ve been there many times already, but I’d like to return to France and take a two month trip with the family so my kids can be enveloped in the society and also go and visit some of the other European places and, you know, just lots of history there. Exactly, yes, it’s close enough to Germany, Holland, Spain, Italy and England, as well as all of the other European nations. Those are all places that I’ve visited. I’d like to revisit them and take my kids there to visit.

    Carlos:             Yeah, and I feel like you need a good two months to be able to appreciate all of the pastries.

    Jason:              I’m not sure I can get through all the pastries in two months. The pastries, the food, is just so good.

    Carlos:             Which then brings us to our next question. A food that reminds you of your childhood?

    Jason:              So, just in childhood, I would have to go with lasagne. There’s so many different flavors of lasagne, but that’s probably one of my favorites I started with. But then in my late teens, I really grew to appreciate a dish from the Mascarene Islands, La Reunion Island specifically, called Rougaille Saucisse, and basically, it’s like a sausage tomato dish. It can be very spicy, served with rice, it’s just so good. And no matter where you make it elsewhere in the world, kind of like French pastries, you can make them anywhere in the US or anywhere else in the world, it does not taste the same.

    Carlos:             Yeah, it’s funny how food can do that sometimes. That is interesting, I don’t know that I’ve heard of many good sausages from an island before, normally that’s– That’s cool. Okay, so how did you first get started with SQL Server?

    Jason:              So, I saw this thing back in ’95 showing that the certification exams for SQL Server 6.5 were the most difficult to pass, and I said, “sounds like something challenging, so I want to do it.”

    Carlos:             There you go, interesting. That’s probably the most unique answer I’ve heard to that.

    Jason:              Give me something hard.

    Carlos:             Give me something hard.

    Jason:              Let’s take it on.

    Carlos:             Here we go. Now, in that time, so since that certification exam to now, we’ve talked a little bit about Extended Events. This question does not have to necessarily include Extended Events, but if there’s one thing about SQL Server that you could change, what would it be?

    Jason:              That’s an easy one.

    Carlos:             Here we go.

    Jason:              That’s the easiest question of the day. As of Management Studio 17.3, they introduced this new feature that I absolutely deplore. It’s XEvent Profiler. Get rid of that piece of crap.

    Carlos:             Interesting, okay.

    Jason:              I know a lot of people love it and they wanted it, but just get rid of it. There’s no purpose.

    Carlos:             Because you’re a T-SQL guy?

    Jason:              I’m not so much a T-SQL guy, but this is breeding confusion for a lot of people saying, “well, Profiler is deprecated.” And it was actually around the time that this was released that Microsoft redacted some of their documents to say that they changed the deprecation announcement. At least the deprecation announcement in the documentation, but if you go back to like a 2008, 2008 R2 instance, you can find that some of those things show up as deprecated in the engine. But you see Profiler and you’re sitting there wondering, “well, I thought Profiler was deprecated, we should stop using that.”

    Kevin:              Yeah, I hate the term.

    Jason:              Don’t like the term and I don’t like the product that it provides. And then on top of that, if you open it up in not just Management Studio, but in Azure Data Studio, it is called just Profiler. So you’re confusing people. I mean, sure, it runs on the XEvent engine, but all it does is– it’s not fully-baked, and we are now at 18.2 and it’s still not fully baked.

    Carlos:             Still not fully, yeah.

    Jason:              There’s a lot of various weird functionality that just doesn’t work, still, and it was basically get something up quick, get something out to quiet some of the–

    Carlos:             The noise.

    Jason:              The noise that was coming because people wanted a way to create an XEvent session easily and I’m sitting there going, “no, that was already there.” Because if you open up the GUI to create a new session inside of Extended Events, it takes you roughly three more clicks and it does open up an Extended Events Profiler session and start it, but I can create a better session using the same template that XEvent Profiler uses, I can create a better session to be more direct to capturing precisely what I need.

    Carlos:             Gotcha, okay. Now, what’s the best piece of career advice you have received?

    Jason:              It’s actually a hard one.

    Carlos:             We’re back to the hard ones. You got your one easy one.

    Jason:              Because I don’t know that it was necessarily career advice, but I can tell you a good piece of advice, and it was more of a technique, and it wasn’t necessarily, like I was saying ‘career’, but it was back in, I want to say my sophomore year in high school with my calculus teacher. His methodology of teaching, “tackle the hardest thing first, because then everything else becomes easier.”

    Carlos:             Interesting, there we go. I like that. Jason, our last question for you today, if you could have one superhero power what would it be and why do you want it?

    Jason:              I have no clue. Maybe if there was a superhero power– I guess somebody does have it. Wolverine has it, and that’s the quick regenerative powers, but not just regenerative in your physical nature, but in your mental nature. Because, I tell you, running around working and then working with your kids and trying to raise a family and then doing SQL Server Events. I’m not complaining, don’t get me wrong.

    Carlos:             Getting old is hard, huh?

    Jason:              I’m just– hey, you get tired, you know? Right now, I’m coaching baseball. I mean, it’s a free freshman team, mostly it’s like 14-year-olds playing against travel teams and you go through that practice, you know, it’s not just a mental exertion, but it’s also a physical exertion by the end of practice, and then I’ve had all of the mental exertion throughout the day, and you’re just exhausted at the end of the day. So if you could have something that would regenerate your energy just like that, like a super power, that’d be cool.

    Carlos:             Very good, awesome. Well, Jason, thanks so much for being on the program, today.

    Jason:              Appreciate you having me.

    Carlos:             Okay, compañeros, that’s going to do it for today’s episode. Thanks so much for tuning in, wherever you might be, whether you’re coaching baseball or walking the dog, whatever it might be, thanks for joining in. You can reach out to us on social media. Jason, how can people connect with you on social media?

    Jason:              Twitter @sqlrnnr. If you want to find me on LinkedIn, you just look up my name, Jason Brimhall. Not a whole lot of me out there, and I’m the only one that pretty much does databases.

    Carlos:             There you go. And Kevin?

    Kevin:              You can find me somewhere in your ring buffer.

    Carlos:             And compañeros, you can reach out to me on LinkedIn. I am at Carlos L Chacon, thanks for tuning in again to today’s episode, and I hope to see you on the SQL Trail.

Listen to Learn

00:40 Intro to the guest and topic
01:32 Compañero Shout-Outs
02:21 The genesis of the trace and how Extended Events came into play
06:52 Trace events has not been added onto, but Extended Events continues to grow
09:11 Does Extended Events have too many events?
11:01 Reasons to use Extended Events
14:15 There is one Extended Events event that is unduly heavy
15:28 Query Store is not always a good solution
16:44 Some must-have sessions and other session advice
22:00 Extended Events’ limitations with services outside SQL Server
25:30 Ways to view your Extended Events data
27:18 Take your lumps and get comfortable with T-SQL and parsing
28:37 Last thoughts on Extended Events
29:28 SQL Family Questions
37:02 Closing Thoughts

Imagine what’s possible with a dedicated SQL specialist on your team.

Leave a Reply

Back to top