Welcome to the fourth in the series of posts about how to maintain your larger databases better. In case you have missed any of my other posts in this series, the links for them are below:
- Maintaining your larger databases better Part 1
- Maintaining your larger databases better Part 2 – Indexes
- Maintaining your larger databases better Part 3 – Recommended DMV’s
I 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 3 and to help you to short this endless list this post will cover my personal recommendations.
I’ll start with discusing the ones that I have used and end 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 for use against your SQL Server databases. Within these is the missing index script that I prefer to use.
They contain useful scripts to find server and database information. In addition, he updates them on a regular basis. You can see the ones for November here.
I have used these scripts a lot to help me get server information. Actually, I’ve been a big fan of the missing indexes script that uses index advantage for many years.
Glenn provides scripts to 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.
The headline tool that comes with this is sp_blitz. It’s very useful if you have just inherited a database and want to see what potential issues there are with it. For instance, it reports the issue I discused in my previous postabout 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. 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
I talked about Ola’s solution in part 2 of this series. Over the years I have used others and this is by far the best for me.
You can fine tune it better than with most alternatives as I discussed in part 2. 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.
For more detailed guidelines for all the parameters click here.
Now I’ve used this a few times on instances with larger databases. It does have it’s uses. Especially if you are using the Power BI dashboard that comes with it to look at multiple servers. Using it can can help you identify items which were not configured correctly.
Subsequently, I have used it with larger databases. Once I ran it with the defaults against an instance with a 7TB database on there. It took a bit longer than usual, but it worked.
You can find out more about DBAChecks 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.
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.
But, bear in mind that the latest version is 64 bit only. Therefore, if you’re working on a 32 bit OS you will have to either upgrade or look to use elsewhere.
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.
Originally created by Marcin Gminski, this evolving Performance Monitor is definitely one to try out.
Open Query Store (https://www.openquerystore.com/)
Created by William Durkin and Enrico van de Laar. It’s an open source tool 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 introduced.
Database Health Monitor (http://databasehealth.com/about/steve-stedman/)
Created by Steve Stedman. I admit I only found out about this recently. However, I have dabbled with it and found it is a useful app.
Now a bit of advice. Some free scripts you can get to use within SQL Server usually require them to be in the Master database. However, some corporations are not allowing this for easier migrations.
Therefore, a simple workaround for this is to either deploy the script into another database. However, in some cases, like alternatives to sp_helpindex, you might have to deploy them into every database if you wish to use them.
Well I hope you found my recommendations useful. Like I said at the end of the day I posted these to make peoples life’s easier.
If you know any other free tools available for SQL Server that worth mentioning feel free to comment.