SQLStarters

I’m finally getting back to the “Tools of the Trade” series with a third post on some of my favorite (and free) SQL Server tools. This time on the spotlight is the Diagnostic Scripts by Glenn Berry. The scripts can be found from his blog, here. The whole package is 6 different T-SQL scripts, one for each version of SQL Server starting from 2005 and going to 2016. There’s also a blank Excel to go with each one.

Installation

There’s absolutely no need to install anything to your servers, which is definitely a bonus. All you need is Management Studio and Excel.

Usage

They can’t really get much simper to use. Basically all you need to do is to take the script, run it query by query and copy the results to accompanying Excel. While it might look scary at first as there are hundreds of lines of T-SQL jumping at you, just stay calm and start reading. There’s a huge number of queries to run, but once you’re done, you’ll have a very good picture of how your instances and databases are doing. They’re also quite well commented so you’ll know what sort of information you’ll be getting out from each one of the queries.

My favorite features

Personally I’ve used this after I’ve “inherited” administrative responsibilities of an environment I’m not familiar with. When I’m mapping a health of an instance I usually start by gathering items like SQL Server version, installation dates and hardware related information (Drives, memory usage and CPU). Then I’ll move into going through databases (options, file locations, VLFs), scheduled jobs, alerts, configuration values, trace flags and how many users there are connected and most importantly.. Backups!

Mapping your SQL Server instances is one way to use the scripts, but it has another use, as the name suggests: Diagnosis. If you have poorly behaving SQL Server instance I warmly recommend that you grab yourself the proper version of the Diagnostic Scripts and start troubleshooting the problem. You’ll be able to pull out lot of information about I/O, user connections, wait statistics and even CPU and memory usage. These scripts will also let you dig into database level information such as blocking and locks, missing indexes and poor indexes among plenty of other useful information.

There’s one thing I found very cool myself, while I prefer to monitor things like CPU usage from Performance Monitor the Diagnostic Scripts can give you accurate enough information about that as well. But there’s also a query that’ll tell you from which database the CPU usage is coming from. I think that was pretty damn impressive and something you can’t really do with Performance Monitor.