Wait stats – rozdiel za N sekúnd

/* DeadlockCafe | Wait stats – rozdiel za N sekúnd
   Účel:  Dva odbery sys.dm_os_wait_stats s pauzou medzi nimi: na čo server čaká práve teraz, nie od posledného reštartu. S podielom, priemerným čakaním a signal vs. resource.
   Režim: READ ONLY (čaká N sekúnd) | SQL Server 2016 – 2022, on-premises
*/
SET NOCOUNT ON;

DECLARE @Seconds int = {{Seconds:30|Dĺžka merania v sekundách (1 až 3600)}};

IF @Seconds < 1 OR @Seconds > 3600
BEGIN
    RAISERROR(N'Seconds musí byť od 1 do 3600.', 16, 1);
    RETURN;
END;

-- WAITFOR DELAY potrebuje text hh:mm:ss
DECLARE @delay char(8) = CONVERT(char(8), DATEADD(SECOND, @Seconds, CONVERT(datetime, 0)), 108);

IF OBJECT_ID(N'tempdb..#ignore') IS NOT NULL DROP TABLE #ignore;
IF OBJECT_ID(N'tempdb..#w1') IS NOT NULL DROP TABLE #w1;
IF OBJECT_ID(N'tempdb..#w2') IS NOT NULL DROP TABLE #w2;

-- benígne / idle čakania (z receptu Wait stats + doplnky)
CREATE TABLE #ignore (wait_type nvarchar(60) NOT NULL PRIMARY KEY);
INSERT INTO #ignore (wait_type) VALUES
 (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'DIRTY_PAGE_POLL'), (N'DISPATCHER_QUEUE_SEMAPHORE'), (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'MEMORY_ALLOCATION_EXT'), (N'ONDEMAND_TASK_QUEUE'), (N'PARALLEL_REDO_DRAIN_WORKER'), (N'PARALLEL_REDO_LOG_CACHE'),
 (N'PARALLEL_REDO_TRAN_LIST'), (N'PARALLEL_REDO_WORKER_SYNC'), (N'PARALLEL_REDO_WORKER_WAIT_WORK'), (N'PREEMPTIVE_XE_GETTARGETSTATE'),
 (N'REDO_THREAD_PENDING_WORK'), (N'REQUEST_FOR_DEADLOCK_SEARCH'), (N'RESOURCE_QUEUE'), (N'SERVER_IDLE_CHECK'),
 (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'SOS_WORK_DISPATCHER'), (N'SP_SERVER_DIAGNOSTICS_SLEEP'), (N'SQLTRACE_BUFFER_FLUSH'), (N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'),
 (N'SQLTRACE_WAIT_ENTRIES'), (N'UCS_SESSION_REGISTRATION'), (N'WAIT_FOR_RESULTS'), (N'WAIT_XTP_CKPT_CLOSE'),
 (N'WAIT_XTP_HOST_WAIT'), (N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'), (N'WAIT_XTP_RECOVERY'), (N'WAITFOR'), (N'WAITFOR_TASKSHUTDOWN'),
 (N'XE_BUFFERMGR_ALLPROCESSED_EVENT'), (N'XE_DISPATCHER_JOIN'), (N'XE_DISPATCHER_WAIT'), (N'XE_LIVE_TARGET_TVF'), (N'XE_TIMER_EVENT');

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #w1
FROM sys.dm_os_wait_stats;

DECLARE @t0 datetime2(3) = SYSDATETIME();
WAITFOR DELAY @delay;

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #w2
FROM sys.dm_os_wait_stats;

DECLARE @elapsed_ms int = DATEDIFF(MILLISECOND, @t0, SYSDATETIME());

IF OBJECT_ID(N'tempdb..#d') IS NOT NULL DROP TABLE #d;

SELECT w2.wait_type,
       w2.waiting_tasks_count - ISNULL(w1.waiting_tasks_count, 0) AS tasks,
       w2.wait_time_ms        - ISNULL(w1.wait_time_ms, 0)        AS wait_ms,
       w2.signal_wait_time_ms - ISNULL(w1.signal_wait_time_ms, 0) AS signal_ms
INTO #d
FROM #w2 AS w2
LEFT JOIN #w1 AS w1 ON w1.wait_type = w2.wait_type
WHERE NOT EXISTS (SELECT 1 FROM #ignore AS i WHERE i.wait_type = w2.wait_type)
  AND w2.wait_type NOT LIKE N'PWAIT[_]%'
  AND w2.wait_type NOT LIKE N'QDS[_]%';

-- 1) súhrn merania
SELECT @Seconds                                                      AS requested_seconds,
       CAST(@elapsed_ms / 1000.0 AS decimal(18,1))                   AS measured_seconds,
       CAST(SUM(wait_ms) / 1000.0 AS decimal(18,1))                  AS total_wait_s,
       CAST(100.0 * SUM(signal_ms) / NULLIF(SUM(wait_ms), 0) AS decimal(5,1)) AS signal_wait_pct
FROM #d
WHERE wait_ms > 0;

-- 2) top čakania za meranie
SELECT TOP (25)
       wait_type,
       tasks                                                         AS waiting_tasks,
       CAST(wait_ms / 1000.0 AS decimal(18,2))                       AS wait_s,
       CAST((wait_ms - signal_ms) / 1000.0 AS decimal(18,2))         AS resource_s,
       CAST(signal_ms / 1000.0 AS decimal(18,2))                     AS signal_s,
       CAST(100.0 * wait_ms / NULLIF(SUM(wait_ms) OVER (), 0) AS decimal(5,2)) AS pct,
       CAST(1.0 * wait_ms / NULLIF(tasks, 0) AS decimal(18,2))       AS avg_wait_ms,
       CAST(1.0 * wait_ms / NULLIF(@elapsed_ms, 0) AS decimal(18,2)) AS wait_s_per_s
FROM #d
WHERE wait_ms > 0
ORDER BY wait_ms DESC;