Free Tools Recommendations from Leaders in the SQL Server Community
This fall at the Dev/IT Connections Conference in Las Vegas I moderated a panel of speakers taking part in the SQL Server track and the question arose around our favorite free tools for SQL Server. Since you all could not be at the conference it seemed like a valuable topic to share here. Many of us shared the same favorites or had those “Ah Ha!” moments when someone brought up a great free application or set of scripts. We didn't take good notes during the panel discussion so I reached out to the panelists after the fact via email and included their responses here.
I’ll take advantage of my position as the author here to include a few more along the way that didn’t end up getting mentioned that I think are also worth a look… because I can.
On Just About Everyone's List
The tools that seemed to be on the lists of those specializing in database administration were from Adam Machanic (sp_whoisactive), Ola Hallengren (SQL Server Maintenance Solution) and SQL Sentry (SQL Server Plan Explorer):
Argenis Fernandez (blog | twitter)
"sp_whoisactive is the one that I use the most and I would also mention Adam's series on monitoring; Ola's is the other one, of course."
Andrew Kelly (blog | twitter)
"I too think sp_whoisactive is the most useful free tool and as Argenis mentioned his blog series is fantastic to learn how to use the tool under different scenarios."
Tim Ford (blog | twitter)
"I use Ola's scripts every day (in addition to citing myself in the third-person.) Any SQL Server I've deployed in the past three years bases all of its backup, DBCC CHECKDB and indexing strategies and processes off of this package of scripts. I love the fact that I can mine metadata about these processes from a centralized table that I can the incorporate into troubleshooting, altering and historical reporting for indexing enhancements - specifically adjusting fill factor based upon index fragmentation frequency. As a champion of the Dynamic Management Objects I also can't speak highly enough about Adam Machanic's sp_whoisactive script as well - which leverages the DMOs quite heavily.
Many SQL MVPs that are responsible for performance tuning use either the Pro or the Free version of SQL Sentry's Plan Explorer product. The functionality and graphical improvements for analyzing query plans is leaps ahead of the graphical execution plan experience inside of SQL Server Management Studio. As powerful as the free version is I'm honestly surprised that it's still free. It's quite a gift to the SQL Server Community."
Additional DBA-Centric Free Tools
Grant Fritchey also reminded us that Ola Hallengren has competition on the index maintenance free scripting front, in particular from Michelle Ufford (download her Index Defrag Script here.) I can attest that her scripts definitely do the trick having used them over the years before moving to Ola’s when incorporating backups and DBCC CHECKDB when I backed off of the “rolling my own solution for maintenance” roller coaster. Grant, always the master of getting right to the point commented:
Grant Fritchey (blog | twitter)
"Michelle Ufford’s scripts."
To be fair Grant also suggested that there were a variety of free tools that his employer, Red Gate Software, provides to the SQL Community as well but he didn't want to throw bias into the mix. I'll happily do so though as I do believe that my first foray into the "Free SQL Tools" environment came from use of their SQL Prettifier that was around back in the early stages of my accidental DBA days and is still around to this day. Red Gate has quite an assortment of free tools for the DBA and developer alike. They can all be accessed here.
One tool that came up that I was unaware of on the DBA side of SQL was this suggestion from Jes Borland
Jes Borland (blog | twitter)
"I didn't specifically mention this on stage, but I love Richie Rump's Statistics Parser."
Allen White, ever the lover of Powershell of course brought that platform into play with his suggestion for Kendal Vandyke’s SQL PowerDoc:
Allen White (blog | twitter)
"Kendal Vandyke's SQL PowerDoc allows you to run PowerShell against any and all of the SQL instances in your environment and output diagnostic results to Microsoft Excel for analysis. I use this all the time when examining new environments or measuring current state to baselines."
David Klee joined us this year at DEV/IT Connections and if you’re not aware of the name you should be - particularly if you’re virtualizing your SQL environments using VMWare. Aside from being a voice of authority in the SQL Server space when it comes to storage and performance; he’s also a VMWare vEXPERT. He’ll be joining many of the people cited in this article aboard the Caribbean SQL Cruise in February as well. David takes the angle of discussing a free tool hooked into analyzing IO:
David Klee (blog | twitter)
"My favorite tool is the SQLIO Batch PoSH script by Niels Grove-Rasmussen, available here. It wraps Microsoft's free SQLIO tool and makes it usable for a variable load test on storage, very applicable in my world."
Business Intelligence-Related Free Tools Options
It’s convenient being in charge of selecting the speakers for the Connections Conferences’ SQL Server track because I can ensure that all aspects of SQL Server are covered. While I’m familiar with most free tools offerings on the DBA and Developer side of the equation I don’t have that familiarity in the BI and BA space. Thankfully we had representation in the panel for those topics from Rafael Salas and Tim Mitchell.
Rafael Salas (blog | twitter)
"There is a great BI tool called sp_ssiscatalog form Jamie Thomson - for SSIS 2012 and above in project deployment model"
Tim Mitchell (blog | twitter)
"My single favorite free tool is BIDS Helper which is available through Codeplex: https://bidshelper.codeplex.com/"
The Free Gifts That Keep on Giving
I think it's absolutely fantastic that so many share their knowledge with this community and that so many companies provide free tools with no strings attached. It's one of the things that sets the #SQLFamily appart from other technical groups.
In the United States we're about to celebrate Thanksgiving - a day set aside to give thanks for what we've been given and have achieved in life. I'd like to say that among other things I'm thankful for all those who take time out of their lives to share with their peers. You elevate us all with your contributions. No matter where you are when you're reading this and whether you celebrate Thanksgiving in your area of the world or not I hope you too have much to be thankful for this year and all the years to come.
