[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:
- 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.)
- You send me the wait stats info in email.
- I’ll give you some feedback and insight on your wait stats.
- 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.
- 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”
This may be a silly question, but does this cause a noticeable performance or RAM hit?
No – and I’ve updated the post to say so.
Thanks to those who pointed out the typo that crept in during cut’n’paste. All fixed now.
Aw… only one server…? :-)
You can do it for as many servers as you want – the more data the better!
Would it be possible to make the anonymized data available as a download for others interested in taking a look?
Let me think about that.
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.
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.
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.
Paul. Thank you for the offer. Will be sending data from two servers looking forward to your feedback.
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 ??
Doesn’t matter. And yes.
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?
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.
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?
You do blocking chain analysis through sys.dm_os_waiting_tasks to find the lead blocker, and also check out http://michaeljswart.com/2011/05/okay-youve-found-blocking-now-what/
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.
…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?
We’ll see how it goes – deployed it from a centralized management server against 9 instances
Cool deal ! Just emailed – 7 small production servers.
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.
1) Yes – if you go to the original post in the Wait Stats category, I’ve updated the script.
2) Yes – bring it on!
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?