February 2011 Version of SQL Server 2005 and 2008 Diagnostic Information Queries

Wow, it is already February 2011, so here are the February 2011 versions of the SQL Server 2005 Diagnostic Information Queries and the SQL Server 2008 Diagnostic Information Queries.

To go with them are the matching blank results spreadsheets for SQL Server 2005 and SQL Server 2008. The spreadsheets have their tabs labeled in order to match the queries. That way, it is easy to run each query, click the top left box in the results grid to select all columns and rows, right-click and select “Copy with Headers” to copy all of the results to the Windows clipboard. Then you can paste the results to the appropriate tab in the spreadsheet.

I also want to remind people that Mainstream Support for SQL Server 2005 ends on April 12, 2011 (even if you are on SP4).  This is a good excuse to push for an upgrade, especially if you are running SQL Server 2005 on top of Windows Server 2003 or 2003 R2, which are both out of Mainstream Support.

If you are still using the SQL Server 2005 version of SQL Server Management Studio (SSMS), you will need to make sure you change a setting under “Tools”, “Options”, “Query Results”, “SQL Server”, “Results to Grid”, to enable “Include column headers when copying or saving the results”, like you see below, before you run the queries.


Otherwise, you will lose the column headers, which makes the results much harder to interpret.

As always, you will need to have VIEW SERVER STATE permission to run most of the DMV queries.

I advise you to run each query one at a time, after reading the comments. The last query in the script (to check index fragmentation) could take quite a while to run, depending on your database size and your hardware infrastructure.

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

5 Responses to February 2011 Version of SQL Server 2005 and 2008 Diagnostic Information Queries

  1. Randy says:

    Fantastic, thanks for your hard work.

  2. Carlos says:

    Thanks Glenn! So much to learn, so little time…

  3. Pingback: Weekly Challenges – 2/6/11 « SQL Feather and Quill

  4. Michael Rybicki says:

    Looks like your hyperlink to the February Spreadsheet hasn’t been changed to point to the February file. I got the file anyway by changing the link manually.

    Also – do you have a list of things that have changed in this version? I haven’t finished learning all of the January ones yet but I’m guessing you came up with a few more?

    I have a suggestion for one if you haven’t already thought of it – I have taken the “Top Cached SPs By Total Logical Reads” and modified it to show me the queries that have the largest difference between Minimum READs and Maximum READs. I’m not quite done with it but the goal is to find the sprocs that were being used with a bad plan and should be RECOMPILED every execution because the results sets vary so greatly. Our example was reading Customer and Sales Order information when One customer might only have one or two orders (which the plan was cached with) and then someone querying a customer that had 26,000 orders. The logical reads went from 201 to 2.6 million so we added WITH RECOMPILE to force a good plan everytime on that and several other queries. It worked temporarily for that customer but we are still researching a long-term fix that will cover the many different scenarios for all of our customers.


  5. Pingback: Parallelism, SQL Server, and you: Round 2 | Art of the DBA

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 )

Connecting to %s