How I Use sp_Blitz To Get The Results I Want
Video Summary
In this video, I continue my series on the store procedures I use in my day-to-day consulting work to help clients identify and resolve issues with their SQL servers. After covering a range of scripts like SP Pressure Detector, SP Quickie Store, SP Human Events, and SP Human Events Block Viewer, as well as diving into SP Who is Active, I now turn my attention to the SP Blitz scripts. Created during my time at Brenoza Unlimited, these scripts have evolved significantly since their initial development. While some of the code formatting remains a point of pride and shame for me, I’ve learned that clean, readable code is essential for effective troubleshooting. In this video, I focus on two key features: using the `check server info` parameter to gather comprehensive information about the SQL server environment, and leveraging the ability to skip checks for items that don’t pertain to my specific consulting needs. This approach helps me present a more focused set of results, making it easier for clients to prioritize their attention on critical issues.
Full Transcript
Erik Darling here with Darling Data. And I’m going to pick up kind of where I left off with my series of videos about how I use different store procedures in my day-to-day consulting work to help people figure out what’s wrong with their SQL servers and fix it in exchange for money. That’s my business model. Take it or leave it. So, we’ve covered SP Pressure Detector, SP Quickie Store, SP Human Events, and SP Human Events Block Viewer. We’ve covered SP Who is Active. A lot of SPs have been covered in these videos. So many SPs. It’s hard to believe how many SPs there are. Everyone thinks they’re special. Anyway, I’m going to spend the next first, I guess, five videos talking about how I use SP Blitz scripts. Of course, you know, I was an employee over at Brenoza Unlimited for some years. And while I was working there, I did a whole lot of work on the scripts, you know. Some I’m more proud of than others, I think. If I had to pick one thing to be particularly embarrassed about, it’s the way that a lot of the queries are formatted in there.
They’re real ugly, the way I wrote them. And, you know, it’s kind of a mark of shame for me. But you know what? Code formatting is one of those things that you evolve over time. And I am just in a more evolved place code format-wise now than I was then. And, you know, I think then I was just happy if it worked and worked relatively quickly. And now I’m very picky about all sorts of things like not having tabs and not having white space and, you know, having things sort of spread out so you can read them easier, not just have like a bunch of stuff crammed on one line.
You know, like, don’t tell me just put it in a CTEto make it more readable. Most definitely doesn’t work. But, yeah, anyway. My far and away favorite parameter, and one that I think I wrote at least a couple few of the checks in here to deal with, is check server info equals one.
And check server info equals one will get you all basically everything in this list. Everything in this list will show up if you use check server info equals one. You can read the list yourself, but, you know, just because I don’t want to have a 30-second video.
OS details, if you have lock pages and memory enabled, if you have instant file initialization enabled, which service accounts equals server and agent run under. I think browser too, but I forget. How many CPU cores you have, like kind of like what their layout is, NUMA-wise.
You know, like, you know, how many, like if you have two core, two sockets and 10,000 cores per socket, you owe Microsoft your life. How much memory is in the server, kind of how that memory is assigned to CPU cores. The patch level of your server, if it’s part of an AGR cluster.
The last time it was restarted. How much drive space you have and how much is available. The last time your server was restarted. I think, wait, did I already say that? I think I already said that.
No, okay. Cut, cut, cut, cut that. What did they say? Anyway. If your server is virtualized, we’ll try to give you some information about that. If you have any CPUs and or memory that are offline that are not available to SQL Server because of you installed the wrong version of SQL Server and you have, like, you’re limited to 20 cores or someone set up affinity masking or something else goofy, then that’ll be that. That can be particularly helpful for folks on standard edition who have done something asinine with their VM, like, give it 16 cores, but give it one core per socket for 16 cores.
And then SQL Server is like, well, I can only see four of them. You only have four cores now. The other 12, who knows?
They’re just sitting there twiddling thumbs or whatever CPU cores have for twiddling. What power plan your CPUs are operating under, that’s obviously probably far more important for, like, a physical server than a VM or for anything in the cloud. But, you know, I am a bit superstitious when it comes to the balanced power mode thing.
So I always like to make sure that any server that I touch runs in high-performance power mode. You know, you can go green with your tablets and personal laptops and, you know, you can compost something to make up for it. But when CPUs cost $7,000 per core, I want them spinning at full speed.
Sorry about that. Mother Gaia. I don’t know. There’s probably worse offenders out there in the world.
It’ll tell you if you have multiple SQL Server instances stacked on one server, if you have any of the SS, AS, IS, or RS components installed. And then if you have any significant weights, it’ll give you, like, the top three or something, or top five or something like that. I forget where the top is in there.
No one would care if they were the bottom weights. So check server info. Pretty awesome. Now, one thing that I end up doing with servers after I’ve gotten to know them a bit is using a really often overlooked feature where you can skip checks for things you don’t care about.
So you need to create a table and a database. And you need to have three columns in that table. Server name, database name, and check ID.
And you can tell SP Blitz where that table lives. And it will skip for whatever server, database, and check ID you put in there. Whatever you configure it to do, basically.
So for this, I wanted to check everything in every database except these things, right? So, like, I’m going to leave server name and database name null because I want these checks to apply no matter where we’re doing them. And, like, there are things that, you know, really just don’t, like, affect my consulting.
Right? Like, this list of things in here is never, like, if I start pointing, like, any of this stuff out to someone and they’re like, yeah, but my server is on fire and, like, there’s 30,000 block processes. Can we focus on that?
I’ll be like, oh, yeah, it’s probably a good idea. So this is just stuff that is not for me. It might be important to you if you need to audit certain things, if you care about who owns a database and privileges and all that other stuff. And this is for you.
This is not for me. So I’ve already run SP Blitz, and we’re going to look at the results with all that stuff skipped. And I know that you’re probably going to be pretty horrified by the things you see in here. You’re going to say, Eric, you are a terrible database administrator.
You have not taken backups. You have not run dbcc checkdb. You have dropped clean buffers. Your tempdb is on the C drive. And I will say, yes, it’s a VM. And it doesn’t matter much for me what happens to it.
But I have this stuff everywhere I go. So, yeah, there’s just stuff that is just not going to matter to me. And I like to skip that stuff.
And I like to keep a nice tidy set of results there that are generally focused on things that would make a difference to people. Or at least for what I do anyway. I don’t know.
Let’s see what else we have in here. I don’t know. So, all right. So, you know, to be fair to me here, there’s some stuff that I leave. Some stuff that I leave is in the incorrect state so that when I run this, I know that checks are working and things like that.
So, you know, on the occasion that I do work some with SP Blitz, I like to make sure that the checks that I write will fire correctly. So, I tend to sometimes leave things in not as good a state as they could be. So, I don’t know.
There’s not a whole lot of interesting stuff in here, is there? PBS pre-allocate. Ooh, la, la. Yeah, look at that. Look at that. I got all the smart settings. I got instant file initialization. I got log pages and memory.
Yeah, I’m doing well. I’m rocking and rolling here. Anyway, those are the two things that I like to do with SP Blitz. I have to use the check server info parameter.
And once I’ve gotten to know a server and I know what doesn’t matter to me, I like to use the ability to skip checks. So, I don’t get overwhelmed with 10,000 lines of things that don’t concern me. So, yeah, that’s that.
Anyway, I’ll cover some of the other Blitz scripts that I use. I think, I forget exactly which ones I’m going to be covering at this point, but there’s going to be four more of them. Coincidentally, there will be four more of these to fill five working days of blog posts.
And you can make of that what you will. I don’t want people watching SQL Server, getting SQL Server emails on the weekends. It’s rude.
Rude. Anyway, thank you for watching. If you like this video or you think you would like other videos better, which you might, it’s possible, you can subscribe to my channel. If you like this video, you can smash that like button.
That was not my hardest punch. And I’m going to record a few more of these. So, I will see you over there.
Thank you for watching. Hope you enjoyed yourselves. I hope you learned something. And I will see you soon in another video. It’s been a while. Thank you. Thank you.
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.