SQL Server 2008/2008 R2 Diagnostic Queries for March 2012

Since it is nearly March, I will go ahead and jump the gun by a few days and post an updated version of my SQL Server 2008/2008 R2 Diagnostic Queries for March 2012.

For this version, I have added information about the latest SQL Server 2008 R2 Cumulative Updates, added some more interpretation information for many of the queries, and added some new columns to some of the existing queries.

This set of queries is only for SQL Server 2008 or SQL Server 2008 R2. Some of the queries (which are marked), will only work with SQL Server 2008 R2 SP1 since they use DMVs or DMFs that were added in SP1.

As always, I recommend that you run the queries one at a time, after reading the comments and instructions for each query. You should not make any rash decisions based on the results of a single query. Instead, you should gather more information and use your good judgment and experience before you do something like dropping an index.

About these ads
This entry was posted in SQL Server 2008 R2 and tagged . Bookmark the permalink.

7 Responses to SQL Server 2008/2008 R2 Diagnostic Queries for March 2012

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

  2. Jason says:

    Glenn, I love these diagnostic queries. I think you might have the output column name of the Volume info for all databases on the current instance a little mixed up.
    The column Space Used % should actually be labelled Space Free % as it’s dividing the Available Space by the Total Space. For example my System DB Drive is showing 21340921856 total_bytes and 20661043200 available_bytes, and Space Used% is showing 100%.

  3. Rick says:

    These are awesome Glenn. Thanks for sharing!

  4. Pingback: Something for the Weekend – SQL Server Links 02/03/12

  5. IJeb says:

    Glenn, thanks for these scripts. I have used different versions since 2009 and they are always very helpful.
    I noticed the same problem as Jason, [Space Used %] should be [Space Available %].

  6. Michal says:

    Hello Glen!

    First I would like to say thank YOU for your great work!

    I am using your queries on daily basis and I found useful one addition that i made. Maybe it will be a good idea to add it to your next release of Diagnostic queries. Please have a look ;)

    Output from this can be used to excel line chart and it show you how much your storage performance is degrading over time based on backup history.

    — Storage writes throughput from backup history

    WITH Thr (
    database_name,
    backup_finish_date,
    duration_in_min,
    server_name,
    size_in_MB
    –,recovery_model –optional atributes
    –,is_readonly –optional atributes
    )
    AS
    (
    SELECT database_name,
    backup_finish_date,
    DATEDIFF(mi,backup_start_date,backup_finish_date) as duration_in_min,
    server_name,
    backup_size/1048576
    –,recovery_model –optional atributes
    –,is_readonly –optional atributes
    FROM msdb..backupset
    )

    SELECT t.*
    ,(size_in_MB/ duration_in_min) as throughput_in_MB_per_min
    FROM Thr as t
    WHERE duration_in_min > 0
    ORDER BY Backup_finish_date

    PS: maybe it will not be the best script, but I am trying to return something useful to the community. Hope it helps.

  7. @Kos1mo says:

    Awesome collection, as always! Thanks so much!

    I can’t think of a reason this tweaked PLE section would have any disadvantages? If you can’t either would love to see it in a release! :)

    — Page Life Expectancy (PLE) value, multiple instances
    SELECT cntr_value AS [Page Life Expectancy], [object_name]
    FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE [object_name] LIKE N’%Buffer Manager%’ — Modified for multiple instances
    AND counter_name = N’Page life expectancy’ OPTION (RECOMPILE);

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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s