Send me your wait stats and get my advice and 30 days of free Pluralsight in return

[Edit: 3/25/14 No more codes left – thanks for all the data! – please don’t send any more.]

Yes, you read that correctly. Call me crazy, but I’ve been wanting to do this for a while.

Here’s the deal:

  1. You run the code from this post that creates a 24-hour snapshot of the wait stats on your server. (You can do it on as many servers as you want! Or multiple times on the same server. Seriously, the more data the better.)
  2. You send me the wait stats info in email.
  3. I’ll give you some feedback and insight on your wait stats.
  4. I’ll send the first 500 of you a code to get 30 days of free access to all 100+ hours of SQLskills Pluralsight training classes.
  5. I’ll also editorialize the results (possibly over several posts) in April

I was going to work with Pluralsight to get codes for just my SQL Server: Performance Troubleshooting Using Wait Statistics course, but they’re cool with giving away a month of all our courses. Nice!

What’s the catch? There is no catch. I get lots of real-life data, you get lots of real-life training. No credit-card required.

Why am I doing this? I *really* like looking at wait statistics data (kind of like Jonathan with his unhealthy obsession with analyzing deadlocks) and it’s been more than 3 years since my last huge wait stats survey. Also I *really* like helping people understand what wait statistics mean, I *really* like being sleep-deprived :-), and I’d *really* like to show you how awesome our online training courses are.

When does this offer end? You’ve got until the end of March to send me your wait stats info.

Rules? One code per email address that sends me wait stats, no matter how many servers you send in – I’ve got a limited supply. Please don’t post as a comment with the stats in (I’ll delete them) – email only. I’ll send you the code and info on how to redeem it.

Use the T-SQL code below please (or grab the zip file from here), and leave the WAITFOR set to 24 hours. Send me an email [Edit: the survey’s done now – please don’t send any more data] with the results in plain text or a spreadsheet. Everybody wins.

Look forward to hearing from you!

PS There is no perf hit from running this code, or any nasty side-effect. It simply creates two snapshots of the sys.dm_os_wait_stats output 24 hours apart.

PPS Thanks to those who pointed out the typo that crept in between coding and posting – sorry about that.

