SQL Server First Responder Kit [Free DBA Tool of the Week]

I can’t do a series on free SQL Server DBA tools without including the SQL Server First Responder Kit put out by Brent Ozar and his company (and the SQL Family who helped)! This is that post.

A couple weeks ago, I was inspired to start this blog series because of an amazing PowerShell module called DBATools which impressed me moving agent jobs and logins in an Availability Group for a 2005 – 2016 migration we’re doing for a client. What can I say, I like free tools. You should too.

Hence this series. So without further introduction.. This week’s post. . .

Brent Ozar – The Man

I’ve known Brent for about 7 years.  I was 10 years into my career and had decided to start blogging, tweeting and giving back. His example among others encouraged me to start speaking at events also. Brent was an inspiration to me starting out with the sharing. Lots of tips. His FreeCon event. He even helped me encourage other new bloggers and speakers. I had a series on starting blogging and speaking. He, without hesitation, agreed to give some really poorly produced interviews on this blog even. Clearly he cared about this SQL Server community.

Anyway, this was Brent before he was a SQL Server MVP, before he was running the pretty neat company he is running right now. He’s the same today, just older looking (I have less hair, so there’s that). His motivation was really in giving back to the community and remains that. Sure I have to imagine there were goals and some form of a fuzzy map to his future endeavors, but mostly? He was in it to have fun, help others and see how long he could keep at this. So far, so good, Brent. Keep punching hard 😉

Even in my run up to leaving full time life 5 years ago he was there with tips. Recently as I re-launched Straight Path Solutions as a SQL Server consultancy he was there with help, advice and encouragement, even being in the same space of sorts. That’s looking out for others.

You can find his personal blog which is full of insights about life, business, cruise ships and anything else he finds interesting. He shares quite freely. You can find him at his company, Brent Ozar Unlimited or you can find him on twitter. 

sp_Blitz – A Genesis

This started in 2011 with a stand alone script. You can see a changelog for the tool which starts with version 2. It’s now over version 52. Go look at that changelog. Look at all the folks who were not only helped by the tool but inspired to help give back.

The sp_Blitz procedure performs a SQL Server health check on your environment of sorts. Some of the checks are the same checks and types of checks we perform here with our SQL Server Health Assessment, we also add in a lot of manual review and interview questions to augment some of the raw black or white data points. When I go to do a health assessment on a client? I love seeing a client who installed and used this script. It means they care about their environment and they started looking first. When we get called in to a longer term Senior Remote DBA retainer relationship, I love seeing this already there. Same reason – it’s great getting involved with a firm who investigated issues, worked out some of the simpler challenges and want that senior help to keep things proactively good or help them tackle the more tricky challenges.

This is the tool that started it all. I remember Brent talking about this tool on twitter, in videos, at presentations. It was his baby, his idea, and he gave it away from free from the beginning. It’s been a staple in the SQL Server community for about 4 or 5 years now. Up until recently it was “99.9999999999%” free – you had to “pay” with an e-mail address. No biggie, you get on a weekly newsletter with curated content from his team and some occasional announcements about their company. That’s a fair price. In June of 2016, though, he decided to release this as an open source project. Easier to contribute. Easier to download. More democratized, as the hipster techie types like saying these days (I think they still like saying that).

So enough about the tool’s darn story. What does it do? It checks the health of your SQL Server environment. If you clicked next-next-next-finish and never changed defaults when you installed SQL Server, it will gently tell you why that was bad. If you have wait stats that are out of whack, it will tell you that. It prioritizes the findings. It’s a mini-health check of sorts. Though as you can see when you play with the tool, you likely won’t find the list of changes for you to make so “mini”, it is quite detailed. Check out the tool. As with any free script on the web – review it, understand it, read about it, look at what others say about it. But do download and run this. Check out the results. Make a list and get to work. You can get the latest release of the entire kit on Github here.

But Wait! There’s More!

