Free DBA Superpowers
Free SQL Server Tools
The title might be a little silly but the truth is that a lot of smart people have done the heavy lifting that you can benefit from. The SQL Server community, often referred to as #SQLFamily, are a helpful, giving bunch of people. They develop awesome tools to help solve a problem they had. Then they release it to the public and continue to improve and support it. Truly above and beyond what you would expect.
Diagnostic and Troubleshooting
SQL Server First Responder Kit
If you’ve been around SQL Server for very long, you have likely heard of Brent Ozar
. Brent developed the First Responder Kit as a tool for someone walking up to a server for the first time. As a consultant or just the guy that inherited a server, you need to assess the situation. The tools are also great to have on hand as part of your regular DBA toolbox. Download
here. Also available via the dbatools command
SQL Diagnostic Queries
has been writing and publishing diagnostic queries for SQL Server all the way back to version 2005. He has versions for each release of SQL, including Azure SQL and SQL Managed Instances, and updates the ones for versions that are still in mainstream support by Microsoft (2016 as of this writing). Download
here. You can also run these queries with the dbatools
Microsoft Tiger Team Toolbox
The SQL Server support team at Microsoft is called the Tiger Team. The tools and scripts they use daily to diagnose and solve customer problems are available at Tiger Team Github.
Adam Machanic wrote a stored procedure that is an improvement on the out-of-the-box sp_who2. My favorite feature is that the procedure provides an xml link that shows the query being run. Available
here. Also available via the dbatools
wrote sp_PressureDetector, sp_HumanEvents and sp_QuickieStore to help diagnose some of your common SQL Server problems. Available
here. You can also easily install these with the dbatools
SET STATISTICS IO ON. Very nice job of making the results human readable.
Check out the online tool
Frequently the best troubleshooting tool is a historical log of what happened. Without a monitoring solution in place, you have to wait for a problem to happen again and hope you catch it this time. It’s much easier to scroll back through a report and see what was going on at the time of the event. Monitoring also helps you see problems coming and gives you a chance to fix it before it gets too big.
has done an incredible job putting together a robust monitoring solution that is lightweight and scalable. He continues to make improvements and release new versions. There even a slick PowerBI dashboard to view everything with. Check it out at
sqlwatch.io. Can be installed with dbatools
Grafana / Telegraf/ InfluxDB
Tracy Boggiano (b|t) has put together a nice monitoring package together that makes use of several open source products. Check it out here.
When things aren’t breaking, you still have to keep the server running. Backups, patching, migrations etc. These tools help make that easier.
(b|t) If you’ve read any of my other posts, you’ve likely heard me praise dbatools. I use it, literally every work day. It is being continually updated and expanded by volunteers. There is also a slack channel where you can ask question and get help (or provide help if you’ve some knowledge to share.) The dbatools module version 1.1.7 was released 08/03/2021 and has 635 commands. (Now:2.0.3 released 05/22/2023 and has 678 commands.) Check out the website dbatools.io.
SQL Server Maintenance Solution
has put together a T-SQL only solution for backups, dbcc checks and index management. These scripts have become one of the most common ways to easily get a new server set up for backups quickly and easily. Simply set a few parameters like where to put the backup and how long to keep them, run the scripts and then schedule the jobs that have been created. Go take a look
here. You can also install it with dbatools using the
SQL Undercover Toolbox
David Fowler and Adrian Buckman (b|t) have put together a nice set of tools to help you manage you SQL Server instance. You can find out more here.
DBA Checks is a set of Pester tests that check your SQL Server for compliance against best practices. Another very active project from the SQL/PowerShell community. Find out more here.
has written some very helpful scripts to help with optimizing storage, on-the-fly documentation, general administrative needs, and more. Go take a look
here or install with the dbatools
Some of these tools are part of my daily routine. Others are what I turn to when there’s a problem. Still other’s I keep meaning to use and forget they are there. This list is partly for me to remember that last group and finally use them. I’m sure there are other tools that I didn’t mention here. Let me know in the comments below what your favorite tool is.
Thanks for reading.
2021-08-03 05:00 (Last updated: 2021-08-11 16:09)
Comments powered by Talkyard.