--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'%<SystemHealth>%') 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: <Tim Radney>
-- Create date: <Feb 4th 2016>
-- Description: <Capture Page Life Expectancy>
--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: <Tim Radney>
-- Create date: <Feb 4th 2016>
-- Description: <Capture File Stats>
--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: <Tim Radney>
-- Create date: <Feb 4th 2016>
-- Description: <Capture Page Life Expectancy>
--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: <Tim Radney>
-- Create date: <Feb 4th 2016>
-- Description: <Capture Waiting Tasks>
--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: <Tim Radney>
-- Create date: <Feb 4th 2016>
-- Description: <Capture Wait Stats>
--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