How I Use sp_BlitzFirst To Profile SQL Server
Video Summary
In this video, I delve into the practical applications of SPBlitz scripts in my day-to-day consulting work, specifically focusing on SPBlitzFirst and SPPressureDetector. I share how these tools help me identify performance issues and gather valuable data points that guide further troubleshooting. I also discuss the nuances of running SPBlitzFirst using SendStartup or sampling for a period of time, highlighting why I prefer the sampled mode with seconds for its detailed insights. Additionally, I explain how SPBlitzFirst complements my work by providing a snapshot of what happened over a short timeframe, while SPBlitzHumanEvents helps dig deeper into the root causes of performance issues.
Full Transcript
Erik Darling here with Darling Data and continuing on with how I use the SPBlitz scripts in my day-to-day consulting life along with all the other store procedures that we’ve talked about in this series. Some mine, some others. It would be kind of, well, I don’t know. Can’t not talk about these ones. One thing that’s bothered me consistently over the last few videos is that I am always using my O-Stress login. He says SSMS Windows for some reason. I forget what I was testing, but now I’m just like permanently logged in as O-Stress.
Usually I’m SA, because I’m like the coolest person who uses my demo VM, so whatever, but okay. So, SPBlitzFirst is pretty cool. A lot of the times I use SPPressureDetector because it digs in a little bit further on some things that I usually end up having to troubleshoot.
It gives me some additional data points and, you know, interesting things to look at. But I still do end up using SPBlitzFirst for a number of different reasons. And also SPBlitzFirst in the sampled mode with the seconds is sort of what propelled me to write SP underscore human events because, you know, SPBlitzFirst tells you what happened over a period of time.
It doesn’t tell you how it happened, right? It doesn’t tell you what caused stuff for everything. So, I did, like, that’s, like, a lot of why I wrote SP human events is like, okay, well, if, like, I spot something just absolutely crazy in that 10 second window, like, then I need to dig deeper and I want to be able to do that quickly without, like, you know, like, like extended event gooeying things and being annoying and all that other, having to, like, parse XML on the spot and all that other glorious nonsense that you have to do to work with the profiler’s replacement.
So, I don’t know. So, I don’t know. First easiest way to run SPBlitzFirst is just to use SendStartup.
You’ll get information about, like, what weight stats you’ve accumulated, what your files have been up to from a read and write perspective. You’ll get a bunch of perfmon counters. You’ll get Holy Mother of God, so many perfmon counters.
I gotta be honest with you, I rarely look at this except for, like, the bottom, like, couple few lines. The stuff that I usually look at in here is batch requests, compilations, and recompilations. There’s just a lot of perfmon counters in here, and I just don’t always care about all of them enough unless something, like, really just jumps out and screams, like, hey, I’m real weird.
Fix me. But there’s a lot of stuff in here that I just tend to skip over because it’s just, it’s sort of information overload a lot of the time.
But the other way to run this is to sample your server for a period of time. And the expert mode parameter is really extra useful here because this is what gets you a whole lot of extra result set stuff that you don’t get if you just run it for a number of seconds. So, the first thing that it does is actually runs spblitzwho.
It’s actually the first thing, the last thing that it does. spblitzwho is sort of a slightly less configurable version of spwho is active. Adam Mechanic once told me that spblitzwho is mostly right, and I was pretty happy with that.
So, if there were anything running on my server, it would have appeared up here, and the results would have been mostly right. So, that’s pretty cool. The second section down is, I believe, what Mr. O calls the headline news section.
So, anything of particular note that spblitz first checks for would show up in here. So, kind of coming back to, again, what sort of got me to write sphumanevents was, you know, just again, I’m going to reuse the example. Let’s say we had 400 batch requests a second, and like 399 of them either compiled or recompiled.
Then I would use sphumanevents to look at compilations or recompilations and track down exactly which queries that was happening to so we could figure out why it happened and how to fix it. Right? So, if I think that might be something that I did with sphumanevents to dig in on that. You know, other stuff that, you know, would show up in here would be like blocking, long-running queries, things like that.
So, there’s all sorts of stuff that, like, again, like it tells you what happened but not like what caused it or why it happened. Right? So, like that’s when I would use sphumanevents to dig in on like blocking, compiles, recompiles, waitstats, or query performance. Speaking of waitstats, the next section down is where you get all the waits that happened during that 10-second period.
Again, my server isn’t terribly busy so there’s nothing really all that interesting in here. But, you know, we do have all that. If you’re, any of your, anything happened to any of your drives, whether reads or writes, that would show up here.
We would get all the perfmon counters that happened during that window. And it would also, if we, you know, decided we really wanted to like examine the plan cache, we would be able to do that. If I really wanted to look at the plan cache though, I would probably just use spblitzcache.
But also like I spend a lot of time these days avoiding the plan cache. I spend way more time in query store doing stuff because that’s where there’s just far more interesting things going on. Better historical data, stuff like that.
And then, of course, like I said before, the final thing that it would do is run spblitzwho. And if anything, we’re running here, it would show up in this section and again be mostly right. So that’s pretty much the way that I use spblitzfirst these days.
Nothing, nothing, that’s been like pretty much the way I’ve used it like forever and ever. Sometimes I might sample longer or shorter periods of time, kind of depending on exactly what I’m trying to catch happening. But, you know, this is a pretty good starting place for you to run spblitzfirst to start examining stuff on your servers.
So, yeah, there’s all that. Anyway, thank you for watching. I hope you vaguely, mildly enjoyed this video in some bizarre way.
I hope you enjoyed yourselves. I hope you’re wearing pants. Kinda.
Maybe. If you like this video, feel free to hit the like button. If you like this, and also if you like this video, you should hit the subscribe button so that when I make more videos that you will undoubtedly like, you will be alerted to them instantly.
That would be, that would be the wisest choice that you ever made in your life. Humbly. My humble opinion.
That would be the single smartest thing you could do. It wouldn’t be, you know, learning a trade or it wouldn’t be investing for retirement. It wouldn’t be taking care of yourself, dieting and exercising, living a healthy lifestyle.
It would be subscribing to my channel. Alright. So, I’m gonna do some more of these, I guess. I’m gonna start uploading this one.
No time like the present. So, yeah. I will see you in another video. At some point when Hyper-V Manager goes away. Thanks.
Ah, good. Alright. Before I stick this landing too hard, thank you for watching.
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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.