http://www.sqlservercentral.com/blogs/glennberry/2010/12/29/updated-sql-2005-and-2008-diagnostic-queries/

Printed 2014/08/20 04:45AM

Updated SQL 2005 and 2008 Diagnostic Queries

By GlennBerry, 2010/12/29

Since it is almost January 2011, I thought I would post the January 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.

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.

image

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.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.