USE [sqlnexus]
GO
CREATE TABLE dbo.Tmp_tbl_OS_WAIT_STATS
	(
	rownum bigint NULL,
	runtime datetime NULL,
	wait_type varchar(45) NULL,
	waiting_tasks_count bigint NULL,
	wait_time_ms bigint NULL,
	max_wait_time_ms bigint NULL,
	signal_wait_time_ms bigint NULL,
	wait_category  AS (case when [wait_type] like 'LCK%' then 'Locks' when [wait_type] like 'PAGEIO%' then 'Page I/O Latch' when [wait_type] like 'PAGELATCH%' then 'Page Latch (non-I/O)' when [wait_type] like 'LATCH%' then 'Latch (non-buffer)' when [wait_type] like 'IO_COMPLETION' then 'I/O Completion' when [wait_type] like 'ASYNC_NETWORK_IO' then 'Network I/O (client fetch)' when [wait_type]='SOS_RESERVEDMEMBLOCKLIST' OR [wait_type]='RESOURCE_SEMAPHORE' then 'Memory' when [wait_type] like 'RESOURCE_SEMAPHORE_%' OR [wait_type]='CMEMTHREAD' then 'Compilation' when [wait_type] like 'MSQL_XP' then 'XProc' when [wait_type] like 'WRITELOG' then 'Writelog' when [wait_type]IN ('CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK','SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH','BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER','TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE','DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES','SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK', 'CXPACKET') then 'IGNORABLE' else [wait_type] end)
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_tbl_OS_WAIT_STATS SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.tbl_OS_WAIT_STATS)
	 EXEC('INSERT INTO dbo.Tmp_tbl_OS_WAIT_STATS (rownum, runtime, wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms)
		SELECT rownum, runtime, wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms FROM dbo.tbl_OS_WAIT_STATS WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.tbl_OS_WAIT_STATS
GO
EXECUTE sp_rename N'dbo.Tmp_tbl_OS_WAIT_STATS', N'tbl_OS_WAIT_STATS', 'OBJECT' 
GO
CREATE CLUSTERED INDEX cidx ON dbo.tbl_OS_WAIT_STATS
	(
	runtime,
	wait_category
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
