USE [msdb]
GO

/****** Object:  Job [IncrementalWaitStatsCapture]    Script Date: 11/10/2015 6:34:50 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 11/10/2015 6:34:50 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'IncrementalWaitStatsCapture', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [IncrementalWaitStatsCapture]    Script Date: 11/10/2015 6:34:51 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'IncrementalWaitStatsCapture', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'--IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]    WHERE [name] = N''#CaptureStats1'')    DROP TABLE [#CaptureStats1];
--IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]    WHERE [name] = N''#CaptureStats2'')    DROP TABLE [#CaptureStats2];
--DROP TABLE [#CaptureStats1];
--DROP TABLE [#CaptureStats2];
--SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] into dbo.System_Prev_Wait_Stats FROM sys.dm_os_wait_stats;
----
/* ---- create this table the very first time----*/
--IF EXISTS (SELECT * FROM [admindb].[sys].[objects] WHERE [name] = N''#System_Prev_Wait_Stats'')    DROP TABLE [System_Prev_Wait_Stats];
--GO
/* ---- comment it out after table created ----*/
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] INTO #CaptureStats1 FROM dbo.System_Prev_Wait_Stats;
GO

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] INTO #CaptureStats2 FROM sys.dm_os_wait_stats;
GO
INSERT INTO #CaptureStats2 SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] FROM sys.dm_os_wait_stats;
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 [#CaptureStats2] AS [ts2]    LEFT OUTER JOIN [#CaptureStats1] 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 [#CaptureStats2] AS [ts2]   
    LEFT OUTER JOIN [#CaptureStats1] 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'')    )
INSERT INTO [dbo].[IncrementalWaitStats]
           ([CreatedDatetime]
           ,[WaitType]
           ,[Wait_S]
           ,[Resource_S]
           ,[Signal_S]
           ,[WaitCount]
           ,[Percentage]
           ,[AvgWait_S]
           ,[AvgRes_S]
           ,[AvgSig_S])
(SELECT getdate() AS [CreatedDatetime], [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)
GO
truncate table #CaptureStats1
GO
truncate table #CaptureStats2
GO
TRUNCATE TABLE dbo.System_Prev_Wait_Stats;
DROP TABLE dbo.System_Prev_Wait_Stats;
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] into dbo.System_Prev_Wait_Stats FROM sys.dm_os_wait_stats;
GO
-- Cleanup
DROP TABLE [#CaptureStats1];
GO
DROP TABLE [#CaptureStats2];
GO
--IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]    WHERE [name] = N''#CaptureStats1'')    DROP TABLE [#CaptureStats1]; 
--IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]    WHERE [name] = N''#CaptureStats2'')    DROP TABLE [#CaptureStats2];
GO', 
		@database_name=N'ADMINDB', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'IncrementalWaitStatsCaptureSchedule', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=2, 
		@freq_subday_interval=15, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20150621, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'd54b36cd-5db0-4e4f-b260-42580831fc3a'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

