MERRY CHRISTMAS
Video Summary
In this video, I delve into the process of getting raw data into tables for logging purposes within my software’s event sessions. I walk through setting up these sessions and explain why it’s crucial to be very selective when configuring filters to avoid unnecessary overhead on your server. The transcript highlights the importance of creating different filters carefully, as well as the potential pitfalls of overly aggressive filtering that could lead to excessive data accumulation in logging tables. I also share some practical insights into managing this data efficiently, setting up an upsert scenario for future updates, and preparing to create views that will make the logged data more accessible and user-friendly for end-users.
Full Transcript
Yeah, I suppose this thing is on. Hm. Hm. Hm. So, uh, in the, in the, the last video, I showed you how the code that logs data to tables works in, uh, sp underscore human events. So now, I want to show you how to get your data into those tables. Now, again, I’m going to say it again, because there are things like this that you can’t say enough. Right now, this is only moving raw data in. An upcoming feature release of my software will, uh, include creating views to make better sense of this data. Now, we already have, uh, uh, the mechanism to create the, uh, the mechanism to create the event sessions. We have the mechanism to log the data to tables, and we have a mechanism to, uh, delete data from those tables after a certain period of time. That’s all automated in the, uh, command that starts logging stuff. Well, the, the, the logging and the, the, the deletion is in there. To set sessions up to get logged to tables, we need these commands. Now, uh, I have the, uh, debug options set on for these, uh, so that I can show you the output. Now, if we run these, rather than giving us any additional information, what we do is we, uh, set up the event session, uh, and then we bail out. And then we’ll do that once for each one of these event sessions. And this is just to allow you to, uh, create different filters. And I would urge you, very, very much urge you, when you’re creating these event sessions to be more aggressive than normal when setting up filters, uh, for, you know, durations of things, for which databases you want things from, uh, sampling session IDs. This is all stuff that I walk through in, in other videos in the series to show you how to do that. But, um, yeah, I would, I would urge you to not set up, uh, sessions that will cause significant observer overhead. And it might, um, contribute to the logging tables filling up a lot faster. And I’ll show you why. I’m going to give you a cautionary tale of why when I show you the data that I’ve already, I’ve already logged in there. So these are the commands you’d want to run to, uh, set the sessions up, right? With, of course, with whatever filtering you want. Then the command that you want to run to, um, you know, you don’t, you don’t need debug in here, of course. Having debug in here would be silly for you. I have it in here so that I can show you what gets printed out for some things. And so I can show you, uh, the results of some temp tables. So to get things off and running, logging to a table, we need this command.
So this will output to a database that I have called crap into the DBO schema. And if I run this, this will get kicked off running a loop. And this is the result of the temp table that, um, that we have to log when things are last checked, when things are last updated, yada, yada, yada. Uh, you can kind of see the last checked ones getting higher. So the last updated ones will only fire if we actually log data to a table. So these might change over time. But anyway, you can see that there. Um, there’s not a whole lot of, there’s not, not, there’s not anything going on in here right now. I already ran a workload that would generate some, uh, some, some data movement into those tables. But you can see this is where the dynamic SQL prints out.
And you can see, this is why it looked crappy in the proc so that it looks pretty when I print it here, or at least sort of pretty most of the time. There’s some stuff I got to go back and fix, I guess this one here logging to, uh, what is this? Weights and weights, you need some help. Yeah, weights, weights could use some fixing, but everything else looks pretty good. At least, at least, at least as far as I, at least as far as I’m concerned, I may not have the highest coding standards in the world, but, uh, there’s nothing I can do about that. That’s a printing artifact. That is not, uh, how the code actually looks.
So they, that’s what that looks like there. But then let’s go look at the, the results in the table. So, uh, over in my, my crap database, I have these, uh, let’s see, one, two, three, four, five, six tables, because I created, uh, um, I created the five sessions here and along with compiles, uh, there comes a new set of, uh, there becomes a second extended event that looks at, uh, query parameterization stuff. So let’s go, uh, just get a quick look at this. If we select the top 1,000 rows out of here, uh, we will find all of the blocking information that we have from a blocking scenario that, uh, was going on, on my server, my, my laptop server.
Now, uh, I gotta fiddle with this a little bit. I’m not exactly thrilled with, um, just inserting the same thing over again. I will probably use some sort of upsert scenario in here in the future. So rather than, um, you know, just blindly insert the same blocking scenario over and over again, uh, I’ll do something to, um, um, to make sure to either insert a new blocking scenario or only, or update, uh, an existing blocking scenario. So, um, you know, or update like the, the, um, I guess the wait time.
Um, if you look over here, we can see that this sort of goes up gradually, uh, across sessions. So what I’ll do is try to figure out a way to, um, uh, upsert rather than just blindly insert data over and over again, because that could get rather cumbersome when we start looking through stuff. Uh, the reason that there’s nulls in some of these is because the blocking, uh, session does not have all of the same data in it that the blockade session does. So, um, there’s something we have to deal with there, something we just have to live with.
Now, uh, moving on, uh, let’s look at the compiles table. So this will show us all of the queries that, uh, had a compilation event. And this is a pretty good, um, example of why, why you’d want to be aggressive with your filtering, because you can end up with a lot of sort of nonsense in here, right? So, you know, all this stuff that, you know, maybe we don’t need coming in here. Um, let’s see, let’s see, let’s cash ran, did it? I don’t know.
It’s surprising to me. I didn’t know SP blitz cash ran on this thing. News to me. Anyway, I didn’t, I don’t run it that I know of. Maybe I did. I don’t know. I forget, I forget exactly what, oh, you know what I did was part of my demo workload. That’s right. So, uh, yeah. So this is all the stuff that compiled. And now the stuff thing that I think is pretty cool is, uh, the parameterization table.
Uh, and what this will tell us is more information about, uh, if the query could have been parameterized or not. So if we look over here, we’ll get some information. Uh, and this is parameterizable column will tell us if the query, if, if, if the optimizer thinks that we could have done something to parameterize this query. There’s a lot of neat information coming from this extended event. Um, um, I don’t know. I would, I would urge you to get on a new enough version of SQL Server to, uh, get data from there.
And then, uh, going on down, we have stuff that came out of the query session. And this is just everything that ran and, I don’t know, met our, met our needs and all that fun stuff. Now this is, again, this is the raw data. And in the actual store procedure, I go through quite a bit of code to present you with a, um, an accurate set of information per query. But you can kind of see, like, how, like, you kind of see why this thing gets crazy, uh, over time. So again, um, be very judicious with the way you set up filters on this.
Do not just try to catch everything because you will catch hell on your server. Uh, the recompiles table will give us, uh, information about, uh, what recompiled and why. So if, uh, we requested a recompile or if we had deferred compilation or if stats change or whatever other schema changes or whatever else, uh, seems to contribute to that happening. And then of course the weights view, finally, we will have, uh, all of the fun weights that we experienced on this server while we were collecting data.
So, uh, step, stage one is, I think, complete on this. Aside from, you know, going back and looking at the upsert thing for, um, for the blocking sessions, that’s, it’s kind of, kind of a hair across my butt. I’ll work about, I’ll work on that sometime, uh, probably in the next week or so. And then after that, we’ll be creating views to, uh, make sense of the data logged in those tables.
So what I’ll, so what I’m going to do is, uh, since I have data in there right now, I’m going to start working on applying views to that data so that you, as the wonderful, fabulous end user that, um, you are, can go ahead and select star from those views and just get back the pertinent, well-formatted information that, uh, you are used to from expert T-SQL coders like me.
I’m kidding. I’m kidding. It’s a joke. It’s clearly a joke. No one would ever call me an expert T-SQL coder. I mean, you’ve, you’ve seen my code. It’s expert. Yeah. Lucky, lucky it works. I think, I feel like I’m taking advantage of SQL Server. I’m going to write code sometimes, like.
I’m just, like, pulling the wool over its eyes. Like, no, this is totally fine. It’ll work. And it’s like, okay, we’ll go with that. Oh, poor software. Anyway, uh, I hope you enjoyed this. I hope that you are having fun using SP underscore human events, and I will see you in another video. Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.