Everybody loves freebies, and the SQL Server community is full of them. Free scripts, stored procedures, blog posts, books, and helpful advice from some of the smartest minds are all available to you right now. So whether you’re just starting your career as a Database Administrator or if you’ve been doing this for decades, I’m sure you have discovered plenty of free resources that you rely on every day.
If you haven’t, well…consider that maybe you’re doing it wrong. Seriously, other people have done a lot of hard work to make your life easier. You’ve already got enough to worry about, so you should enjoy the fruits of their efforts.
In this post, I’m sharing 10 of the free resources from some of the best and brightest in the SQL Server community that we here at Straight Path rely on regularly. These sites not only provide information and tools to allow us to perform daily database administration, but they also include items that make troubleshooting problems much, much easier.
Here you go.
WhoIsActive is an indispensable tool for troubleshooting what’s happening now with queries on your SQL Server. There are many ways to use the WhoIsActive, and Adam Machanic’s WhoIsActive site has pages of documentation with examples on ways to use it beyond the default execution. Do yourself a favor and learn to capture the results into a table so you can review problems that happened while you were away from the computer doing more important life things.
The community-supported First Responder kit is an astounding suite of stored procedures for analyzing your SQL Server instance. You can start with sp_Blitz to get a quick look at possible issues with your instance (Pro Tip: Use @CheckServerInfo = 1 to get additional server info.) There are many other helpful stored procedures for troubleshooting specific issues, like sp_BlitzIndex, sp_BlitzCache, and sp_BlitzLock, and once you use sp_DatabaseRestore to restore a database from full, differential, and log backups, you may never use another method again.
Everyone has questions, and there is probably no place on earth that has had more SQL Server (and other systems, too) questions answered than StackExchange. Many of the smartest SQL Server minds often answer questions here, so you’re in good hands asking about a quirk or issue you encounter. Just don’t ask others in the forum to do your job for you.
Does PowerShell seem like a scary language to you? If so, make your way over to dbatools and look how easy they have made PowerShell. With hundreds of SQL Server-specific cmdlets to make database administration faster and easier, it’s unfathomable to imagine any other way to care for dozens or even hundreds of instances. Be sure to check out at Start-DbaMigration, which I’ve personally used dozens of times to test and execute database migrations in a fraction of the time I spent before discovering dbatools. It’s like magic but real.
Erik Darling is a very giving man. Over the last few years, he’s not only contributed to both WhoIsActive and the First Responder’s Kit but he’s also made a few nifty stored procedures of his own. Need to troubleshoot CPU or memory pressure? Try sp_pressuredetector. Want to drill into query store beyond the GUI? Use sp_QuickieStore. He’s also got scripts for more stored procedures, helper views, and his way to log database activity to a table using WhoIsActive. Lots of hidden gems with documentation and links to videos demonstrating usage. Like I said, he’s a very giving man.
Do you want to find the things wrong with your SQL Server instances? At first glance, you may decline, but if you want to know the awful truth (or maybe, just maybe, to confirm everything is all good), then download Glenn Berry’s diagnostic query scripts, which are specific for each version of SQL Server. Carefully walk through each of these meticulously commented queries and learn not just about your databases but also about best practices for your SQL Server instances. You’ll likely become smarter just by executing them and examining the results, which you can also put in his provided spreadsheets.
Lots of DBAs have scripts for things like creating a performance baseline, fixing VLFs, and updating statistics. So do the folks on Microsoft’s Tiger Team, who have worked on the various builds for SQL Server. Smart folks like Pedro Lopes, Argenis Fernandez, and Pam Lahoud have populated this repository with their go-to scripts right out in plain sight for everyone to enjoy. As a bonus, there’s also a “sessions” folder which contains videos and materials from various presentations the Tiger Team members have given over the years. Get your learn on.
Mike Walsh likes to say the Straight Path DBAs have seen a thing or two, and in our collective experience Ola Hallengren’s stored procedures and jobs are still often the best solutions for most clients’ needs regarding database backups, corruption checks, and index maintenance. They’re reliable, and configurable, and after a decade or so, most DBAs are familiar with them. Do yourself (and the person who replaces you after you get a promotion) a big favor by setting up these ubiquitous scripts and jobs on your instances.
Way back in 2001, Steve Jones and some of his friends decided there needed to be a central location for SQL Server questions and resources. More than two decades later, SQLServerCentral still exists, with helpful blog posts and articles from various members of the community, as well as a forum for discussing SQL Server-specific issues. There are also incredible resources for learning, with Stairway programs designed to walk you step-by-step through features and concepts, as well as a Books section that features dozens of free downloadable eBooks.
Does your SQL Server seem slow? If so, you should ask it where it hurts. Over a decade ago Paul Randal wrote a post about wait statistics that contains a query every DBA should have at the ready. (The query has been updated since 2010, so you can still use it.) Executing the query will return a list of the most frequent wait types for your instance, which often have cryptic names like BROKER_TO_FLUSH, FT_IFTSHC_MUTEX, and KSOURCE_WAKEUP. The best way to decipher these? Go to the SQL Skills Wait Types Library and find out just what they mean, and what – if anything – you need to do anything about them.
As you can see, there is no shortage of folks who have spent countless hours creating information and tools to make your life as a DBA, well, if not easier than definitely less difficult.