Erland Sommarskogs wait_stats_monitor

CREATE PROCEDURE wait_stats_monitor AS
SET NOCOUNT ON
DECLARE @latest datetime,
 @now    datetime
SELECT @now = getdate()
IF object_id('waitstats') IS NULL
 BEGIN
 CREATE TABLE waitstats (
 sample_time       datetime     NOT NULL,
 wait_type         nvarchar(60) NOT NULL,
 sample_length_sec int          NULL,
 delta_tasks_count bigint       NULL,
 delta_wait_time   bigint       NULL,
 delta_signal      bigint       NULL,
 max_wait_time_ms  bigint       NOT NULL,
 tot_tasks_count   bigint       NOT NULL,
 tot_wait_time     bigint       NOT NULL,
 tot_signal        bigint       NOT NULL,
 CONSTRAINT pk_waitstats PRIMARY KEY (wait_type, sample_time)
 )
CREATE INDEX sample_time ON waitstats (sample_time)
 END
IF NOT EXISTS (SELECT * FROM waitstats)
 BEGIN
 INSERT waitstats (sample_time, wait_type, max_wait_time_ms,
 tot_tasks_count, tot_wait_time, tot_signal)
 SELECT @now, wait_type, max_wait_time_ms,
 waiting_tasks_count, wait_time_ms, signal_wait_time_ms
 FROM   sys.dm_os_wait_stats
 END
 ELSE
 BEGIN
 SELECT @latest = MAX(sample_time) FROM waitstats
INSERT waitstats (sample_time, wait_type, sample_length_sec,
 delta_tasks_count,
 delta_wait_time,
 delta_signal,
 max_wait_time_ms, tot_tasks_count, tot_wait_time,
 tot_signal)
 SELECT @now, ws.wait_type, datediff(ss, m.sample_time, @now),
 ws.waiting_tasks_count - m.tot_tasks_count,
 ws.wait_time_ms        - m.tot_wait_time,
 ws.signal_wait_time_ms - m.tot_signal,
 ws.max_wait_time_ms, ws.waiting_tasks_count, ws.wait_time_ms,
 ws.signal_wait_time_ms
 FROM   sys.dm_os_wait_stats ws
 CROSS  APPLY (SELECT TOP 1 *
 FROM   waitstats m
 WHERE  m.wait_type = ws.wait_type
 ORDER  BY sample_time DESC) m
 WHERE  ws.waiting_tasks_count <> m.tot_tasks_count OR
 ws.wait_time_ms        <> m.tot_wait_time   OR
 ws.signal_wait_time_ms <> m.tot_signal
 END
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *