sp_HumanEvents: Getting Help

Posted on by Erik Darling

All I Wanted Was A Pepsi

sp_HumanEvents

Video Summary

In this video, I introduce SP_human_events, a stored procedure designed to make extended events more accessible for SQL Server users. I delve into the help parameter and how it can be used to get additional support or clarification on specific issues. The video covers various parameters and their default values, limitations such as memory constraints and compatibility requirements, and provides example calls to demonstrate its usage in different scenarios. While this procedure aims to simplify extended events for you, I emphasize that proper use is crucial to avoid potential problems with your SQL Server. If you need more detailed assistance or have specific questions, the video encourages viewers to visit my GitHub repo where they can ask questions, submit issues, and even contribute code fixes if needed.

Full Transcript

Et voila! Here we are with the second video to introduce SP underscore human events, my stored procedure to help make extended events a little bit more accessible to the fine people of Planet SQL Server. And this video is just going to be a short one about the help parameter and how you can use it to get more help. Now, if you are watching this video, pay careful attention to the upload information on the YouTube. If you are watching this video far into the future, there’s a pretty good chance this stuff has changed. Actually, there’s a pretty good chance this stuff will change like tomorrow. Because I’m, I’m, I am always working on this thing to make it better for you fine people. And, um, yeah, so there’s that. Uh, anyway, it, it, this is just about getting help. And if you’re here, I assume you need help or want help or I don’t know, there’s something wrong in your life.

Maybe this stored procedure will fix it. Maybe it won’t. I don’t know. But anyway, uh, the first thing that I have to start off with here is a warning. If you misuse this thing, you can cause problems. If you do something terrible with this stored procedure, you can harm your precious SQL Server. So don’t misuse it. If I give you a car and a bottle of whiskey, it is up to you as an adult, probably human being to make a sane and rational choice with what to do with that combination of items. Just like it is up to you as a sane and rational human being to, um, make a sane and rational choice about what to do with the combination of parameters that you feed in to SP human events.

So, that is the warning. If you need additional support, there is a very helpful, I promise not spam bit.ly link. Uh, there, where that’ll, that’ll bring you right to my GitHub repo where you can ask questions, um, create issues, submit code fixes. Just, just please open, open an issue first so we can at least talk about whether this is a good idea for you or not, or a good idea for the procedure or not. Um, and then the second section down is just a short sort of introduction. Uh, tells you what sort of issues we can, uh, track down using SP human events.

And then if we go a little bit further, we, I discuss some of the limitations. Uh, first is that you need to be on at least SQL Server 2012 or higher. That does not set a very high bar. Um, but if you’re on SQL Server 2008, you are out of luck. You better call Saul. Um, the second is that, uh, this is designed to make things easier. And, uh, one way to make things hard is to present someone with many, many options.

And I do not want to make extended events harder for you. I want to make them easier for you. So, some customizations are limited. Um, you know, also the more customizations I offer, the more complicated dynamic SQL gets, and the more time I spend drinking and the more chances there are of there being bugs. And I just don’t want that for you.

Um, a couple of things down here are, I don’t want to create files. Uh, one of the chief things that extended events do or can do is create an event file to track, uh, information into stored data in. And I just don’t want to do that. That introduces, um, the potential to, uh, you know, write a, you know, big, big files out to your SQL Server somewhere. And there’s not an, there’s not a good, easy way to clean them up unless I use XP command shell.

And I just don’t like it. Uh, I don’t want to check file path validity. I don’t want to have to deal with Azure file paths that start with HTTP. It’s not, it’s not, it’s not, not fun. I did that with SP blitz lock, not doing that here. Um, so with the use of the ring buffer, which I am using, which is just an in memory, um, chunk of information, there are some limitations.

You may not see full event information and, uh, we may wrap around, uh, during the time that we choose to collect information because I am choosing intentionally not to use more than one gig of memory in order to store this data in. Um, I will perhaps make that configurable someday, but today it is not.

Uh, the other thing is that if you want to use the blocking event, you will need to turn on the blocked process report. If you try blocking, if you try to view blocking and you don’t have that turned on, you will get, um, uh, an error and you will get the code needed to turn on the blocked process report.

