August 2011 SQL Server 2008 Instance Level Diagnostic Queries

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 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. 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!

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

11 Responses to August 2011 SQL Server 2008 Instance Level Diagnostic Queries

  1. Thanks, Glenn. Your diagnostic queries are great and remain one of the 428 reasons I read your blog. Keep up the awesome work! Oh, nice book by the way.



  2. Greg Milner says:

    It is hard for me to express how grateful I am for these queries and how much they have helped me with my job. Thanks! Great work.

  3. ricky lively says:

    happy b’day!

  4. Cameron says:

    Happy Birthday Glenn. Enjoy reading your blog.

  5. DaniSQL says:

    Happy (Late) Birth Day!

  6. Clark Cruz says:

    Thanks for the queries! I just found an instance where I did not have the configuration for optimize for ad hoc workloads. This helped me fix that. SELECT @Glenn AS AwesomeDBA

  7. Jason Crider says:

    Happy belated Birthday!
    Awesome tools. I had used a previous copy of the code that included a section for “Possible Bad Indexes (writes > reads). I was just curious why that wasn’t in this script.


  8. Pingback: Presentation & links from my #BIWUG session | Thomas Vochten

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s