Review: Diagnostic Scripts by Glenn Berry

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.

Author: Mika Sutinen

Hi, My name is Mika Sutinen and I'm a Senior Database Administrator for a company called Tieto. I've been working in IT-industry for two decades and I've spend most of my career working with healthcare information systems. I've worked with SQL Server for most of my career, starting with version 6.5 a long, long time ago. My other interests are high availability, everything related to performance (testing, monitoring, etc), Windows operating systems and I'm currently learning more about Azure.

One thought on “Review: Diagnostic Scripts by Glenn Berry”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s