Introducing sp_PerfCheck

Introducing sp_PerfCheck

---

Video Summary

In this video, I introduce a new stored procedure called SP_PerfCheck that I’ve been working on, silently and slowly but surely. This procedure is designed to quickly assess important performance-related settings and issues on your SQL Server, whether it’s an on-premises installation, a managed instance, Azure SQL DB, or Amazon RDS. The goal is to provide a straightforward overview of key performance metrics without delving into other areas like backups or database integrity checks, which can be complex due to the evolving landscape of SQL Server features and cloud environments. SP_PerfCheck focuses on performance configurations and settings, offering a useful set of information that can help you quickly identify potential issues and optimize your server’s performance.

Full Transcript

Erik Darling here with Darling Data, and in today’s Darling video, we are going to introduce a brand new member to the family. No, not a new employee, not a new baby. Are you out of your minds? I am damn near 45 years old. What business do I have procreating? This is a new store procedure that I’ve been working on, silently, slowly, but surely, called SP underscore PerfCheck. The goal of this is to quickly look at important performance-related settings and issues on your server, your SQL Server, sort of regardless of where it runs. So this is compatible with on-prem SQL Server, managed instance, Azure SQL DB, and Amazon RDS. Everything else is just a VM in the cloud, right? And I can already hear what you’re saying. Well, why do a lot of things? Why do all this stuff in your own procedure? Why not just do stuff with SP Blitz? Well, SP Blitz is like an overall sort of health checky thing. I don’t, in this procedure, I don’t spend any time looking at backups, particularly backups, because like the backup story gets harder and harder to tell with like AGs, like people taking backups on secondaries, the cloud where who knows what. MSDB is not like a great source of truth for a lot of things. I’m not going in, making sure you’re running DBCC CheckDB. I’m not looking at like torn page and check some page verification stuff. I’m just focusing on performance metrics and performance configuration stuff. That’s it. That’s the whole point. I don’t really do anything else in there. I get in, list out a whole bunch of important stuff and try to get you the right information. Now, this is largely only compatible with SQL Server 2016 and up.

There’s a lot of stuff that on 2014, it just gets real annoying or before 2016, it just gets real annoying to figure out. So I left that out of the batch. Given that SQL Server 2019 just hit the end of mainstream support, going back prior to 2016 seems rather wasteful. So we’re not going to do that. So let’s take a quick look at a couple of things. First, over on my website, there is a URL for SP PerfCheck. Now, this doesn’t go through a lot of the stuff that some of the other URLs do, because what I wanted to have here was a largely accurate, maybe not completely cut out list of all of the things that it looks for. So if we zoom in a little bit here, we’ll see the commands and stuff that it looks for. Some of this stuff, if you’re used to running SP Blitz will be familiar to you.

Like I did try to make it so that if you’re used to seeing performance-related metrics there, you will also see them here. But I also go and look at a lot of other stuff. So settings, storage performance, database configuration stuff, query store stuff, what do you call them, database scope configurations, things like that. And I do some looking at weight stats and, I don’t know, this section is for like real weird stuff. So I put it kind of on its own.

But if you scroll down through all this, you will get some more information about all of like, like there are URLs in the procedure that will get you to all of this stuff in here. As for the store procedure itself, of course, it starts off with the usual ASCII art that I handcraft with nothing but concentration and my deep affection for you. And then, of course, you know, fun fun ensues after that.

And over here, a couple of things that are important to note. One of them is if you want to get help with this procedure, there is a help parameter because I put a help parameter in all of my procedures so that you can get help so that you don’t have to email me to ask me what a parameter is. So there’s not a lot of parameters for this one just yet because this is sort of in the newish stages of development.

Doing a lot of just sort of like finding my sea legs with it, figuring out exactly what I want to do and check. Figuring out like sort of getting a good idea like what’s in scope for the results. If you need, if you run into a problem with this and you feel like the problem might be dynamic SQL related, there is a debug mode which will print out all of the stuff that will return results for everything in like the temp tables and stuff.

And then, of course, in the messages tab, you will see all of the dynamic SQL printed out. That includes for the like the overall stuff and for the database by database stuff. As for actual results, as for like what the stuff that you will see in here, it is what I think is a pretty useful set of stuff.

Up at the top is sort of a summary of important things that like things that are good for you to know about the server. So that’s this section up here where I just like highlight all the normal stuff. You know, well, this is of course the version of the store procedure, not SQL Server.

You know, and this is like, you know, having stuff like this is really useful, especially if, you know, you are making like a spreadsheet of, you know, servers or you’re a consultant and you want to take a quick screenshot that gives you like a nice like overview of stuff in the server. Right. Like this current server configuration, all sorts of good things in there for you to dig into and, you know, have have a screenshot of so that, you know, the state of the server when you looked at it.

And then down below are the results where you will see all of the stuff that was picked up by by SP perf check. And of course, this URL column over here will bring you to the correct spot on the page for whatever finding we have in here. And this server is in relatively good shape.

I leave something slightly out of tune. So when I run this stuff, I have like a little built in unit test for it. But there was a lot of time spent configuring and unconfiguring things to make sure that the various checks work. So if you are in the market for a store procedure that gives you a good performance overview of settings and bad stuff going on and like there’s stuff about weight stats in here that doesn’t, that hasn’t popped up on this because I haven’t been doing a lot of like crazy performancey stuff on here where wait that like meet the weight stats, thresholds, but trust me there’s good stuff in there.

So if you are looking for a performance check of a SQL Server that you are looking at, whether it’s Azure, managed instance, on-prem, VM, RDS, whatever, this thing should have you pretty well covered. So if you have any questions, comments, or concerns about the code involved here, you can either go to code.erikdarling.com. That’ll bring you to my GitHub repo where you can open issues, ask questions, suggest improvements to things.

Or you can go to my website and contact me. There’s a contact form on my website where I will say for support, please go to GitHub. Because that’s where I do my script support.

I don’t tend to do script support via email because it is very hard to track issues and stuff via email. I have a difficult enough time sensibly maintaining a nicely organized GitHub repo with the fury that I work, the furious pace that I work at. So anyway, thank you for watching.

I hope you enjoyed yourselves. I was going to say I hope you learned something, but you’re probably going to just learn about this script existing. So that’s a plus, right? That’s something to learn.

And I hope that you find this utility script useful. You know, again, these are not tools that I make for the community. These are tools that I make for myself that make my job easier, that I choose to share with the community.

But I do want other people to use them and I do want to get feedback on them. I do want to improve things so that they are more useful for everybody, myself included. There is a benefit for all of us here.

We all get better with your help, with our help. Something like that. Anyway, thank you for watching.

Go get this thing from code.erikdarling.com. It goes right to my GitHub repo. Boom, boom, boom, boom, boom. All of a sudden, there you are.

Professional SQL Server Performance Consultant. All you need, a few stored procedures. A couple books. About 15 years of forgetting everything else important and stuffing your brain full of database stuff.

Minor, minor things. Anyway, I’m going to go now. Thank you for watching.

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.