SQL Server 2008 Diagnostic Information Queries (May 2012)

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!

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

13 Responses to SQL Server 2008 Diagnostic Information Queries (May 2012)

  1. veronelazio says:

    Thank you! Outstanding as always and very useful. I appreciate having this in one easy to reference script.

  2. Pingback: Are You Using the Right SQL Server Performance Metrics? | SQLRockstar | Thomas LaRock

  3. 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?

  4. Scott says:

    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/

  5. 7gartner says:

    the link appears to be broken

  6. 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)

  7. Janos says:

    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.

  8. Pingback: Montando um gráfico de utilização de CPU « Freccia's Blog

  9. Excellent!!

  10. Pingback: SQL Server Dev Connections 2012 | Dana Baxter

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 )

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