SQL Server 2008 Diagnostic Information Queries (April 2012)

Since it is nearly April, I will go ahead and jump the gun again and post an updated version of my 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 are DMV queries that require VIEW SERVER STATE permission. 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.

I have fixed a few minor issues with this version, and added a couple of 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.

11 Responses to SQL Server 2008 Diagnostic Information Queries (April 2012)

  1. csewconnors says:

    Good God, man!

    Just kidding – Wow, that’s a lot of good stuff! Very impressive. Thanks so much Glenn!

  2. Ranga Narasimhan says:

    Thanks for providing, its been very useful.


  3. Pingback: Don’t Plan to Fail « Mike Donnelly, SQLMD

  4. Christian says:

    Hello Glenn,
    is it possible for the next “releases” of your “Diagnostic Information Queries” to include a schema column, if it makes sense.

  5. Pingback: Something for the Weekend – SQL Server Links 06/04/12 - John Sansom SQL Server DBA in the UK

  6. msbuild says:

    Awesome job here. I will test each queries over the coming days 🙂

  7. Scott says:

    Several of the Top Cached SPs queries take considerable time to execute. Is that normal?

    • Glenn Berry says:

      Which ones seem to be taking a long time? Is the server/database under any stress, do you have a large number of stored procedure plans in the cache?

      • Scott says:

        These all have no results:
        Top Cached SPs By Execution Count (SQL 2008)
        Top Cached SPs By Avg Elapsed Time (SQL 2008)
        Top Cached SPs By Total Worker time (SQL 2008). Worker time relates to CPU cost
        Top Cached SPs By Total Logical Reads (SQL 2008). Logical reads relate to memory pressure
        Top Cached SPs By Total Physical Reads (SQL 2008). Physical reads relate to disk I/O pressure
        Top Cached SPs By Total Logical Writes (SQL 2008).

  8. Glenn Berry says:

    The top cached SP queries will have no results if there are no execution plans from stored procedures in the plan cache. This could be because the database you are connected to does not have any stored procedures or because they have not been executed since the instance was started or the plan cache was cleared. It is also possible that you are pointing at the master database.

    I thought your original question was about some of these queries taking a long time?

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