Updated SQL 2005 and 2008 Diagnostic Queries

Since it is almost January 2011, I thought I would post the January 2011 versions of the SQL Server 2005 Diagnostic Information Queries and the SQL Server 2008 Diagnostic Information Queries.

To go with them are the matching blank results spreadsheets for SQL Server 2005 and SQL Server 2008. The spreadsheets have their tabs labeled in order to match the queries. That way, it is easy to run each query, click the top left box in the results grid to select all columns and rows, right-click and select “Copy with Headers” to copy all of the results to the Windows clipboard. Then you can paste the results to the appropriate tab in the spreadsheet.

If you are still using the SQL Server 2005 version of SQL Server Management Studio (SSMS), you will need to make sure you change a setting under “Tools”, “Options”, “Query Results”, “SQL Server”, “Results to Grid”, to enable “Include column headers when copying or saving the results”, like you see below, before you run the queries.


Otherwise, you will lose the column headers, which makes the results much harder to interpret.

As always, you will need to have VIEW SERVER STATE permission to run most of the DMV queries.

I advise you to run each query one at a time, after reading the comments. The last query in the script (to check index fragmentation) could take quite a while to run, depending on your database size and your hardware infrastructure.

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

9 Responses to Updated SQL 2005 and 2008 Diagnostic Queries

  1. Pingback: The Rambling DBA: Jonathan Kehayias : An XEvent a Day (30 of 31) – Tracking Session and Statement Level Waits

  2. Ronald Clauw says:

    Hi, Glenn

    I noticed an issue in your coding of your recent ‘Updated SQL 2005 and 2008 Diagnostic Queries’ regarding the statistics.

    — drop and create statistics (as test)
    use AdventureWorks

    if exists (select * from sys.stats where name = N’MYSTATS’ and object_id = object_id(N'[dbo].[ErrorLog]’))
    drop statistics [dbo].[ErrorLog].[MYSTATS]

    create STATISTICS [MYSTATS] on [dbo].[ErrorLog]([UserName], [ErrorNumber], [ErrorState])

    — this is your code

    — When were Statistics last updated on all indexes?
    OBJECT_SCHEMA_NAME(o.object_id) as SchemaOwner,
    o.name, i.name AS [Index Name],
    STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
    s.auto_created, s.no_recompute, s.user_created, st.row_count
    FROM sys.objects AS o WITH (NOLOCK)
    INNER JOIN sys.indexes AS i WITH (NOLOCK)
    ON o.[object_id] = i.[object_id]
    INNER JOIN sys.stats AS s WITH (NOLOCK)
    ON i.[object_id] = s.[object_id]
    AND i.index_id = s.stats_id
    INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
    ON o.[object_id] = st.[object_id]
    AND i.[index_id] = st.[index_id]
    WHERE o.[type] = ‘U’
    and o.name = ‘ErrorLog’

    –ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);

    — this code returns the correct statistics (without the join to sys.indexes)
    OBJECT_SCHEMA_NAME(o.object_id) as SchemaOwner,
    o.name as TableOwner,
    s.name as StatisticsName
    from sys.objects o with (nolock)
    inner join sys.stats s with (nolock)
    on o.object_id = s.object_id
    where 1 = 1
    and o.is_ms_shipped = 0
    and OBJECT_SCHEMA_NAME(s.object_id) not in (‘sys’)
    and OBJECT_NAME(s.object_id) not in (‘sysdiagrams’)
    and o.type = ‘U’
    and o.name = ‘ErrorLog’

    Kind regards.

  3. Ronald Clauw says:

    Hi Glenn

    This script is gives you more possibilities:

    use AdventureWorks

    select @@SERVERNAME as ServerName,
    DB_NAME(DB_ID()) as DatabaseName,
    t.name as TableName,
    i.name as IndexName,
    st.name as StatisticsName,
    STATS_DATE(i.object_id, i.index_id) AS LastUpdated,
    ps.row_count as RecordCount,
    st.auto_created as IsAutoCreated,
    st.user_created as IsUserCreated,
    st.no_recompute as IsNoRecompute

    from sys.tables t with (nolock)
    inner join sys.stats st with (nolock)
    on st.object_id = t.object_id
    inner join sys.indexes i with (nolock)
    on t.object_id = i.object_id
    inner join sys.dm_db_partition_stats as ps with (nolock)
    on t.object_id = ps.object_id
    and i.index_id = ps.index_id
    where 1 = 1
    and t.name ‘sysdiagrams’

    Kind regards

  4. ob says:

    Running the script on mirror fails with

    Msg 927, Level 14, State 2, Line 237
    Database ‘AdventureworksDR’ cannot be opened. It is in the middle of a restore.

  5. Pingback: SQL Server Diagnostic Queries « SqlSchool.gr

  6. Pingback: SQL SERVER – Guest Post – Jonathan Kehayias – Wait Type – Day 16 of 28 « Journey to SQL Authority with Pinal Dave

  7. Jeff Moden says:

    Hi Glenn,
    I’ve just discovered this wonderful post. I’ve not tried them, yet, but I wanted to say thank you for taking the time to share such a great collection of nicely documented and highly useful code and for maintaining it. You’re a truly generous and thoughtful person. Thanks, Glenn!

  8. Pingback: An XEvent a Day (30 of 31) – Tracking Session and Statement Level Waits | Jonathan Kehayias

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