When I first started working with SQL Server early in the year 2000, the SQL Server community was not like it is today. There were not all the conferences, blogs, monitoring solutions, and training. Compared to how things are now, you kind of felt like you were on an island all alone, unless you had a good mentor. I was fortunate because I had a good mentor, Russell Fields (MVP), who helped me along the way. But, there were so many things you had figure out and create for yourself. For instance, because Microsoft’s maintenance plans have always left a lot to be desired … 🙂 … many people were creating their own systems for doing database maintenance. I created a database driven maintenance system, which included schedulers, exclusions, backups, intelligent optimizations, integrity checks, and history logging.
However, now is a different time, when we don’t have to “re-invent the wheel” or create everything from scratch. There are a lot of useful tools in the SQL community that can make our lives much easier, that we don’t have to custom build.
Useful Free Tools
Here’s a list of some of my favorites:
- Ola Hallengren’s SQL Server Maintenance Solution (http://ola.hallengren.com) – The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. The solution is based on stored procedures, functions, the sqlcmd utility, and SQL Server Agent jobs. This is almost an absolute must to use! In fact, using this solution made me stop developing on my own maintenance system.
- Adam Mechanic’s sp_whoisactive (http://sqlblog.com/tags/Who+is+Active/default.aspx) – This extremely useful stored procedure is a replacement for sp_who and sp_who2 that actually shows you the queries and execution plans, and it is well documented on that site. Also, there are a lot of parameters that makes sp_whoisactive really useful.
- Brent Ozar’s sp_blitz (http://www.brentozar.com/blitz/) – This is a really handy stored procedure to do fast health checks on a SQL Server. It looks at things such as configuration, security, health, and performance issues and even prioritizes the list.
- Glen Berry’s Diagnostic Information Queries (http://sqlserverperformance.wordpress.com/) –This script, which is based primarily on DMVs, gives you a ton of information on your SQL Server. It can also be very useful in understanding what certain DMVs are useful for.
Now, although I refer to them as free tools, make sure you read the license. In some instances, if you are planning on making money off of them, they may not be free in that case.
Each of the tools I listed has helped me, and I actively use them in my environment. However, as a rule, whenever you are using a new tool or script, always test it out in your test environment. Then, once you are comfortable with it, run it in production.
So, don’t re-invent the wheel! But rather, take advantage of some of the useful free tools provided by some of the experts in the SQL community.