Since tomorrow is May 1, I think it is time to publish the latest set of SQL Server 2008 Diagnostic Information Queries. This version works on SQL Server 2008 and SQL Server 2008 R2. There are a few queries that only work with SQL Server 2008 R2 SP1, but they are noted in the comments.
Many of these queries are DMV queries that require VIEW SERVER STATE permission. Make sure to read the instructions before you run each query. You really should run these queries one at a time, and take a few moments to look at each set of results, rather than running them in a single batch. Much of the value of these diagnostic queries is in the result interpretation comments after each query.
I have fixed a few minor issues with this version, and added several new queries. Please let me know what you think!
Glenn Berry's SQL Server Performance 
Thank you! Outstanding as always and very useful. I appreciate having this in one easy to reference script.
Glad that the script is helpful!
Pingback: Are You Using the Right SQL Server Performance Metrics? | SQLRockstar | Thomas LaRock
Thank you very much Glenn for making these available. In looking through them, I think a couple SQL 2012 specific DMV queries snuck into this SQL 2008 script — sys.dm_db_log_space_usage and sys.dm_os_server_diagnostics_log_configurations — or did I miss something?
No, a couple of SQL Server 2012 specific queries slipped in when I made a revision. I have fixed that. Thanks for letting me know!
Excellent. I like to run them all at once, so I have added print statements (essentially uncommenting your description before the query and the recommendations after). Also, I added a cursor to go through all the databases. I have one server that has 150 databases on it and was surprised to find quite a few don’t have stored procedures! I have added this one in as well http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/
the link appears to be broken
I am a huge fan of your diagnostic queries and a regular reader (RSS) of your blog. Thank you for all your contributions.
One Suggestion: on line 174, for the [Space Free %] calculation, you cast as a decimal(18,1) which effectively rounds disk free space to the closest 10%. I changed this to (18.3) to receive further details. Additionally, I the *100 could occur before being cast to (18,1)
Thanks for pointing those changes out.
This is really a great collection of useful queries. I would change the “Top cached queries by Execution Count (SQL Server 2008)” a little bit with a SELECT TOP 250 or qs.execution_time > N where N depends on the workload. I have ten thousands of queries in the system and took long to get them.
Pingback: Montando um gráfico de utilização de CPU « Freccia's Blog
Excellent!!
Thanks
Partha
Pingback: SQL Server Dev Connections 2012 | Dana Baxter