--First Create a SQLskillsMonitor blank database USE [SQLskillsMonitor] GO /****** Object: Table [dbo].[CPUhistory] Script Date: 4/2/2018 11:56:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[CPUhistory]( [SQL Server Process CPU Utilization] [int] NULL, [System Idle Process] [int] NULL, [Other Process CPU Utilization] [int] NULL, [Event Time] [datetime] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DBInventory] Script Date: 4/2/2018 11:56:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DBInventory]( [ServerName] [varchar](100) NULL, [DatabaseName] [varchar](100) NULL, [LogicalFileName] [sysname] NOT NULL, [FileGroupName] [varchar](100) NULL, [Growth] [varchar](25) NULL, [FileSizeMB] [int] NULL, [UsedSizeMB] [int] NULL, [FreeSpaceMB] [int] NULL, [PhysicalFileName] [nvarchar](520) NULL, [Status] [sysname] NOT NULL, [Updateability] [sysname] NOT NULL, [RecoveryMode] [sysname] NOT NULL, [FreeSpacePct] [numeric](5, 2) NULL, [CAPTURE_DATE] [datetime] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[FileStats] Script Date: 4/2/2018 11:56:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[FileStats]( [DB] [nvarchar](128) NULL, [Drive] [nvarchar](2) NULL, [type_desc] [nvarchar](60) NULL, [Reads] [bigint] NULL, [Writes] [bigint] NULL, [ReadLatency(ms)] [bigint] NULL, [WriteLatency(ms)] [bigint] NULL, [AvgBPerRead] [bigint] NULL, [AvgBPerWrite] [bigint] NULL, [physical_name] [nvarchar](260) NOT NULL, [CAPTURE_DATE] [datetime] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[PLE] Script Date: 4/2/2018 11:56:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[PLE]( [INSTANCE] [nvarchar](128) NULL, [CAPTURE_DATE] [datetime] NOT NULL, [OBJECT_NAME] [nchar](128) NOT NULL, [COUNTER_NAME] [nchar](128) NOT NULL, [UPTIME_MIN] [int] NULL, [PLE_SECS] [bigint] NOT NULL, [PLE_MINS] [bigint] NULL, [PLE_HOURS] [bigint] NULL, [PLE_DAYS] [bigint] NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[WaitStats] Script Date: 4/2/2018 11:56:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[WaitStats]( [WaitType] [nvarchar](60) NOT NULL, [Wait_S] [decimal](16, 2) NULL, [Resource_S] [decimal](16, 2) NULL, [Signal_S] [decimal](16, 2) NULL, [WaitCount] [bigint] NULL, [Percentage] [decimal](5, 2) NULL, [AvgWait_S] [decimal](16, 4) NULL, [AvgRes_S] [decimal](16, 4) NULL, [AvgSig_S] [decimal](16, 4) NULL, [CAPTURE_DATE] [datetime] NOT NULL ) ON [PRIMARY] GO /****** Object: StoredProcedure [dbo].[Capture_CPU_Time] Script Date: 4/2/2018 11:56:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /*============================================================================ ------------------------------------------------------------------------------ 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. ============================================================================*/ -- ============================================= CREATE PROCEDURE [dbo].[Capture_CPU_Time] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Get CPU Utilization History for last 256 minutes (in one minute intervals) (Query 36) (CPU Utilization History) -- This version works with SQL Server 2012 DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK)); INSERT INTO [dbo].[CPUHistory] ([SQL Server Process CPU Utilization] ,[System Idle Process] ,[Other Process CPU Utilization] ,[Event Time]) SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization], SystemIdle AS [System Idle Process], 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp] FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] FROM sys.dm_os_ring_buffers WITH (NOLOCK) WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE N'%%') AS x) AS y ORDER BY record_id DESC OPTION (RECOMPILE) END GO /****** Object: StoredProcedure [dbo].[Capture_DBinventory] Script Date: 4/2/2018 11:56:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: --Copyright 2016 - SQLskills.com -- ============================================= CREATE PROCEDURE [dbo].[Capture_DBinventory] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; /* Find free space for each database data and log file http://www.mssqltips.com/tip.asp?tip=1426 */ DECLARE @DBInfo TABLE ( ServerName VARCHAR(100), DatabaseName VARCHAR(100), FileSizeMB INT, LogicalFileName sysname, PhysicalFileName NVARCHAR(520), Status sysname, Updateability sysname, RecoveryMode sysname, FreeSpaceMB INT, FreeSpacePct numeric(5,2), FreeSpacePages INT, CAPTURE_DATE datetime, FileGroupName VARCHAR(100), growth VARCHAR(25)) DECLARE @command VARCHAR(6000) SELECT @command = 'Use [' + '?' + '] SELECT @@servername as ServerName, ' + '''' + '?' + '''' + ' AS DatabaseName, CAST(f.size/128.0 AS int) AS FileSize, f.name AS LogicalFileName, f.filename AS PhysicalFileName, CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status, CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability, CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode, CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB, 100 * (CAST (((f.size/128.0 -CAST(FILEPROPERTY(f.name, ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(f.size/128.0)) AS numeric(4,2))) AS FreeSpacePct, GETDATE() as CAPTURE_DATE, fg.groupname ,''growth'' = (case f.status & 0x100000 when 0x100000 then convert(nvarchar(15), growth) + N''%'' else convert(nvarchar(15), (convert (bigint, growth) * 8)/1024) + N'' MB'' end) FROM dbo.sysfiles f LEFT JOIN dbo.sysfilegroups fg ON f.groupid = fg.groupid' INSERT INTO @DBInfo (ServerName, DatabaseName, FileSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpaceMB, FreeSpacePct, CAPTURE_DATE, FileGroupName ,Growth ) EXEC sp_MSForEachDB @command INSERT INTO SQLskillsMonitor.dbo.DBinventory ([ServerName],[DatabaseName],[LogicalFileName],[FileGroupName],[Growth],[FileSizeMB],[UsedSizeMB], [FreeSpaceMB],[PhysicalFileName],[Status],[Updateability],[RecoveryMode],[FreeSpacePct],[CAPTURE_DATE]) SELECT ServerName, DatabaseName, LogicalFileName, FileGroupName, Growth, FileSizeMB, (FileSizeMB - FreeSpaceMB) UsedSizeMB, FreeSpaceMB, PhysicalFileName, Status, Updateability, RecoveryMode, FreeSpacePct, CAPTURE_DATE FROM @DBInfo --WHERE databaseName like 'NFCBOL'--LogicalFileName like 'TP%' ORDER BY ServerName, DatabaseName, FileGroupName END GO /****** Object: StoredProcedure [dbo].[Capture_FileStats] Script Date: 4/2/2018 11:56:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: --Copyright 2016 - SQLskills.com --Original code Written by Paul S. Randal, SQLskills.com /*============================================================================ ------------------------------------------------------------------------------ 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. ============================================================================*/ -- ============================================= CREATE PROCEDURE [dbo].[Capture_FileStats] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF EXISTS (SELECT * FROM [tempdb].[sys].[objects] WHERE [name] = N'##SQLskillsFileStats1') DROP TABLE [##SQLskillsFileStats1]; IF EXISTS (SELECT * FROM [tempdb].[sys].[objects] WHERE [name] = N'##SQLskillsFileStats2') DROP TABLE [##SQLskillsFileStats2]; SELECT [database_id], [file_id], [num_of_reads], [io_stall_read_ms], [num_of_writes], [io_stall_write_ms], [io_stall], [num_of_bytes_read], [num_of_bytes_written], [file_handle] INTO ##SQLskillsFileStats1 FROM sys.dm_io_virtual_file_stats (NULL, NULL); WAITFOR DELAY '00:59:00'; SELECT [database_id], [file_id], [num_of_reads], [io_stall_read_ms], [num_of_writes], [io_stall_write_ms], [io_stall], [num_of_bytes_read], [num_of_bytes_written], [file_handle] INTO ##SQLskillsFileStats2 FROM sys.dm_io_virtual_file_stats (NULL, NULL); WITH [DiffLatencies] AS (SELECT -- Files that weren't in the first snapshot [ts2].[database_id], [ts2].[file_id], [ts2].[num_of_reads], [ts2].[io_stall_read_ms], [ts2].[num_of_writes], [ts2].[io_stall_write_ms], [ts2].[io_stall], [ts2].[num_of_bytes_read], [ts2].[num_of_bytes_written] FROM [##SQLskillsFileStats2] AS [ts2] LEFT OUTER JOIN [##SQLskillsFileStats1] AS [ts1] ON [ts2].[file_handle] = [ts1].[file_handle] WHERE [ts1].[file_handle] IS NULL UNION SELECT -- Diff of latencies in both snapshots [ts2].[database_id], [ts2].[file_id], [ts2].[num_of_reads] - [ts1].[num_of_reads] AS [num_of_reads], [ts2].[io_stall_read_ms] - [ts1].[io_stall_read_ms] AS [io_stall_read_ms], [ts2].[num_of_writes] - [ts1].[num_of_writes] AS [num_of_writes], [ts2].[io_stall_write_ms] - [ts1].[io_stall_write_ms] AS [io_stall_write_ms], [ts2].[io_stall] - [ts1].[io_stall] AS [io_stall], [ts2].[num_of_bytes_read] - [ts1].[num_of_bytes_read] AS [num_of_bytes_read], [ts2].[num_of_bytes_written] - [ts1].[num_of_bytes_written] AS [num_of_bytes_written] FROM [##SQLskillsFileStats2] AS [ts2] LEFT OUTER JOIN [##SQLskillsFileStats1] AS [ts1] ON [ts2].[file_handle] = [ts1].[file_handle] WHERE [ts1].[file_handle] IS NOT NULL) INSERT INTO SQLskillsMonitor.dbo.FileStats ([DB], [Drive], [type_desc], [Reads], [Writes], [ReadLatency(ms)], [WriteLatency(ms)], [AvgBPerRead], [AvgBPerWrite], [physical_name], [CAPTURE_DATE]) SELECT DB_NAME ([vfs].[database_id]) AS [DB], LEFT ([mf].[physical_name], 2) AS [Drive], [mf].[type_desc], [num_of_reads] AS [Reads], [num_of_writes] AS [Writes], [ReadLatency(ms)] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END, [WriteLatency(ms)] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END, /*[Latency] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,*/ [AvgBPerRead] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END, [AvgBPerWrite] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END, /*[AvgBPerTransfer] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes])) END,*/ [mf].[physical_name], getdate() AS [CAPTURE_DATE] FROM [DiffLatencies] AS [vfs] JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id] -- ORDER BY [ReadLatency(ms)] DESC ORDER BY [WriteLatency(ms)] DESC; -- Cleanup IF EXISTS (SELECT * FROM [tempdb].[sys].[objects] WHERE [name] = N'##SQLskillsFileStats1') DROP TABLE [##SQLskillsFileStats1]; IF EXISTS (SELECT * FROM [tempdb].[sys].[objects] WHERE [name] = N'##SQLskillsFileStats2') DROP TABLE [##SQLskillsFileStats2]; END GO /****** Object: StoredProcedure [dbo].[Capture_PLE] Script Date: 4/2/2018 11:56:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: --Copyright 2016 - SQLskills.com -- ============================================= CREATE PROCEDURE [dbo].[Capture_PLE] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO SQLskillsMonitor.dbo.PLE ([INSTANCE], [CAPTURE_DATE], [OBJECT_NAME], [COUNTER_NAME], [UPTIME_MIN], [PLE_SECS], [PLE_MINS], [PLE_HOURS], [PLE_DAYS]) SELECT @@servername AS INSTANCE ,getdate() [CAPTURE_DATE] ,[OBJECT_NAME] ,[COUNTER_NAME] , UPTIME_MIN = CASE WHEN[counter_name]= 'Page life expectancy' THEN (SELECT DATEDIFF(MI, MAX(login_time),GETDATE()) FROM master.sys.sysprocesses WHERE cmd='LAZY WRITER') ELSE'' END , [cntr_value] AS PLE_SECS ,[cntr_value]/ 60 AS PLE_MINS ,[cntr_value]/ 3600 AS PLE_HOURS ,[cntr_value]/ 86400 AS PLE_DAYS FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND[counter_name] = 'Page life expectancy' END GO /****** Object: StoredProcedure [dbo].[Capture_WaitingTasks] Script Date: 4/2/2018 11:56:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: --Copyright 2016 - SQLskills.com --Original code Written by Paul S. Randal, SQLskills.com /*============================================================================ ------------------------------------------------------------------------------ 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. ============================================================================*/ -- ============================================= CREATE PROCEDURE [dbo].[Capture_WaitingTasks] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO SQLskillsMonitor.dbo.WaitingTasks ([session_id],[exec_context_id],[scheduler_id],[wait_duration_ms],[wait_type],[blocking_session_id],[resource_description],[Node ID],[text],[database_id],[query_plan],[cpu_time],[CAPTURE_DATE]) SELECT [owt].[session_id], [owt].[exec_context_id], [ot].[scheduler_id], [owt].[wait_duration_ms], [owt].[wait_type], [owt].[blocking_session_id], [owt].[resource_description], CASE [owt].[wait_type] WHEN N'CXPACKET' THEN RIGHT ([owt].[resource_description], CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1) ELSE NULL END AS [Node ID], --[es].[program_name], [est].text, [er].[database_id], [eqp].[query_plan], [er].[cpu_time], getdate() AS [CAPTURE_DATE] FROM sys.dm_os_waiting_tasks [owt] INNER JOIN sys.dm_os_tasks [ot] ON [owt].[waiting_task_address] = [ot].[task_address] INNER JOIN sys.dm_exec_sessions [es] ON [owt].[session_id] = [es].[session_id] INNER JOIN sys.dm_exec_requests [er] ON [es].[session_id] = [er].[session_id] OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est] OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp] WHERE [es].[is_user_process] = 1 ORDER BY [owt].[session_id], [owt].[exec_context_id]; END GO /****** Object: StoredProcedure [dbo].[Capture_WaitStats] Script Date: 4/2/2018 11:56:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: --Copyright 2016 - SQLskills.com --Original code Written by Paul S. Randal, SQLskills.com /*============================================================================ ------------------------------------------------------------------------------ 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. ============================================================================*/ -- ============================================= CREATE PROCEDURE [dbo].[Capture_WaitStats] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; 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]; 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; WAITFOR DELAY '00:59:00'; 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; 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') ) INSERT INTO SQLskillsMonitor.dbo.WaitStats ([WaitType],[Wait_S],[Resource_S],[Signal_S],[WaitCount],[Percentage],[AvgWait_S],[AvgRes_S],[AvgSig_S],[CAPTURE_DATE]) 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], Getdate() AS [CAPTURE_DATE] 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 -- 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]; END GO