Here is the latest version of my SQL Server 2008 Diagnostic Information Queries, with some minor tweaks and improvements to a number of the existing queries, and one new query. To go with it is an updated, blank 2008 results spreadsheet, where the tabs in the spreadsheet are labeled and in the same order as the queries. The idea is that you can run these queries one by one, click on the top left square of the results grid, and then right-click and select “Copy with Headers” to select and copy the results of the query and then paste them into the matching tab of the results spreadsheet.
These queries will work on both SQL Server 2008 and SQL Server 2008 R2 (although some of them will only work on SQL Server 2008 R2 SP1 or later). This is indicated in the instructions for each query.
The July 2012 versions for SQL Server 2005 and SQL Server 2012 will be available in the next day or so. As always, let me know what you think of these queries.
Glenn Berry's SQL Server Performance 
Thank you for the update. Both the links points to the same SQL script file. Could you please update the spreadsheet link.
The link should be fixed now.
Thank you 🙂
Hello!
Do you know something like your set of scripts but for Oracle?
Thanks in advance!
Glenn. We are running database mirroring. As a result, DBMIRROR_EVENTS_QUEUE and DBMIRRORING_CMD dominate the results of query 20 (they account for more than 92% of the total waits). We also see DBMIRROR_DBM_EVENT. As far as I can determine, the first two are a consequence of mirroring just waiting for something to do, whilst the last is “Identified for informational purposes only. Not supported. Future compatibility is not guaranteed” in SQL Server 2008. If my analysis is correct would it be worth adding these to the list of exclusions?
I noticed the node name behaves buggy when including the is_clustered column in the select list.
( Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) )
removing the is_clustered column from your query shows the correct data in column cluster_nodename.
Select * from sys.dm_server_services shows the correct data.
Thank you for these great sets of inventory queries.
Hi Glenn. Just discovered your blog a couple of weeks ago and I found your diag queries very useful, so I’ve spent 3 hours of my job time working on a simple windows forms application that parses and runs your diagnostic queries on a remote server without the use of SMSS and all the copy&paste 😀
It’s only a 27kb exe. There must be a “query file” named queries.sql on the same location, that should be your query file. i’ve used the one in this post. The only modification I’ve made is commenting lines 539 and 540 (Use Yourdatabase) as they are queries that cannot be directly launched.
Your file is so coherent in its format that’s been really easy to do this 🙂
It’s not fully debugged, but it’s a good start and it works
Feel free to test it, make suggestions, etc.
Release files: http://personales.gestion.unican.es/ramosf/sqldiagqueries/sqldiagqueries_release.zip
Source code: http://personales.gestion.unican.es/ramosf/sqldiagqueries/sqldiagqueries_source.zip
Maybe it’s a good first step for something in Codeplex!
No, these are SQL Server only.
Pingback: SQL Server – Diagnostic Information Queries (2005/2008/2012) « Alex Souza
Thanks very much.
Amazing set of queries, very useful !
Have a great day 🙂
Roni
I think I got that fixed. Thanks!
Query #17 (VLF Counts) honks with ‘column name or number of supplied values does not match table definition’. I don’t have a RecoveryUnitID in the results of DBCC LOGINFO, so I omit that column from the temp table definition and all is good. Thanks for some very useful scripts, Glenn.
Link is not working…Giving 404 not found message…
It works for me.