Maintaining your larger databases better Part 4 – Recommended FREE tools for the job

Reading Time: 4 minutes

Introduction

In this post I cover part 4 of a series about maintaining your larger databases better if you use SQL Server. It covers personally recommended free tools you can use.

I decided to cover free tools because in general people prefer things that are free. With this in mind, I wanted to recommend ones I have used myself over the years.

In case you have missed any of my other posts in this series you can click on the links for them below:

• Maintaining your larger databases better Part 1
• Part 2 – Indexes
• Part 3 – Recommended DMV’s

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 that you can use to help maintain your databases better. With this in mind, this post will cover my personal recommendations.

I’ll start with discussing the ones that I have used and will finish with a few honourable mentions to others as well.

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 indexex 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 find the latest versions 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. In fact, these scripts have helped me identify other performance issues as well.

Glenn provides scripts for various versions of SQL Server databases, including ones in Azure. 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. Which can be very useful for you. Especially 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, another useful item 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. In reality, 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 which covered indexes. 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, something which I discussed previously in part two. Plus, you can also output the logs in a variety of ways.

In addition, you can also specify it to only filter indexes in a particular schema as well. Something else I have also done in the past.

You can find out more about it in detail here.

dbachecks

Now, I’ve used dbachecksa few times on instances with larger databases that are terabytes in size. Generally speaking, it does have it’s uses. You use it along with the dbatools PowerShell module. In fact, dbatools works well with other free tools mentioned in this post.

In addition, you can also use a Power BI dashboard that comes with dbachecks to look at multiple servers. Using this dashboard can help you quickly identify options which are not configured correctly on SQL Servers.

Subsequently, I have used it with larger databases. In fact, once I ran it with the defaults against an instance that contained a seven terabyte database. It took a bit longer than usual. However, it did work.

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 of the plans created within SQL Server Management Studio 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.

Another interesting thing to note is that there is now a Plan Explorer extension that you can use with Azure Data Studio. You can read more about that in detail here.

Honourable mentions

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.

SQLWatch (https://sqlwatch.io/)

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.

Advice

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 enterprise environments are not allowing this. Because keeping stuff in user databases makes migrations easier.

Therefore, a simple workaround for this is that you can deploy the script into another database instead. However, in some cases, like alternatives to sp_helpindex, you might have to deploy them into every database that you wish to use them.

Final word

I hope you enjoyed part four in the series about maintaining your larger databases better.

In addition, 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 of other free tools available for SQL Server that are worth mentioning feel free to comment.