Welcome to the fourth in the series of posts about how to maintain your larger databases within SQL Server better. I decided to cover free tools because in general people prefer things that are free.
In case you have missed any of my other posts in this series you can click on the links for them below:
I specifically wrote these posts to help you avoid issues and free up your time to focus on other things.
Nowadays there are a lot of free tools available you can use to help maintain your databases better. As promised in part three, and of course to help you potentially narrow down the list, this post will cover my personal recommendations.
I’ll start with discusing the ones that I have used and finish with a few honourable mentions to others.
Glenn Berry’s diagnostic scripts
For those of you who don’t know, Glenn provides free diagnostic scripts that you can use within your SQL Servers. In fact, within these scripts contains the missing index script that I prefer to use.
They contain useful scripts to help you find server and database information. In addition, he updates them on a regular basis. You can see the ones for November here.
In fact, I have used these scripts a lot to help me get server information. For example, I’ve often used the missing indexes script that uses index advantage for many years.
Glenn provides scripts you can use with various versions of SQL Server. In addition, these scripts also contain useful comments.
Brent Ozar’s First Responder Toolkit
Here’s another free tool which comes in very handy, which you can download here.
Generally speaking, the headline tool that comes with this is sp_blitz. It can be very useful if you have just inherited a server and want to see what potential issues there are with it. For instance, it identifies the issue I discussed in my previous post about untrusted foreign keys.
In addition, there are other useful things in this toolkit. For instance, one of them is sp_blitzindex which I do find useful now and again. Because next to the identification of potential missing indexes it also identifies inefficient heaps.
In other words, I mean reasonably large heaps which contain spaces where rows have been deleted or moved elsewhere. For myself, it saves me having to dig into my old black SQL internals book to figure out a good script for it.
Ola Hallengren’ s indexing solution
Now, I talked about Ola’s solution in part two of this series. In fact, over the years I have used others and this is by far the best for me.
You can fine tune it better than most alternatives, which I discussed previously in part two. Plus, you can output logs in a variety of ways.
In addition, you can also specify it to only filter indexes in a particular schema as well, which I have used in the past.
You can find out more about it in detail here.
Now, I’ve used this a few times on instances with larger databases. Generally speaking, it does have it’s uses.
For instance, you can use it with the Power BI dashboard that comes with it to look at multiple servers. Using it can help you identify items which were not configured correctly.
Subsequently, I have used it with larger databases. In fact, once I ran it with the defaults against an instance that contained a 7TB database. It took a bit longer than usual, but it worked.
You can find out more about DBAChecks in detail here.
SentryOne Plan Explorer
This free app provided by SentryOne is great if you ever have to look at execution plans against large databases. In addition, it’s also useful for plans for smaller databases.
In fact, I prefer using this tool instead using SSMS to navigate around a large execution plan. Furthermore, I have encouraged a client to start deploying this on their large estate where possible.
However, bear in mind that the latest version is 64 bit only. Therefore, if you’re working on a 32 bit OS you can either upgrade or look to use elsewhere.
You can find out more about SentryOne Plan Explorer in detail here.
In addition, there are a few others which I have looked at briefly. However, I have not used them that often for various reasons. They are as below, and you can click on the links in the heading to read more about them in detail.
Originally created by Marcin Gminski, this evolving Performance Monitor is definitely one to try out. Especially since it is now used in conjunction with things like dbatools.
Open Query Store (https://www.openquerystore.com/)
Created by William Durkin and Enrico van de Laar. It’s an open source tool that you can use as an alternative to Query Store.
Initially aimed at versions of SQL Server before SQL Server 2016, which is when Query Store was first introduced.
Database Health Monitor (http://databasehealth.com/about/steve-stedman/)
Created by Steve Stedman, which I only found out about recently. However, I have dabbled with it and found it to be a useful app.
Now a bit of advice. Some free scripts you can get to use within SQL Server usually require them to put stuff in the Master database. However, some corporations are not allowing this for easier database migrations.
Therefore, a simple workaround for this is that you can deploy the script into another database. However, in some cases, like alternatives to sp_helpindex, you might have to deploy them into every database that you wish to use them.
Well I hope you found my recommendations useful. Like I said before, at the end of the day I posted these to make peoples life’s easier.
Of course, if you know any other free tools available for SQL Server that are worth mentioning feel free to comment.