Brent is someone who understands marketing, but he didn’t just call it a kit to make sp_blitz sound bigger. Instead he includes a handful of other tools he and his team have created over the years. I’ll be less verbose here, but seriously, go download the kit and see more yourself! Start checking the health of your SQL Server. You can fix many things yourself, I promise.

• sp_BlitzCache –  Learn about your plan cache. Understand which queries in cache are problematic. Get some tips about query tuning.
• sp_BlitzFirst – This used to be called “sp_AskBrent” – look at your environment right now. Stop using Activity Monitor! Use this or a tool like it instead. A few modes of operation here to help you out.
• sp_BlitzIndex – Learn about your indexes (indices, if you prefer). Helps troubleshoot issues in design and performance.
• sp_Blitz – Come on, that’s the tool I just took a few paragraphs to tell you about.

Thank Brent!

And thank all those people who contributed bugs, suggestions, improvements, ideas and thoughts to this project. So cool that we have such amazing people and resources in the community. Go look at his blog. Look at his twitter feed. There are so many out there who give as much. We are pretty blessed to be involved in this whole SQL thing. Go take a few minutes and thank Brent for all he has given to this SQL Server community. He’ll demur on taking the praise, but tell him thanks anyway.

Postscript.

I asked Brent how many hours were put in, asked about the reason to go to open source via github and about the story of the tool to make sure I didn’t miss anything. I also asked him if he wanted to add anything. He answered quickly, with his permission to quote him (emphasis mine, took a few things out that I covered above):

The first tool was Blitz, but not as a stored procedure – just as a standalone script where people would highlight line by line. . . .  In 2011, I brought out sp_Blitz at PASS, and then Kendra wrote sp_BlitzIndex, and Jeremiah wrote sp_BlitzCache, then I wrote sp_AskBrent.

We’d always wanted to open source the scripts, but the thing that pushed us over the edge was Microsoft embracing open source this year. They seem to be implying that contributions to open source projects on Github would help people in their quest to become MVPs. I’ve always gotten emails from people offering code contributions, and plus our employees work like crazy on these scripts – so I thought, why not do it in the open and let people get credit for it?

PASS was a factor too – this year’s Summit speaker contract said you could only use a tool onstage if it was open source. We’ve got blog posts coming explaining how presenters can use sp_BlitzFirst to gauge wait stats, Perfmon counters, and resource-intensive queries during a 30-second sample, and that really helps illustrate performance tuning concepts. We’ve been seeing people do more user group sessions on these scripts, so we thought, might as well make it easier for them to do it onstage at Summit too if they want.

I knew I wanted to work on sp_Blitz when I saw people get genuinely excited about what they learned. I kept seeing people go, “Wow, I had no idea that was stored in a system table that I could query!” I don’t think most users understand just how awesome SQL Server is – there’s such a wealth of diagnostic data freely available, but…it’s not, really. It’s a pain in the rear to query. But what if I could start building a snowball and rolling it downhill, gradually adding more diagnostic data? I knew it’d be big if I just started pushing, and it keeps getting awesomely bigger.

If I had to guess on hours, I’d say Jeremiah, Kendra, and I each put in at least 10 hours a month in these tools for the last 5 years, so that’s 1,800 hours right there. Since we hired Erik Darling, that number has grown big time – I’m going to guess we’re putting 10-20 hours a week into these tools now, so probably 3,000 hours, plus the community volunteers.

The one thing I want people to know: run sp_Blitz on your production servers RIGHT NOW.  Not later, not next week, RIGHT NOW. You would not believe how many times we catch databases that aren’t being backed up, corruption running rampant, horrible poison waits, or awful configuration issues.  It’s okay if you don’t FIX these issues right away, but at least be aware that they’re happening.

• Brent

What free tools do you like and use? I have a lot more to talk about. Scripts, downloads, etc. This series can probably keep going for a long time. But tell me about your favorites in the comments. Tell me about one you’ve written in the comments or e-mail.