Since it is August 1, 2011 (which is also my birthday), I thought it would be a good time to unveil a completely new set of Diagnostic queries that focus completely on SQL Server instance level metrics.

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 August 2011 SQL Server 2008 Instance Level Diagnostic Queries are available to download from DropBox here.  I also have a matching, blank Excel spreadsheet, with labeled tabs available from DropBox here.

The idea here is that you should read the comments fir 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. You can also paste the results from the results grid in SSMS into the matching tab in the spreadsheet, so you can keep them for baseline purposes.

Please let me know what you think of this new set of queries. Thanks!