Top 10 Free SQL Server Tools

Top 10 Free SQL Server Tools 2012

I know you all love talking about your favorite tools and maintenance scripts. Whether your pleasure is problem diagnosis, query tuning, index analysis or performance monitoring. These sql server tools are all available absolutely free and with the common goal of making your life easier.

In no particular order of awesomeness (they are all fantastic in their own individual way) here are my top 10 free SQL Server tools.

My Top 10 Free SQL Server Tools

1. Plan Explorer

If you find yourself having to look at SQL Server execution plans (and you should be) this is THE tool for you. SQL Sentry Plan Explorer builds upon the graphical execution plan view in SSMS to make working with query plans a more efficient and “pleasurable” experience. No more scrolling through massive plans and hunting through property windows to find the metrics you need.

2. sp_Blitz

Like Batman’s utility belt, this tool does pretty much everything. If you’ve been handed a SQL Server instance and need to give it the once over in order to get a handle on things then this is what you use to get the job done. sp_Blitz helps you rapidly assess configuration, security, health, and performance issues. As if that were not awesome enough, the results are organised in a prioritized list and provide simple explanations of each risk, plus a column of links that you can copy/paste into your browser for more information about each issue. The authors, Brent Ozar (Blog|Twitter) and his team, have even put together a tutorial video on how this tool works. A wonderful example of #sqlWinning and most certainly worthy of a hat tip.

3. sp_WhoIsActive

Want to know what is happening on your SQL Server instance right now? This is the tool you use. It’s my favourite initial diagnosis instrument. When I’ve got a production issue on my hands and nothing more to go on than “is there something wrong with SQL Server?” this is my go-to weapon. The creation of Adam Machanic (Blog|Twitter), sp_WhoIsActive enables me to triage an instance of SQL Server so that I can immediately get a feel for what’s currently happening and quickly decide what my next steps should be. As well as all currently executing commands and what they’re waiting on, I can optionally pull back additional information like the execution plan and blocking chain.

4. Management Data Warehouse (MDW)

Yet another fantastic product feature available right out of the box that I’m surprised more folks are not taking advantage of. It contains a significant amount of performance monitoring insight and capability that many vendors will happily charge you for. Using the MDW I have been able to make production performance insight data directly available to development teams by extending the default implementation using SQL Reporting Services.

5. Index Defrag Script v4.1 

Everyone has their favourite Index Defrag script. Some folks (suckers) like to author their own index maintenance scripts and everyone else likes to use Ola Hellengren’s ;-) Of course not being one to follow the crowd, this little beauty written by Michelle Ufford(Blog|Twitter) is my own personal favourite. I’ve been very happily using this script for index maintenance of production environments for many years and many versions. It gets the job done extremely well and deservers more press.

6. sp_BlitzIndex

It’s natural to think that your indexes improve your performance. Sure that’s what they’re supposed to do sure but there are cases where having indexes can actually be a hindrance to performance. Things like duplicate and unused indexes can be silent killers, dragging your performance down quietly in the background. You can use sp_BlitzIndex to evaluate your overall index health and to identify areas where you can make adjustment and improvement to nip potential future problems in the bud.

7. Compression Estimator

SQL Server Compression is one of the more underused features of the product, most likely because a lot of folks don’t know how to use it to their gain. You can download this utility from Codeplex and use it in order to identify how much space you could save, as well as evaluate which type of compression you should consider (ROW or PAGE). Armed with this type of information, you can make a stronger case to both management and your customers as to why you should be taking advantage of the SQL Compression feature.

8. Central Management Server

Managing a large number of SQL instances can be tricky. CMS is a SQL Server feature for making the job of administering multiple servers easier. You can centralise your registered server lists, ensuring your entire team is working from the same reference and run multiple queries against “groups” of servers using a single query. It’s a great feature and I encourage you to learn more about how it can simplify the administration of your environment.

9. ClearTrace

Here’s a great tool for working with SQL Server trace files. ClearTrace imports SQL Server 2000, SQL Server 2005 and SQL Server 2008 trace and profiler files into SQL Server and displays summary performance information. It’s easy to use and helps you get to the bottom of performance problems fast.