/*============================================================================
  File:     WaitStats2014.sql

  Summary:  24-hour snapshot of wait stats

  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  Written by Paul S. Randal, SQLskills.com

  (c) 2014, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
	WHERE [name] = N'##SQLskillsStats1')
	DROP TABLE [##SQLskillsStats1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
	WHERE [name] = N'##SQLskillsStats2')
	DROP TABLE [##SQLskillsStats2];
GO

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms],
       [max_wait_time_ms], [signal_wait_time_ms]
INTO ##SQLskillsStats1
FROM sys.dm_os_wait_stats;
GO

WAITFOR DELAY '23:59:59';
GO

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms],
       [max_wait_time_ms], [signal_wait_time_ms]
INTO ##SQLskillsStats2
FROM sys.dm_os_wait_stats;
GO

WITH [DiffWaits] AS
(SELECT
-- Waits that weren't in the first snapshot
		[ts2].[wait_type],
		[ts2].[wait_time_ms],
		[ts2].[signal_wait_time_ms],
		[ts2].[waiting_tasks_count]
	FROM [##SQLskillsStats2] AS [ts2]
	LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
		ON [ts2].[wait_type] = [ts1].[wait_type]
	WHERE [ts1].[wait_type] IS NULL
	AND [ts2].[wait_time_ms] > 0
UNION
SELECT
-- Diff of waits in both snapshots
		[ts2].[wait_type],
		[ts2].[wait_time_ms] - [ts1].[wait_time_ms] AS [wait_time_ms],
		[ts2].[signal_wait_time_ms] - [ts1].[signal_wait_time_ms] AS [signal_wait_time_ms],
		[ts2].[waiting_tasks_count] - [ts1].[waiting_tasks_count] AS [waiting_tasks_count]
	FROM [##SQLskillsStats2] AS [ts2]
	LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
		ON [ts2].[wait_type] = [ts1].[wait_type]
	WHERE [ts1].[wait_type] IS NOT NULL
	AND [ts2].[waiting_tasks_count] - [ts1].[waiting_tasks_count] > 0
	AND [ts2].[wait_time_ms] - [ts1].[wait_time_ms] > 0),
[Waits] AS
	(SELECT
		[wait_type],
		[wait_time_ms] / 1000.0 AS [WaitS],
		([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
		[signal_wait_time_ms] / 1000.0 AS [SignalS],
		[waiting_tasks_count] AS [WaitCount],
		100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
		ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
	FROM [DiffWaits]
	WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',
        N'CHKPT',						N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',				N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',					N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT',	N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',				N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',				N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',				N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH',	N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',				N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP',	N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',
        N'WAITFOR',						N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT')
	)
SELECT
	[W1].[wait_type] AS [WaitType],
	CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
	CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
	CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
	[W1].[WaitCount] AS [WaitCount],
	CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
	CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
	CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
	CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
	ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
	[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO

-- Cleanup
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
	WHERE [name] = N'##SQLskillsStats1')
	DROP TABLE [##SQLskillsStats1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
	WHERE [name] = N'##SQLskillsStats2')
	DROP TABLE [##SQLskillsStats2];
GO

26 thoughts on “Send me your wait stats and get my advice and 30 days of free Pluralsight in return

  1. Would it be possible to make the anonymized data available as a download for others interested in taking a look?

  2. DB Mirroring is skewing results badly. In the end I got only DBMIRROR_EVENTS_QUEUE, DBMIRROR_DBM_EVENT and DBMIRRORING_CMD.

    The script bombs with “Arithmetic overflow error converting numeric to data type numeric” sometimes too.

    1. Yup – I’ve been updating the script as it goes along with more wait types that are skewing things. Usually these don’t get filtered out.

      Yes – if there’s only one wait type then the percentage calculation might fail.

    2. I changed “CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage]” to
      “CAST ([W1].[Percentage] AS DECIMAL(5, 2)) AS [Percentage]” to avoid “Arithmetic overflow error converting numeric to data type numeric” in a very particular case.

  3. The main production database currently uses SIMPLE recovery model (I know, but I can explain…).
    Should I turn it to FULL recovery model during these 24 hours or it doesn’t matter ?
    Still on time to get the 30 days of free Pluralsight ??
    Thanks.

  4. Hi Paul,
    I am not expert, not even a DBA, but the problem I see with sys.dm_os_wait_stats is that it gives wrong results with blocked process, in the sense that you cannot interpreted correctly the “wait type” due to all blocked process.
    If possible, don’t you think you should removed (or separate) the wait of blocked process to have more relevant wait statistics to analyse?
    Thanks.

    1. No, it doesn’t give wrong results. It shows that blocking is a primary issue. No – the point is to see all wait types together and diagnose the most prevalent performance issues.

      1. Yes, you are right, but in case of “LCK_” primary issue, how are you able to investigate more on the blocking reason? Don’t you think it could be interesting to tag the wait of blocking (not blocked I agree) process?
        Thanks.

  5. Paul’s courses on pluarlsight are fantastic. I don’t know Paul, Kimberly or anyone else from sqlkills so I gain nothing from shilling the site or the courses but for $29/month this is the steal of the century for sql server dbas.

  6. …running it across our 9 main Production SQL Servers.
    Paul – then are you going to produce a sort of statistical report across all the results that people will send over to you?

  7. Hey Paul,
    1) I’ve used your previously posted wait stats collection script that has about half as many as wait type exclusions with great success. Should I update that analysis script to include this much longer exclusion list or has this query been written for a slightly different purpose?
    2) Do you really mean “the more data the better”? I can send you data from hundreds of production servers if your obsession needs feeding that badly.

  8. Hi Paul,

    Does it have to 24 hours? Can it be against SQL Server 2014 CTP2? I’m running a controlled benchmark which has some interesting wait stats. The benchmark doesn’t run for 24 hours, but I can collect stats for the time period of the benchmark. Are you interested in looking at these?

    Thanks,
    -Peter

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.