September 2011 SQL Server 2008 Diagnostic Queries

Its been a couple of months since I have released an updated version of my SQL Server 2008 Diagnostic Information Queries, so I thought it was about time for a new version.

The first half of this set of queries is focused on the hardware and SQL Server Instance, so it does not matter which database you are connected to when you run them. The second half of the set is database specific, so you want to be connected to the database you are interested in, rather than the Master database. Many of these queries are DMV queries, which require VIEW SERVER STATE permission.

I recommend that you run each query individually, after reading the instructions and interpretation comments for that query. Personally, I like to paste the results for each query into a separate tab in a spreadsheet so I can keep them for baseline purposes.

I also strongly recommend that you look at all of the results, and consider everything you see before you make any rash decisions. For example if you see indexes show up in the “missing indexes” query, don’t go wild and add every index that shows up. A big part of being a wise DBA is to look at the entire picture, and use your judgment, experience and common sense as you interpret the results of these queries, and decide what (if anything) to do about them.

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 Diagnostic Queries

  1. Chris Howarth says:

    Hi Glen

    Thanks for the latest version of your diagnostic queries – I’ve been following their development for some time now and they have proven to be extremely useful.

    During a run of troubleshooting that I performed earlier this week I have noticed that one of the queries deviates slightly from the way in which BOL suggests that a join condition should be written:

    …the deviation can be seen on lines 601-602 of the September 2011 version of your script:

    INNER JOIN sys.partitions AS p WITH (NOLOCK)
    ON a.container_id = p.hobt_id

    BOL indicates that the column on which a.container_id should join is dependent on the value in the sys.partitions.type column:


    “ID of the storage container associated with the allocation unit.
    If type = 1 or 3, container_id = sys.partitions.hobt_id.
    If type is 2, then container_id = sys.partitions.partition_id.”

    I just thought I’d raise this in case you wanted to modify the query for a future release of the scripts.


  2. Pingback: September 2011 SQL Server 2008 Instance Level Diagnostic Queries | Glenn Berry's SQL Server Performance

  3. Pingback: SQL Server 2008 Diagnostic Queries « WebJagger’s Blog

  4. Perry Whittle says:

    Hi Glenn

    just a quick heads up on the database file locations. If using Windows mount points then it’s perfectly valid for all database files (inc tempdb) to be using the same drive letter


    Perry Whittle

  5. Pingback: Performance Tunning Part 2 | Vinay Thakur – Sql Server DBA

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