Free SQL Server Performance Monitoring System Health Extended Event

Posted on by Erik Darling

Free SQL Server Performance Monitoring System Health Extended Event

Summary

In this video, I delve into the OCRAP section of my free SQL Server performance monitoring tool, Darling Data, which surfaces critical issues often hidden in the System Health extended event. This section is like a red flag area where you’ll find nasty things such as bad pages, memory dumps, suspect pages, non-yielding tasks, sick spin locks, and severe errors—basically everything that can go wrong with your SQL Server health. I walk through how to access this information via my GitHub repo at code.erikdarling.com, highlighting the importance of paying attention to these warnings as they can indicate serious underlying issues that might require immediate professional intervention or even a complete system overhaul.

Chapters

Full Transcript

Monitoring tool mogul Erik Darling here with Darling Data. And in today’s video, I want to go over what I call the OCRAP section. The OCRAP section are the real nasty things that can come out of the system health extended event. Normally, you might find that my free store procedure SP health parser would surface these things. move to sit there and run that and deal with the outcome. Well, you can use my free SQL Server performance monitoring tool and you can get all of this goodness done for you. Now, the reason why I call this the OCRAP section is because this is really is where a lot of like you’ll see like this server is not doing well type stuff. So, you know, bad pages, memory dumps, suspect pages, non yielding tasks, sick spin locks, severe errors, all sorts of information about CPU scheduler health, IO warnings, and memory conditions, like stuff that you don’t want to see, but stuff that if it’s happening, you should pretty, you should know about pretty quickly. So if you want to check this out, it’s all at code.erikdarling.com. That’s the shortcut to my GitHub repo. You go there, go to the performance monitor repo, there’s releases with all all of the zip files that contain all of the executables you need to get up and running with this. There’s also a wonderful readme file. That wonderful readme file contains all sorts of pertinent information to how you can get up and running with this without sending me emails asking me about why it’s saying that your server certificate isn’t trusted because you didn’t say to trust the server certificate. So with that out of the way, let’s jump over here. And let’s look at the system events tab. Now, again, like, this is the Oh, crap section. And if you see anything in here, you should call a professional doesn’t have to be me. I might not even be the right professional to talk to. But if you see stuff getting populated in here, you might want to, you know, show some concern with this stuff. So let me just quickly do that. So see some stuff. But so under here are corruption events. So if we detect bad pages, if you have any dump requests, no jokes, right access violations or general access violations. If you have non yielding schedulers, latch warning, six spin locks, or anything like that, that will show up in here and you will, you know, undoubtedly cry. I don’t know, switch to Postgres, whatever it is people do these days. Under severe errors, you know, like I, obviously, obviously, every single server has some errors on it, you know, mostly, you know, it’s like, it’s kind of like me, it’s either like either deadlocks or me killing off queries that I’m annoyed with. So there are some severe errors in here, which are mostly one of those two things. So let’s not worry about the video crap section is when stuff happens in here. Yeah, you run run for the run for the jungle, I guess. What is it? I don’t know. Anyway, scheduler issues, this will tell you tell you not about non yielding schedulers over over time. There is a handy little grid down a little status bar down here that will tell you about any current stuff that is going on.

There is a lot of pressure stuff in here. If this graph populates, you are having some memory issues. CPU tasks. Well, I mean, this is this is sort of a running trend of like how many workers you’re allowed to have and how many workers you’ve created. So if any blocking occurred, so like if you see that blue line, get up real close to that dotted sort of like, I don’t know, like Marigold line or something, that might like, like that might be something you care about, because that would mean that you are running out of worker threads. And that would mean, you know, thread pool and potentially other unfortunate circumstances. So worth looking in there. Memory broker stuff, you know, sort of like, you know, what’s consuming memory, things like that. And then memory node OOM, which is something no one wants to see. Again, I don’t have any of this bad stuff going on on my servers. So you know, we’re kind of lucky there. We did see like, at least an alert pop up. So you know, there’s a sign of life, I can acknowledge that alert, and things will mostly go away. But anyway, just, you know, again, this is all from the system health extended event, this is all running on most versions of SQL Server, and my free monitoring tool goes and collects it and will warn you about the old crap stuff. Right. So like important stuff for you to know about if you are dealing with SQL Server, because if you are dealing with a SQL Server that has like a lot of stuff going on in here, like a lot of stuff going on in here, you might might need to just find a new job, right? Fill out a resume, I’m done, quit.

We’re through here. Sorry about everything. I don’t know. I’d be scared seriously.

Anyway, thank you. Thank you. I hope you enjoyed this. I hope you enjoyed this. I hope you enjoyed this.

Bye-bye. Bye-bye. Bye-bye. Bye-bye. Bye-bye.

Bye-bye. Thank you so much.

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