Recent Improvements to sp_HealthParser
Video Summary
In this video, I delve into some recent enhancements to my SQL Server performance analysis scripts, specifically focusing on the `SP Health Parser` stored procedure. This tool has been a valuable resource for parsing the System Health Extended Event in SQL Server since 2008, converting its XML output into more digestible tables. The latest updates include new events and the ability to log these events directly to tables, providing a more comprehensive view of your server’s health over time. I walk through how these changes work, demonstrating their functionality with examples and explaining the data retention policies that ensure you only capture relevant information. Whether you’re looking to analyze trends or simply gain deeper insights into your SQL Server performance, this update could be just what you need.
Full Transcript
Erik Darling here with Darling Data. Pretend that didn’t happen. Drives everyone crazy. And in today’s video, I have been very, very hard at work on my set of SQL Server performance analysis scripts, stored procedures rather, to help you help yourselves a little bit and also to help me because as as a consultant with very reasonable rates, these are all things that I thought I would find useful in the work I do. So hopefully you will find them useful in the work you do as well. So today’s video, we’re going to talk about improvements to SP Health Parser.
For those of you who are unfamiliar with it, I dropped this one sort of at the end of, towards the middle end of last year. And what this store procedure does is, you know, every SQL Server since about SQL Server 2008 or so has an extended event running on it called the System Health Extended Event. That thing is full of awful XML hell. And it’s a bad time. So I wrote a store procedure to parse it all out, put it all into helpful tables for you.
And the recent additions to it are some events that I was not collecting before and the ability to log those events to tables. So we’re going to talk through that stuff in this video. But before we do, we have some important stuff to talk about. If you like this channel content and you would like to sign up as a, as a say thank you with money member, link down in the video description to do that.
If you are, you know, just, you know, just a, irretrievably broke college student or something, I don’t know, maybe, I don’t know, maybe the, maybe, maybe the stock market stuff has you a little, little, little weaselly about four bucks a month. You can do other stuff to help my channel survive, thrive and whatever else, whatever else, whatever these YouTube channels do, like, comment, subscribe. And if you want to ask me a question, a question or more than one question, you can ask a thousand questions if you want, that I will answer on an office hours episode.
You can do so at the link up here, which is also down in your video description. If you need SQL Server consulting help, perhaps the output of these scripts is just not enough to help you figure out exactly who, what, where, when, and why did you, did your SQL Server dirty. I am available. You can hire me. I will make your SQL Server faster in exchange for money.
And as always, my rates are reasonable. If you would like some training content to help you get better at SQL Server, maybe without, you know, live and in-person Zoom call with me, you can get all 24 hours of my training, beginning, beginner, intermediate, and advanced. 75% off. It’s about 150 US dollars.
And you get that for life. Again, fully assembled link down in the old video description. SQL Saturday, New York City, 2025. That is this year. That is, oh boy, closer, closer by the day, isn’t she?
Uh, Saturday, May 10th, taking place at the Microsoft offices in Times Square. Uh, so be there or be square or be in Times Square. Uh, it’ll work itself out.
Anyway, let’s talk about the improvements to SP Health Parser. Now, um, this thing does a lot of work. I don’t want you to be too scared and taken aback by the code.
Um, the stuff that I want to show you in here is around, well, I’m going to show you a little bit of the code and then we’re going to talk through like, um, uh, other stuff. Like how it works. I’ll show you it working, basically.
Uh, so what this does is, um, it’ll go through and I think I skipped down a little bit too far. Let’s get back up a little bit higher here. So, uh, if you decide to log to a table, um, then there are some changes that I make.
Like I don’t log the blocking stuff to the table because I have, there are other facilities for doing that. If you want to log deadlocks to a table, SP Blitzlock does that. If you want to log, uh, the block process report to a table, we’re going to talk about that in the next video or two.
Uh, because I added, I added this to human events block viewer as well. So it’ll, uh, create these tables. Some of these are the new ones.
So, um, there’s one, two, three, four, five, six, seven, eight, nine, 10, 11, 11 or 12 tables that will get created. And all of the stuff will get logged to the table in here. Um, the new ones are, I believe, uh, memory broker, memory node out of memory, scheduler issues, and severe errors.
Uh, these are all the new stuff that I’m collecting. There are, there’s stuff in the system health extended event for all those. And I just decided to get busy with the XML on that.
Uh, if you are logging stuff to a table, um, I do a few things. Um, if you specify a schema that doesn’t exist, I will create that schema for you. If the tables that you want to log to do not already exist, then I will create all those tables for you.
That is what this lovely mess of dynamic SQL does, uh, all in here. So, uh, that’s a lot of fun, right? Like, oh, look at all this wonderful dynamic, very repetitive dynamic SQL.
Uh, part of what the, this thing does is it has a data retention policy on it. So, um, you, if you want to keep 30 days or seven days or two weeks of data, uh, this will help, help you, uh, achieve that. And it’ll start every runoff by deleting from the tables where, um, those, where the, the times are older than those dates or whatever, however you want to put it.
Uh, once that’s done, uh, you know, your temp tables get created. And let me actually skip down a little bit further. Let me make sure I don’t like cursor down to where I should.
Uh, so what this will do is if you decide to log to a table, it changes the select query slightly. And then it does a insert, uh, to the table. But another thing that it does, um, there’s a insert SQL thing that gets built up based on the table definition.
But, um, one thing that this thing does is it makes sure that you’re not, you’re only putting data in that’s new. So there’s this MD SQL part in here. And what this will do is find the max date currently in your extended event and currently in your logging table and filter out whatever data we collect to only get data from that point on.
So you’re not just constantly logging new crap in there. Cause that would just be a nightmare. Uh, and then, you know, we do the insert into the table.
And if we don’t do the insert, then we, um, then I just like would return the result out to you. So, uh, what we’re going to do is stop looking at code and come over here. This window has SP health parser stuff in it.
Uh, and I’ve set up this DBA database that has no tables in it. I just hit F5. Oh, oh no. This thing tried to, it’s complicated.
My computer is stupid. Uh, so with all of my store procedures, um, there is a help parameter and that help parameter will tell you all sorts of good information about, um, what the store procedure does and all of the parameters that are available to it and what those parameters do, their default value, stuff like that. Uh, so what we’re going to do is I’m going to run this in debug mode, just so we get a bunch of stuff back to look at together.
Uh, and I don’t want you to be scared at all because, oh, that should have been like seven or something, but it doesn’t matter because we’re only running this thing once. Uh, this does take a second to run because it does a lot of XML parsing. Um, I don’t know if we got to a point in here where the XML starts to get parsed, but, uh, boy, is there some heavy duty XML parsing.
Um, this isn’t my fault. I wish that there was another way to do it, but Microsoft stores all this stuff in XML. And so, uh, as your, your humble SQL Server servant, I am forced to parse that XML to make you happy.
But, um, anyway, this thing is successfully run. Uh, we have a bunch of debug output. This, uh, this would not normally get returned to us, but, uh, because I ran this in debug, we, uh, we get a bunch of other things back.
Uh, over in the messages pane, excuse me, over in the messages pane with debug enabled, of course, uh, it’ll tell you that we created tables and, uh, it’ll show you the, uh, inserts that we did. Anything that happens in the dynamic SQL, it’ll show in here. And if we get down a little bit lower, we should start seeing the, uh, insert queries.
Uh, this is the data. This is the dynamic SQL to find the max date, uh, that’s currently in whatever table we’re currently about to insert into. And then pass that in as a parameter down in here, right?
That’s this thing. So we do our insert and we, uh, get data and then we can look at data. So just to make things a little bit easy, let’s just grab, uh, one of these tables.
Let’s refresh this. And now we see all of the tables that got created in here. Um, I don’t know which one is going to actually have stuff in it.
Um, suppose we could try looking at weights by count. That usually has something in it. And here we go.
Here’s what the table returns. Uh, we have the collection time. Uh, we have the event time rounded. So the event time rounded, uh, by default, this will bucket by the hour. So all the weights that happened in an hour, it’ll sum up and give you the average wait time and the max wait time.
The max wait time column is a little tough to deal with because it’s the max wait time that’s been recorded like since. Actually, I don’t know since when it’s either since like startup or since the extended event has stuff in it, but this column gets very repetitive and it doesn’t always lead you to exactly where, um, you know, the weight spiked up to have, have a max wait time of two seconds for async network IO. But anyway, uh, this works pretty well.
Um, one thing to note about, uh, SP, uh, health parser is that not all, uh, events are going to have data associated with them. So kind of like coming back up here to where the tables get created. Um, some, like some of these, if you don’t have problems, nothing ends up here.
So if some of these tables are empty, that’s a good thing. Like for example, for example, like if like memory node OOM, that’s out of memory. If you don’t have memory nodes that, that like end up with out of memory conditions, there’s nothing, there’s no XML in there.
There’s not XML that says nothing happened, right? There’s just no XML. So nothing will end up in there.
So if some of these tables are empty for you, that is why. Anyway, uh, that is a quick overview of the improvements made to SP health parser. Um, I hope that you will find yourself, uh, enjoying these improvements, maybe logging stuff to tables, maybe trending these things over time.
You can do all sorts of fun stuff, like put them in an Excel file or I don’t know, use DuckDB or whatever, whatever crazy things you people do to analyze data. And you can, uh, start figuring out SQL Server performance issues. Um, you could also like do this stuff and hire someone like me to go through the data and figure it out for you, which is also a pretty good plan.
But if I do say so myself, anyway, uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video where we’re going to talk about improvements to SP human events block viewer.
Anyway, goodbye.
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.