SQL Server 2012 Diagnostic Information Queries (Nov 2012)

Here is the November 2012 version of my SQL Server 2012 Diagnostic Information Queries, with some minor tweaks and improvements to a couple of the existing queries. I want to thank Mike Fal (blog/Twitter) who made a very useful suggestion for Query #36.

To go with this new version is an updated, blank SQL Server 2012 results spreadsheet, where the tabs in the spreadsheet are labeled and in the same order as the queries. The idea is that you can run these queries one by one, click on the top left square of the results grid, and then right-click and select “Copy with Headers” to select and copy the results of the query and then paste them into the matching tab of the results spreadsheet.

Many of these queries only work on SQL Server 2012, although some will work on older versions of SQL Server. If you are using an older version of SQL Server, you should use the version of my diagnostic queries for that version of SQL Server.

As always, I welcome any feedback you may have about these queries.

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

3 Responses to SQL Server 2012 Diagnostic Information Queries (Nov 2012)

  1. Chris Yates says:

    Glenn, thanks for this months new rendition. I’m downloading and will actively be using these in my 2012 environment. As I run across anything I’ll ping you back for feedback. Again many thanks for keeping these so up to date. I have found them very useful and have named it my CheklistOnSteroids locally. Take care

  2. Pingback: SQL Server – Diagnostic Information Queries (2005/2008/2012) « Alex Souza

  3. spaghettidba says:

    Glenn, thanks for this incredibly valuable tool. I’ve been using your diagnostic queries for a long time and I always learn something new from your updates.
    Let me propose a tweak to your query #25: since we’re running on SQL Server 2012, we could take advantage of the improvements in the OVER clause and calculate running totals in a more efficient manner:

    WITH Waits
    AS (
    SELECT wait_type
    ,CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS wait_time_s
    ,CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER () AS decimal(12,2)) AS pct
    ,ROW_NUMBER() OVER (
    ORDER BY wait_time_ms DESC
    ) AS rn
    FROM sys.dm_os_wait_stats WITH (NOLOCK)
    WHERE wait_type NOT IN (
    N’CLR_SEMAPHORE’
    ,N’LAZYWRITER_SLEEP’
    ,N’RESOURCE_QUEUE’
    ,N’SLEEP_TASK’
    ,N’SLEEP_SYSTEMTASK’
    ,N’SQLTRACE_BUFFER_FLUSH’
    ,N’WAITFOR’
    ,N’LOGMGR_QUEUE’
    ,N’CHECKPOINT_QUEUE’
    ,N’REQUEST_FOR_DEADLOCK_SEARCH’
    ,N’XE_TIMER_EVENT’
    ,N’BROKER_TO_FLUSH’
    ,N’BROKER_TASK_STOP’
    ,N’CLR_MANUAL_EVENT’
    ,N’CLR_AUTO_EVENT’
    ,N’DISPATCHER_QUEUE_SEMAPHORE’
    ,N’FT_IFTS_SCHEDULER_IDLE_WAIT’
    ,N’XE_DISPATCHER_WAIT’
    ,N’XE_DISPATCHER_JOIN’
    ,N’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’
    ,N’ONDEMAND_TASK_QUEUE’
    ,N’BROKER_EVENTHANDLER’
    ,N’SLEEP_BPOOL_FLUSH’
    ,N’SLEEP_DBSTARTUP’
    ,N’DIRTY_PAGE_POLL’
    ,N’HADR_FILESTREAM_IOMGR_IOCOMPLETION’
    ,N’SP_SERVER_DIAGNOSTICS_SLEEP’
    )
    ),
    Running_Waits AS (
    SELECT W1.wait_type
    ,wait_time_s
    ,pct
    ,SUM(pct) OVER(ORDER BY pct DESC ROWS UNBOUNDED PRECEDING) AS running_pct
    FROM Waits AS W1
    )
    SELECT *
    FROM Running_Waits
    WHERE running_pct – pct < 99
    ORDER BY running_pct
    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