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.
