September 2011 SQL Server 2008 Instance Level Diagnostic Queries

Since it is late September, I thought it would be a good time to release an updated set of Diagnostic queries that focus completely on SQL Server instance level metrics. These queries go a little deeper into the Instance level metrics than the combined Diagnostic Information Queries.

My previous DMV Diagnostic Queries are split between a number of instance level queries in the first half of the script, and then a number of database specific queries in the second half of the script. This is problematic if you have a large number of user databases running on your instance, since you would end up running the instance level queries multiple times (if you decided to run the complete diagnostic set for each user database).

Another problem is that many people who ran the complete set of old diagnostic queries would forget to change their database context to a specific database for the database specific queries, leaving it pointing at the master database. This meant that they would get a lot of pretty useless information about the master database.

This new set of Instance Level Diagnostic Queries is designed to be run first when you are investigating the configuration and performance of a SQL Server instance. Then, guided by the results of these queries, you will be able to run an upcoming set of separate Database specific diagnostic queries against the most interesting databases on the instance.

Many of these queries are DMV queries that require VIEW SERVER STATE permission in order to run. After all of this introduction, the September 2011 SQL Server 2008 Instance Level Diagnostic Queries are available to download from DropBox here.

The idea here is that you should read the comments for each query, then run each query one at a time, and look at the results as you look at my comments on how to interpret the results.

This entry was posted in SQL Server 2008, SQL Server 2008 R2 and tagged , . Bookmark the permalink.

6 Responses to September 2011 SQL Server 2008 Instance Level Diagnostic Queries

  1. Steven Wang says:

    Hi Glenn,

    This is great. Thanks for all your work to put this together.

    I just bought your book “SQL Server Hardware” and I have to say I have never felt so delighted to read a hardware book like yours. I just couldn’t stop myself to read until the last page. Thanks again and you made me started to like hardware.

    Steven

  2. Miguel Palacios says:

    Hello Glen, I was swimming on a sea of scripts that I get from blogs or my own to find out issues on my SQL Server instances, and since I have been using your work on my day routine it became more efficient and enjoyable.

    thanks.

  3. Pingback: Something for the Weekend – SQL Server Links 30/09/11

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