/*--Скрипт мониторинга----------------------
USE tempdb;

DECLARE @atB AS DATETIME;
SET @atB = 
(	SELECT	
		MIN(at)
	FROM	dbo.sys_dm_os_wait_stats
);

DECLARE @atA AS DATETIME;
SET @atA = 
(	SELECT	
		MAX(at)
	FROM	dbo.sys_dm_os_wait_stats
);

SELECT
	tB.at
,	tA.wait_type
,	waiting_tasks_count = tA.waiting_tasks_count - tB.waiting_tasks_count
,	wait_time_ms = tA.wait_time_ms - tB.wait_time_ms
,	wait_time_per_task_ms = 
		CASE WHEN tA.waiting_tasks_count = tB.waiting_tasks_count THEN
			0
		ELSE
			(tA.wait_time_ms - tB.wait_time_ms*1.)/(tA.waiting_tasks_count - tB.waiting_tasks_count)				
		END
,	signal_wait_time_ms = tA.signal_wait_time_ms - tB.signal_wait_time_ms
FROM	
	(	SELECT	*
	 	FROM	dbo.sys_dm_os_wait_stats
	 	WHERE	at = @atB
	) AS tB
INNER JOIN
	(	SELECT	*
	 	FROM	dbo.sys_dm_os_wait_stats
	 	WHERE	at = @atA
	) AS tA	
ON	tB.wait_type = tA.wait_type
ORDER BY
	tA.wait_time_ms - tB.wait_time_ms DESC;
------------------------------------------*/

USE [master];
SET NOCOUNT ON;

BEGIN TRY
	DROP TABLE tempdb.dbo.sys_dm_os_wait_stats;	
END TRY
BEGIN CATCH END CATCH;

BEGIN TRY
	CREATE TABLE #WaitTypeIrrelevant
	(	wait_type NVARCHAR(60) NOT NULL);

	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'BROKER_EVENTHANDLER');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'BROKER_RECEIVE_WAITFOR');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'BROKER_TASK_STOP');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'BROKER_TO_FLUSH');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'BROKER_TRANSMITTER');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'CHECKPOINT_QUEUE');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'CHKPT');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'CLR_AUTO_EVENT');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'CLR_MANUAL_EVENT');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'CLR_SEMAPHORE');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'DBMIRROR_DBM_EVENT');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'DBMIRROR_EVENTS_QUEUE');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'DBMIRROR_WORKER_QUEUE');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'DBMIRRORING_CMD');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'DIRTY_PAGE_POLL');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'DISPATCHER_QUEUE_SEMAPHORE');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'EXECSYNC');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'FSAGENT');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'FT_IFTS_SCHEDULER_IDLE_WAIT');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'FT_IFTSHC_MUTEX');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'HADR_CLUSAPI_CALL');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'HADR_FILESTREAM_IOMGR_IOCOMPLETION');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'HADR_LOGCAPTURE_WAIT');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'HADR_NOTIFICATION_DEQUEUE');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'HADR_TIMER_TASK');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'HADR_WORK_QUEUE');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'KSOURCE_WAKEUP');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'LAZYWRITER_SLEEP');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'LOGMGR_QUEUE');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'ONDEMAND_TASK_QUEUE');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'PWAIT_ALL_COMPONENTS_INITIALIZED');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'REQUEST_FOR_DEADLOCK_SEARCH');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'RESOURCE_QUEUE');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SERVER_IDLE_CHECK');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SLEEP_BPOOL_FLUSH');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SLEEP_DBSTARTUP');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SLEEP_DCOMSTARTUP');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SLEEP_MASTERDBREADY');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SLEEP_MASTERMDREADY');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SLEEP_MASTERUPGRADED');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SLEEP_MSDBSTARTUP');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SLEEP_SYSTEMTASK');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SLEEP_TASK');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SLEEP_TEMPDBSTARTUP');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SNI_HTTP_ACCEPT');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SP_SERVER_DIAGNOSTICS_SLEEP');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SQLTRACE_BUFFER_FLUSH');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'SQLTRACE_WAIT_ENTRIES');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'WAIT_FOR_RESULTS');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'WAIT_XTP_CKPT_CLOSE');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'WAIT_XTP_HOST_WAIT');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'WAITFOR');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'WAITFOR_TASKSHUTDOWN');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'XE_DISPATCHER_JOIN');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'XE_DISPATCHER_WAIT');
	INSERT INTO #WaitTypeIrrelevant (wait_type) VALUES (N'XE_TIMER_EVENT');
END TRY
BEGIN CATCH END CATCH;
GO

SELECT	at = GETDATE(), *
INTO tempdb.dbo.sys_dm_os_wait_stats
FROM	sys.dm_os_wait_stats dows

WAITFOR DELAY '00:00:15';

INSERT INTO tempdb.dbo.sys_dm_os_wait_stats
(	at
,	wait_type
,	waiting_tasks_count
,	wait_time_ms
,	max_wait_time_ms
,	signal_wait_time_ms)
SELECT
	at = GETDATE()
,	dows.wait_type
,	dows.waiting_tasks_count
,	dows.wait_time_ms
,	dows.max_wait_time_ms
,	dows.signal_wait_time_ms
FROM
	sys.dm_os_wait_stats dows
WHERE
	NOT EXISTS ( SELECT NULL FROM #WaitTypeIrrelevant _wti WHERE dows.wait_type = _wti.wait_type ); 

GO

WHILE 1 = 1
BEGIN
	WAITFOR DELAY '00:00:15';

	BEGIN TRANSACTION;
	
	DELETE FROM	t
	FROM
		tempdb.dbo.sys_dm_os_wait_stats AS t WITH (TABLOCK)
	WHERE
		t.at =
			(	SELECT	MIN(_t.at) 
		 		FROM	tempdb.dbo.sys_dm_os_wait_stats AS _t
			)
	;
	
	INSERT INTO tempdb.dbo.sys_dm_os_wait_stats
	(	at
	,	wait_type
	,	waiting_tasks_count
	,	wait_time_ms
	,	max_wait_time_ms
	,	signal_wait_time_ms)
	SELECT
		at = GETDATE()
	,	dows.wait_type
	,	dows.waiting_tasks_count
	,	dows.wait_time_ms
	,	dows.max_wait_time_ms
	,	dows.signal_wait_time_ms
	FROM
		sys.dm_os_wait_stats dows
	WHERE
		NOT EXISTS ( SELECT NULL FROM #WaitTypeIrrelevant _wti WHERE dows.wait_type = _wti.wait_type );
	 	
	COMMIT TRANSACTION;	
END;