You can also get it from this URL or URI depending on, uh, how much of a geek you are, whatever you want to call it. The next section down describes some of the parameters in this fine store procedure. Uh, we have event type, which you use to choose which session you want to spin up.

You can use blocking, query, weights, recompile, compiles, and cert, excuse me, certain variations on those words. So you can just put in block, query, queries, wait, weights, recompiles, recompilations, uh, compiles, compilations. There’s a, we, we account for a lot of different things in there.

I, I don’t expect you to be exactly on top of the wording here. Um, and then a little bit over, we see the default values for things. So then we have some other filters, uh, query duration.

We have a query sort order for which way you want to order data. If you do choose the query event, um, how long things would have to block for in order to qualify, um, weight type. So this can either be a single weight that you want to monitor, or it can be a CSV list of weight types.

Uh, the minimum duration for a weight, uh, if you want to capture execution plans. And then some other filters for, um, if you want to filter to a specific client or host or database or session ID. Or if you want to sample a workload.

So you’re probably wondering why session ID isn’t in VARCAR. And I bet you’re sitting there gritting your teeth and saying, Eric, you’re not following best practices.

You’re storing a number as a string. You’re a bad person. Well, that’s because you have choices. You can either put in a number, which I will dutifully check using, not using is numeric to make sure that you have entered a valid number. Or you can put in the word sample or sampled.

And you can put in a divisor. And what this will do is allow you to sample an entire workload by, uh, figuring out which session IDs, uh, evenly divide by the number you choose as a divisor and only getting information for those. So if you have thousands of sessions and you say, I want only want sessions that divide evenly by a hundred, we can narrow down the amount of data we’re collecting for.

We can also filter to a username, an object name. And if you’re using the block, so this is where things get a little tricky. If you’re using the blocking session, uh, or the event session here, um, that it can, for some reason it doesn’t use, it can’t filter to an object name.

It can only filter to an object ID. So if you’re using blocking, or if you’re using blocking, you want to filter to a specific table, we also need the schema of that table. By, by default that goes to DBO.

But if you need a different schema, you can put that in there. If you’re just searching for a store procedure name, you don’t need to put that in there. Uh, then we also have some filters for, um, excuse me, my mouse wheel is all amok, uh, for how long you want to run the session for. And, like a lot of things, I don’t want to put in, I don’t want to allow you to put in what I think are bad idea, parameters, without some warning.

So, uh, there are some values for some of those filters, especially like very low millisecond weights on things, where, um, if, if you, if you put in values that I deem, whack-a-do, then you will have to use the gimmeDanger parameter set to one in order to use those. Otherwise, I will set them back to what I consider to be same values or throw an error, depending on what I care about most.

Uh, then we have this keep alive one. This isn’t functional yet. Um, I’m working on the ability to, uh, put data into the ring buffer, move it out and into real tables. And, uh, those were, that would, that would allow sessions to stay alive longer.

Um, but that is not functional yet. That will be something that happens in a future release. I wanted to get, uh, an MVP out there for people to start kicking around before I started adding more, uh, more involved features. Uh, the next section down is a list of, uh, example calls to the stored procedure.

So some different, so different ways to look at things in different scenarios. Uh, that’s all very useful stuff. I’m not going to read it all out loud for you because it’s all fairly self-explanatory once you get to reading it. And of course, like every, oh, well, I think like most of the code that I write, uh, this is MIT license.

It is completely open source. Uh, I don’t want to print the entire MIT license out here. I tried a few different variations on that and none of them were good. But if, uh, if you use help and you go over to the massages tab, uh, you will see the full MIT license printout in all its open source glory, making Richard Stallman a very happy person. I think maybe, I don’t know.

I am doing this on, on windows or SQL Server, which is decidedly not free software. So maybe, maybe not like happy, happy, maybe just like, I don’t know, a little bit less miserable. I don’t know. I don’t, I don’t know how to predict that.

Anyway, um, I think that’s it for the help section. Uh, yeah, that, that, that about wraps it up. That’s, that’s all I have in here for now, but I hope that, uh, you found this useful and informative and that, um, me, me rambling on and on in this short video. Uh, helps you learn a few things about how the proper use and functioning of, um, sp underscore human events.

Uh, I’m going to start recording some other videos about actual use cases. So I will see you in those. Au revoir.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Related Posts