10 . SQL Server 2012 Diagnostic Information Queries

SQL Server DMVs were are godsend when they were introduced into the SQL Server product. Just ask anyone who still has to touch a SQL 2000 instance how much fun they are having. DMV’s are of course not pure magic by themselves, they need to be built and combined together into useful T-SQL queries. Thanks to Glenn Berry (Blog|Twitter), the hard work has already been done for you. Glenn has put together a wide variety of awesome T-SQL queries that you can use in order to diagnose the health and performance of your SQL Server instances.

An excellent selection of sql server tools I’m sure you’ll agree and this is a list of only 10. There are so many more fantastic tools and scripts freely available courtesy of the SQL community.  Share your favourites and see what others are sharing in the community forum.

  • http://www.sqlblog.lv Jānis

    For “7. Compression Estimator” i would prefer using script.
    Have wrote one a while ago-
    – no installation needed
    – shows space savings in MB (approximately)
    – shows % data is IN_ROW (The data stored in LOB_DATA and ROW_OVERFLOW_DATA allocation units is not compressed)
    – can copy/paste in Excel
    There are two “-”
    – I do not generate scripts
    – Blog is not in English
    So, if you want to have a look- http://www.sqlblog.lv/2012/07/datu-kompresesana-parskats-par-visam-db.html

    • http://www.johnsansom.com John Sansom

      Hi Jānis,

      Absolutely! You can certainly roll your own script to evaluate the possible use of SQL Server compression. You always want to use the approach that works best for you/your company.

    • Allen McGuire

      Janis – I think if you are a DBA, you probably have some items installed on your laptop/workstation so that shouldn’t really be an issue for most. Further, Compression Estimator gives you more data that you probably need, and what you don’t get you can put it into Excel and manipulate it as you see fit. I prefer to not reinvent the wheel.

  • Paul Brewer

    PAL would make my top 10 list of free tools, the predefined templates are great.
    Regards

    • http://www.johnsansom.com John Sansom

      Yes, that’s a great suggestion Paul.

  • http://dba.jsauni.com Jacob Sauni

    I’ve always found Redgate’s SQL Search handy when looking for objects I have no idea about or aren’t documented any where.

    • http://www.johnsansom.com John Sansom

      I’ve not used RedGate’s SQL Search, I’ll be sure to investigate.

      Thanks for the suggestion Jacob.

    • Mark

      SQL Search is awesome tool (used to run my own script)…very handy to have right in SSMS….use it daily!!!

  • http://sqlchicken.com Jorge Segarra

    Great list but I’d argue putting Ola’s Solution in top 10. Also, and I’m biased on this one obviously, that EPM Framework (and Policy-Based Management in general) is awesome (http://epmframework.codeplex.com).

    • http://www.johnsansom.com John Sansom

      I’m also a big fan of PBM, so that’s a good shout sir. The EPM Framework looks very impressive too so I’ll be looking into that in more detail. Thanks for sharing it with us!

  • http://sqlchicken.com Jorge Segarra

    Oh and Mladen’s SSMS Tools Pack is CLUTCH free tool, http://ssmstoolspack.com

    • http://www.johnsansom.com John Sansom

      Free has got mean completely free in my book. Yes it is without a doubt a fantastic tool (and a regular mention on my previous top tool lists) but alas it is not free for all versions of SQL Server.

  • Knut Snalgord

    Ran across this on a google search; I’m going to have to try a few of these, thanks for posting! FYI, I throw in a 2nd hat for RedGate SQL Search – extremely useful

    • http://www.johnsansom.com John Sansom

      Knut, you’re welcome sir, glad you enjoyed the post.

      Be sure to let us all know how you get on with these tools in the community forum.

  • Jason Washer

    Best free SQL Server backup tool I’ve seen is called SQL Backup Master:

    http://www.sqlbackupmaster.com/

    • http://www.johnsansom.com John Sansom

      Jason, I’d be very interested to hear about your experiences with that tool sir.

  • http://hal2020.com Hal

    Anyone using GreenSQL? I’ve been interested in it for a long time but haven’t taken a serious look at it.

    • http://www.johnsansom.com John Sansom

      Hal, I can’t say that I have sir.

  • http://Usajobs.gov David Washington

    I love all of these tools and really appreciate the time and effort of each one of the developers that allow us to use their products. However, there is one tool that I use all the time that I see didn’t make it and that is SQLCop. SQLCop by Lessthandotnet is awesome! Check it out…..

    • http://www.johnsansom.com John Sansom

      David, thanks for sharing SQLCop with us sir.

      As someone with experience having used the tool it it would be great if you could share your thoughts and experiences with SQLCop in the SQLBrit community forum. I know there will be other readers who would be interested to know more.

  • Kevin Boles

    I think leaving off Ola.Hallengren.com’s AWESOME, FULLY DOCUMENTED, INCREDIBLY FLEXIBLE maintenance suite is a travesty! :-) Oh, and DBSophic’s Qure product is light-years ahead of ClearTrace. And how about SQLJobVis for a Gantt Chart of SQL Agent execution history, which allows you to easily detect job overlaps (which I see at virtually every client I visit).

    • http://www.johnsansom.com John Sansom

      Kevin, what can I say my good sir, I do like a bit of controversy every now and then. Technically I have in fact mentioned Ola’s excellent tool :-) and it’s not like it needs more press, where as there are community members who I do think deserve to be in the spotlight (no tool pun intended) more.

      Funnily enough I’m about to put SQLJobVis to work this coming week, a most excellent recommendation.

      Thanks for your comments.

  • http://Www.paulbrewer.com Paul Brewer

    The Microsoft MAP tool is handy for creating an inventory of servers in development/test environments that have run away with themselves. Most places know exactly how the live environment is setup but less so for dev/UAT ETC.

  • Michael Robson

    would be interested to know your thoughts on this tool, http://www.enterprisedba.com, features I particularly like are quick visibility into any potential problems, the eMail alerts are reliable and I like the DB size reports

  • csm

    I discovered couple of months ago a fantastic tool to analyze workloads, QureAnalyzer from DBSophic. In the past, I usually use ReadTrace from RMLTools, but I think is powerful and easily to use QureAnalyzer.

    • http://dirkhondong.wordpress.com Dirk Hondong

      Just a little bit too late.
      QureAnalyzer is the 2nd tool I use beside ClearTrace.
      I like the option that I either save the trace data into a database or in a “project file”.
      So I can just put it on a USB stick or something else to review the results offline.

      And still an “old-school” tool: SQLNexus which depends on the RML utilities.

      • http://www.sqlblog.lv Jānis

        So, why you use both? Mostly it seems Qure Analyzer does the same as ClearTrace, just interface is better..

  • Alexey

    SQLBackupAndFTP is a nice simple UI tool that runs scheduled backups (full, differential or transaction log) of SQL Server or SQL Server Express databases and save them on folders / NAS / FTP / Dropbox / Google Drive / Amazon S3.

    http://sqlbackupandftp.com/

  • http://www.insidesql.org/blogs/tosc/ tosc

    Hi John,

    what about Ola Hallengren’s SQL Server Backup, Integrity Check, and Index and Statistics Maintenance Solution http://ola.hallengren.com/

    • http://www.johnsansom.com John Sansom

      Indeed, another excellent set of scripts. Thanks for sharing.

  • http://www.MedicalBiller-pr.com George

    I opened this article with enthusiasm hoping to find an Sql Compare type of utility. One that would compare the same table in 2 databases, show their structural differences (not data differences). Anybody knows of the existence of such an animal?

  • http://www.sqlservercentral.com Steve Jones

    One plug for my company, REd Gate: We have a free search add-in

    http://www.red-gate.com/products/sql-development/sql-search/

  • Alex Maslyukov

    Hi,
    consider SQL Hunting Dog from http://www.sql-hunting-dog.com – it is a quick search FREE addin for SQL Server Management Studio 2008 and 2012.

  • Kin (@TheRockStarDBA)

    Hello John,

    Really appreciate your efforts for the community.

    I would suggest to include 2 tools from Codeplex that are very valuable for any DBA :

    Scripted PowerShell Automated Deployment Engine (SPADE) for SQL Server (http://sqlspade.codeplex.com/) –> this will automate SQL Server deployment.

    SQL Power Doc (http://sqlpowerdoc.codeplex.com/) by kendalvandyke. Very useful for documenting and health checking of SQL Server.

    Thanks,
    Kin

    • http://www.johnsansom.com John Sansom

      Kin – Thanks for your suggestions sir. I’m not familiar with those tools but I’ll be sure to take a look.

  • http://twitter.com/SQLTrooper Allen McGuire (@SQLTrooper)

    Our lists overlap a bit, but perhaps you’ll find a few new ones in my toolbox! ScroptSQLConfig is a pretty important one, as is SQLIO. APEX Refactor is a great alternative to SQL Prompt.

    http://allen-mcguire.blogspot.com/2013/08/my-dba-toolbox.html

  • David Atkinson

    A hidden gem is SQL Code Guard, a nifty (and entirely free) T-SQL static analysis tool that is implemented as an SSMS add-in: http://sqlcodeguard.com/

    • http://www.johnsansom.com John Sansom

      David – Thanks for the suggestion. What do you like most about the tool?

      • David Atkinson

        It’s the simplicity. You select a database in the Object Explorer and click Process database. This returns a huge list of code smells. Double click on these and it scripts out the object to a query window, allow you to ‘correct’ the issue.

  • http://www.sqlcopilot.com Richard Fryar

    I hope you don’t mind me mentioning my own tool. The free version of SQL CoPilot has been downloaded a couple of thousand times over the past year.

    It doesn’t have all the functionality of the full version, but it’s still a quick and easy way of seeing what state your SQL Server is in without the need to run any scripts. http://www.sqlcopilot.com

  • Chad Masti

    SQL CoPilot is a winner, hands-down. Especially if your instance has been running for a while (days/weeks), you will get extremely valuable information about where your problems most likely are coming from, within minutes of looking at the “big picture” and then following simple DBA common sense through the CoPilot’s reports from thereon. Best part is, its color-coded SSMS reports alert you to where the metrics seem to be off from “expected” normal values. We all know many DMV queries that we can issue and gather information, but analysis of results and making inferences takes time, but having SQL CoPilot pull this for you, and give you ready visual cues is a great time-saver and productivity enhancer. Further manual investigation and DBA deep-dive instigation can always follow, but the “quick-win” capability of this tool is just too good to overlook and deserves a worthy commendation.

    • http://www.johnsansom.com John Sansom

      Thanks for sharing your experience Chad.

  • Francis

    My favorite free tool is SQLRanger at http://www.sqlranger.com. It’s simple and provides exactly the information you need, without overload. It gets almost no press and is in my opinion highly usable. The developer is extremely open to suggestions and typically will implement something you ask for quickly. Sometimes even the same day! That’s not something you get with the “big name” tools.

  • Nigel Harris

    I used to use Plan Explorer for execution plans but then came across Supratimas (http://www.supratimas.com/) which you can use in the browser or (which I prefer) as a plug-in. As well as guiding you to the key parts of the execution plan it allows you to move the plan into its own workspace making “before and after” comparisons very easy.

    Worth a look!

  • Allen McGuire

    I have every free tool/utility/script (but one I think) in my DBA Toolbox so it’s good to see we’re on the same page! You could probably expand it to a Top 20 these days, however. There are free tools from APEX – the Refactor one might save you money if you currently use Red Gate’s SQL Prompt, for example. The Ozar team has since put out three additional scripts since sp_Blitz: sp_BlitzCache, sp_BlitzIndex and sp_AskBrent. I also think Ola’s backup/maintenance scripts are one of the top in the industry and widely used in the SQL Server community.

    You can find all these and more on my blog post – hope you find it helpful!
    http://allen-mcguire.blogspot.com/2013/08/my-dba-toolbox.html