[Edit: 3\/25\/14 No more codes left – thanks for all the data! – please don’t send any more.]<\/p>\n
Yes, you read that correctly. Call me crazy, but I’ve been wanting to do this for a while.<\/p>\n
Here’s the deal:<\/strong><\/p>\n I was going to work with Pluralsight to get codes for just my SQL Server: Performance Troubleshooting Using Wait Statistics course<\/a>, but they’re cool with giving away a month of all our courses. Nice!<\/p>\n What’s the catch?<\/strong> There is no catch. I get lots of real-life data, you get lots of real-life training. No credit-card required.<\/p>\n Why am I doing this?<\/strong>\u00a0I *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\u00a0our online training courses<\/a>\u00a0are.<\/p>\n When does this offer end?<\/strong> You’ve got until the end of March to send me your wait stats info.<\/p>\n Rules?<\/strong> 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.<\/p>\n Use the T-SQL code below please (or grab the zip file from here<\/a>), and leave the WAITFOR<\/em> set to 24 hours. Send me an email<\/a>\u00a0[Edit: the survey’s done now – please don’t send any more data] with the results in plain text or a spreadsheet. Everybody wins.<\/p>\n Look forward to hearing from you!<\/p>\n PS There is no perf hit from running this code, or any nasty side-effect. It simply creates two snapshots of the\u00a0sys.dm_os_wait_stats<\/em>\u00a0output 24 hours apart.<\/p>\n PPS Thanks to those who pointed out the typo that crept in between coding and posting – sorry about that.<\/p>\n [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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[69,97,101],"tags":[],"class_list":["post-4316","post","type-post","status-publish","format-standard","hentry","category-pluralsight","category-training","category-wait-stats"],"yoast_head":"\n\n
\r\n\/*============================================================================\r\n File: WaitStats2014.sql\r\n\r\n Summary: 24-hour snapshot of wait stats\r\n\r\n SQL Server Versions: 2005 onwards\r\n------------------------------------------------------------------------------\r\n Written by Paul S. Randal, SQLskills.com\r\n\r\n (c) 2014, SQLskills.com. All rights reserved.\r\n\r\n For more scripts and sample code, check out\r\n http:\/\/www.SQLskills.com\r\n\r\n You may alter this code for your own *non-commercial* purposes. You may\r\n republish altered code as long as you include this copyright and give due\r\n credit, but you must obtain prior permission before blogging this code.\r\n\r\n THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF\r\n ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED\r\n TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A\r\n PARTICULAR PURPOSE.\r\n============================================================================*\/\r\n\r\nIF EXISTS (SELECT * FROM [tempdb].[sys].[objects]\r\n\tWHERE [name] = N'##SQLskillsStats1')\r\n\tDROP TABLE [##SQLskillsStats1];\r\n\r\nIF EXISTS (SELECT * FROM [tempdb].[sys].[objects]\r\n\tWHERE [name] = N'##SQLskillsStats2')\r\n\tDROP TABLE [##SQLskillsStats2];\r\nGO\r\n\r\nSELECT [wait_type], [waiting_tasks_count], [wait_time_ms],\r\n [max_wait_time_ms], [signal_wait_time_ms]\r\nINTO ##SQLskillsStats1\r\nFROM sys.dm_os_wait_stats;\r\nGO\r\n\r\nWAITFOR DELAY '23:59:59';\r\nGO\r\n\r\nSELECT [wait_type], [waiting_tasks_count], [wait_time_ms],\r\n [max_wait_time_ms], [signal_wait_time_ms]\r\nINTO ##SQLskillsStats2\r\nFROM sys.dm_os_wait_stats;\r\nGO\r\n\r\nWITH [DiffWaits] AS\r\n(SELECT\r\n-- Waits that weren't in the first snapshot\r\n\t\t[ts2].[wait_type],\r\n\t\t[ts2].[wait_time_ms],\r\n\t\t[ts2].[signal_wait_time_ms],\r\n\t\t[ts2].[waiting_tasks_count]\r\n\tFROM [##SQLskillsStats2] AS [ts2]\r\n\tLEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]\r\n\t\tON [ts2].[wait_type] = [ts1].[wait_type]\r\n\tWHERE [ts1].[wait_type] IS NULL\r\n\tAND [ts2].[wait_time_ms] > 0\r\nUNION\r\nSELECT\r\n-- Diff of waits in both snapshots\r\n\t\t[ts2].[wait_type],\r\n\t\t[ts2].[wait_time_ms] - [ts1].[wait_time_ms] AS [wait_time_ms],\r\n\t\t[ts2].[signal_wait_time_ms] - [ts1].[signal_wait_time_ms] AS [signal_wait_time_ms],\r\n\t\t[ts2].[waiting_tasks_count] - [ts1].[waiting_tasks_count] AS [waiting_tasks_count]\r\n\tFROM [##SQLskillsStats2] AS [ts2]\r\n\tLEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]\r\n\t\tON [ts2].[wait_type] = [ts1].[wait_type]\r\n\tWHERE [ts1].[wait_type] IS NOT NULL\r\n\tAND [ts2].[waiting_tasks_count] - [ts1].[waiting_tasks_count] > 0\r\n\tAND [ts2].[wait_time_ms] - [ts1].[wait_time_ms] > 0),\r\n[Waits] AS\r\n\t(SELECT\r\n\t\t[wait_type],\r\n\t\t[wait_time_ms] \/ 1000.0 AS [WaitS],\r\n\t\t([wait_time_ms] - [signal_wait_time_ms]) \/ 1000.0 AS [ResourceS],\r\n\t\t[signal_wait_time_ms] \/ 1000.0 AS [SignalS],\r\n\t\t[waiting_tasks_count] AS [WaitCount],\r\n\t\t100.0 * [wait_time_ms] \/ SUM ([wait_time_ms]) OVER() AS [Percentage],\r\n\t\tROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]\r\n\tFROM [DiffWaits]\r\n\tWHERE [wait_type] NOT IN (\r\n N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',\r\n N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',\r\n N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',\r\n N'CHKPT',\t\t\t\t\t\tN'CLR_AUTO_EVENT',\r\n N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',\r\n N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',\r\n N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',\r\n N'DIRTY_PAGE_POLL',\t\t\t\tN'DISPATCHER_QUEUE_SEMAPHORE',\r\n N'EXECSYNC',\t\t\t\t\tN'FSAGENT',\r\n N'FT_IFTS_SCHEDULER_IDLE_WAIT',\tN'FT_IFTSHC_MUTEX',\r\n N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',\r\n N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',\r\n N'HADR_TIMER_TASK',\t\t\t\tN'HADR_WORK_QUEUE',\r\n N'KSOURCE_WAKEUP',\t\t\t\tN'LAZYWRITER_SLEEP',\r\n N'LOGMGR_QUEUE',\t\t\t\tN'ONDEMAND_TASK_QUEUE',\r\n N'PWAIT_ALL_COMPONENTS_INITIALIZED',\r\n N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',\r\n N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',\r\n N'REQUEST_FOR_DEADLOCK_SEARCH',\tN'RESOURCE_QUEUE',\r\n N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',\r\n N'SLEEP_DBSTARTUP',\t\t\t\tN'SLEEP_DCOMSTARTUP',\r\n N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',\r\n N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',\r\n N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',\r\n N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',\r\n N'SP_SERVER_DIAGNOSTICS_SLEEP',\tN'SQLTRACE_BUFFER_FLUSH',\r\n N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',\r\n N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',\r\n N'WAITFOR',\t\t\t\t\t\tN'WAITFOR_TASKSHUTDOWN',\r\n N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',\r\n N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',\r\n N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')\r\n\t)\r\nSELECT\r\n\t[W1].[wait_type] AS [WaitType],\r\n\tCAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],\r\n\tCAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],\r\n\tCAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],\r\n\t[W1].[WaitCount] AS [WaitCount],\r\n\tCAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],\r\n\tCAST (([W1].[WaitS] \/ [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],\r\n\tCAST (([W1].[ResourceS] \/ [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],\r\n\tCAST (([W1].[SignalS] \/ [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]\r\nFROM [Waits] AS [W1]\r\nINNER JOIN [Waits] AS [W2]\r\n\tON [W2].[RowNum] <= [W1].[RowNum]\r\nGROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],\r\n\t[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]\r\nHAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold\r\nGO\r\n\r\n-- Cleanup\r\nIF EXISTS (SELECT * FROM [tempdb].[sys].[objects]\r\n\tWHERE [name] = N'##SQLskillsStats1')\r\n\tDROP TABLE [##SQLskillsStats1];\r\n\r\nIF EXISTS (SELECT * FROM [tempdb].[sys].[objects]\r\n\tWHERE [name] = N'##SQLskillsStats2')\r\n\tDROP TABLE [##SQLskillsStats2];\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"