sp_HumanEvents: View Creation and Cleanup Examples

Taking A Nice Warm Bath

Video Summary

In this video, I delve into the process of logging detailed information about SQL Server operations using extended events. Specifically, I walk through setting up sessions that log data to tables and explain how to run a “logging loop” for a few seconds to ensure everything is set up correctly. After setting up the necessary infrastructure, I demonstrate how to create views from the logged data, providing insights into various aspects of SQL Server execution such as recompiles, durations, and parameterization. These views offer valuable information that can help in performance tuning and troubleshooting, though some views might not contain much data due to the short duration of logging. Finally, I cover the cleanup process, detailing how to remove all traces of the extended events setup with a single command.

Full Transcript

Erik Darling back with MORE SP underscore human events goodness. Now in this video I would like to go over a little bit about how or what happens rather when you log stuff to tables now because some views get created as you can maybe see by this line of stuff and we have a new one that’s going to be new mode to clean up things which will hopefully not throw any errors now that I am live and looking at this. It worked in dev which is on my machine so theoretically it should work here too, but who knows. So in order to get the views to set up we do need to set up sessions that will log data to tables. So we do that again. I cover this in another video but to do that we just say which session we want to set up. want and we use this keep alive flag here and that will create all of the extended events necessary to log all that stuff off to tables and then we need to set up the or we need to run the logging loop now again in real life you would probably want to log this from a SQL Server agent job or something but it doesn’t really take much more than this command to to get things off and running so run that and now I am NOT a terribly religious man but I feel I don’t know I guess maybe superstitious and that I always let this run for about 20 seconds because that gives me a few iterations of the of the loop to make sure that I don’t hit any errors like on the second one or third one I got I don’t hit me like n plus one errors so I usually let this run for like 20 30 seconds to make sure that I get a good representation of the loops in there so we hit 30 seconds so I’ll go in and we’ll look at the output now so stuff you’ve already seen is where the tables get created that’s that’s fine and but now the magic that happens after we create tables is we will use SP execute SQL local to the database and we will create these views that look a whole heck of a lot like the code that you will that you would see up in the regular the regular store procedure execution where we would just present this data off to you and there are about 13 of these so I’m not going to spend a whole lot of time sitting there and staring at them explaining to you what each query does you can see pretty you can I try to give the each view a fairly descriptive name so whenever you see one of these things you or whenever you see the view names you should theoretically as long as all goes well as long as all is right in the universe I should see a fairly descriptive you name recompiles by database and object is this one and we are grouping by database and object here so that’s that’s a good start recompiles by durations when we order by the average duration so there’s all sorts of good stuff that you get in these views and I’ll show you these when you get to them and so all this stuff happens but all the good news is that oh so this is these are the inserts we already talked about the inserts so that’s all good there and then so that’s fun and then we get these 13 or so views and the first one will show us any queries so there’s not a whole lot in here right now which is fine you know it doesn’t really need to be I’m just you know showing you that they exist so we get all the queries that have run we get weights by query and database now the one thing I need to mention is that this is a best effort view there’s I could not find a way to get the extended event for wait stats to grab it’s either a SQL text reliably or the any identifiers for executing queries that ran so I can get like query hash or query plan hash or plan handle for anything so this is the best effort we will reach out to the dynamic management views on the server and try to get that information there if we can’t we can’t there’s just nothing nothing more nothing really else I can do about that but we also check out rights by database wait or other weights by database total weights across the whole server there’s not a whole lot of great stuff in here because it didn’t I didn’t run things for very long there shouldn’t be any blocking on the server X I didn’t set any of that up any compilations will be in there and for some of these I decided to give you a few different looks at things I’ll I may expand on these in the future it’s already like a lot a lot of good stuff in here but you know I just further compiles and the recompiles and weights there’s a few different looks there’s a few different views of the data so that’s kind of fun and then you know parameterization there’s some well there’s nothing in there look at but whatever you get the point there are 13 views that give you 13 they give you certain certain certain different views of the data some of them only have one some of them have multiple kind of depending on if I thought it would be interesting to slice and dice the data up you are free to query the views or the base tables however you wish right now they don’t relate very well or at all really aside from event time so I don’t know maybe a long term I’ll try to find out what I’m going to find out.

I’m going to try to work on some way to relate things together but for now I don’t have a great way to do that just because there’s there’s such a lack of uniformity and what the events can actually pick up and do like at first I was really excited to give you compiles by like like query hash and query plan hash but when I went to pick that stuff up it just turned out to all be zeros so and the same thing with recompile so it’s like you know this is not a lot of know what I can do sort of at the mercy of the extended events here which I’m sure all of you can relate to and that’s why you’re here so you know if things improve or get better I’ll certainly try to you know add those improvements in here but again I can’t make that promise anyway now we have a bunch of stuff running on the server and say we don’t want it running anymore let’s say we don’t we want to get rid of everything I did not separate the cleanup mode into different ones like cleanup views cleanup tables cleanup events because it’s I made it so easy to reset things back up if you need to like backup tables first then you know feel free to do that but you know most people if they want to get rid of stuff they want to get rid of everything so now we have this cleanup flag and with clean with the cleanup flag you do need to use the output database name one slight change that I’d made in this iteration of the stored procedure was to give output schema name a default value of DBO you are welcome to change that but for now I wanted to make it less complicated to get data moving so now if you choose an output database will default to DBO if you want a different schema go for it it’s between you and your DBA I guess but anyway we have this cleanup event now so at minimum we need cleanup equals one and output didn’t the output database name and I’m gonna use debug here just to show you what gets output and yes no errors and so what happens is first we will drop all the sessions next we will drop all the tables involved now I again I went pretty low-fi on this and we just use SP execute SQL local to the database to drop tables all in a row I suppose I could add like and then VAR car tenner or NCHAR tenner 13 in there to give us a little bit of separation they all maybe I’ll go do that I’m done recording here a little change the face of T SQL but again locally we’ll also do that for all the views and then we will have absolutely nothing left there will be absolutely no trace that we were ever there so isn’t that fun it’s not so much fun anyway I guess that’s about it I don’t know I don’t really know what there is left to say about it maybe I’ll maybe I’ll go do something constructive now who knows all right thank you for watching and again if you uh if you have any questions comments concerns if you have if you hit any bugs if you hit any if you hit any or if you hit any bugs if you have any requests for code in here please let me know over on github or somewhere I don’t know cry to me on twitter or something I don’t know uh anyway thank you for watching uh and happy event extending or profile or plussing or whatever you want to call it you

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.