Jan 15, 2013

Top 10 Free SQL Server Tools



Top 10 Free SQL Server Tools 2012 What better way to kick off a new year than with a look back at some of the top free SQL Server tools of 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 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 of 2012.

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 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 in the community forum.