--Capture wait stats over time --Exist if database doesn't exist. Adjust the database name if needed. IF DB_ID('sqlmaint') IS NULL RAISERROR ('The database doesn''t exist. Terminating script.', 20, 1) WITH LOG GO --Adjust the database name if needed. USE sqlmaint SET NOCOUNT ON ------------------------------------------------------------------------------------------------------------------------------ --Drop tables and schema if they exists ------------------------------------------------------------------------------------------------------------------------------ IF OBJECT_ID('ws.the_log') IS NOT NULL DROP TABLE ws.the_log IF OBJECT_ID('ws.types') IS NOT NULL DROP TABLE ws.types IF OBJECT_ID('ws.ignores') IS NOT NULL DROP TABLE ws.ignores IF OBJECT_ID('ws.config') IS NOT NULL DROP TABLE ws.config IF SCHEMA_ID('ws') IS NOT NULL DROP SCHEMA ws GO ------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------ --Create schema ------------------------------------------------------------------------------------------------------------------------------ CREATE SCHEMA ws GO ------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------ --Create tables ------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------- --ignores CREATE TABLE ws.ignores ( wait_type_name nvarchar(60) CONSTRAINT ignores_PK PRIMARY KEY CLUSTERED NOT NULL ,comment nvarchar(500) NULL) ----------------------------------------- --types CREATE TABLE ws.types( wait_type_id smallint IDENTITY(1, 1) CONSTRAINT types_PK PRIMARY KEY CLUSTERED NOT NULL ,wait_type_name nvarchar(60) CONSTRAINT types__wait_type_name_UQ UNIQUE NONCLUSTERED NOT NULL ) ----------------------------------------- --the_log CREATE TABLE ws.the_log ( wait_type_id smallint NOT NULL REFERENCES ws.types(wait_type_id) ,capture_ts datetime2(0) NOT NULL ,waiting_tasks_count bigint NOT NULL ,wait_time_ms bigint NOT NULL ,max_wait_time_ms bigint NOT NULL ,signal_wait_time_ms bigint NOT NULL, CONSTRAINT the_log_PK PRIMARY KEY CLUSTERED (capture_ts, wait_type_id) ) --Turn on row compression, for captured_wait_stats, if available (Enterprise Edition or 2016 SP1) IF SERVERPROPERTY('EngineEdition') = 3 --Enterprise Edition OR ( --At least 2016 SP1 LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(128)), 2) = '13' AND CAST(SERVERPROPERTY('ProductLevel') AS nvarchar(128)) = 'SP1' ) BEGIN ALTER TABLE ws.the_log REBUILD WITH(DATA_COMPRESSION = ROW) END ----------------------------------------- --config CREATE TABLE ws.config(dense_days_to_keep int, sparse_days_to_keep int) ------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------ --Populate config table ------------------------------------------------------------------------------------------------------------------------------ INSERT INTO ws.config (dense_days_to_keep, sparse_days_to_keep) VALUES(3, 365) ------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------ --Populate ignore list. Based on Glenn Berry's excellent diag scripts, as of 2017-04-05 (the SQL Server 2016 version) -- http://www.sqlskills.com/blogs/glenn/category/dmv-queries/ ------------------------------------------------------------------------------------------------------------------------------ INSERT INTO ws.ignores (wait_type_name) VALUES ('BROKER_EVENTHANDLER'), ('BROKER_RECEIVE_WAITFOR'), ('BROKER_TASK_STOP'), ('BROKER_TO_FLUSH'), ('BROKER_TRANSMITTER'), ('CHECKPOINT_QUEUE'), ('CHKPT'), ('CLR_AUTO_EVENT'), ('CLR_MANUAL_EVENT'), ('CLR_SEMAPHORE'), ('DBMIRROR_DBM_EVENT'), ('DBMIRROR_EVENTS_QUEUE'), ('DBMIRROR_WORKER_QUEUE'), ('DBMIRRORING_CMD'), ('DIRTY_PAGE_POLL'), ('DISPATCHER_QUEUE_SEMAPHORE'), ('EXECSYNC'), ('FSAGENT'), ('FT_IFTS_SCHEDULER_IDLE_WAIT'), ('FT_IFTSHC_MUTEX'), ('HADR_CLUSAPI_CALL'), ('HADR_FILESTREAM_IOMGR_IOCOMPLETION'), ('HADR_LOGCAPTURE_WAIT'), ('HADR_NOTIFICATION_DEQUEUE'), ('HADR_TIMER_TASK'), ('HADR_WORK_QUEUE'), ('KSOURCE_WAKEUP'), ('LAZYWRITER_SLEEP'), ('LOGMGR_QUEUE'), ('MEMORY_ALLOCATION_EXT'), ('ONDEMAND_TASK_QUEUE'), ('PREEMPTIVE_OS_AUTHENTICATIONOPS'), ('PREEMPTIVE_OS_COMOPS'), ('PREEMPTIVE_OS_CRYPTOPS'), ('PREEMPTIVE_OS_DEVICEOPS'), ('PREEMPTIVE_OS_FILEOPS'), ('PREEMPTIVE_OS_GENERICOPS'), ('PREEMPTIVE_OS_LIBRARYOPS'), ('PREEMPTIVE_OS_PIPEOPS'), ('PREEMPTIVE_OS_QUERYREGISTRY'), ('PREEMPTIVE_OS_VERIFYTRUST'), ('PREEMPTIVE_OS_WRITEFILE'), ('PREEMPTIVE_XE_CALLBACKEXECUTE'), ('PREEMPTIVE_XE_DISPATCHER'), ('PREEMPTIVE_XE_GETTARGETSTATE'), ('PREEMPTIVE_XE_SESSIONCOMMIT'), ('PREEMPTIVE_XE_TARGETFINALIZE'), ('PREEMPTIVE_XE_TARGETINIT'), ('PWAIT_ALL_COMPONENTS_INITIALIZED'), ('PWAIT_DIRECTLOGCONSUMER_GETNEXT'), ('QDS_ASYNC_QUEUE'), ('QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'), ('QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'), ('REQUEST_FOR_DEADLOCK_SEARCH'), ('RESOURCE_QUEUE'), ('SERVER_IDLE_CHECK'), ('SLEEP_BPOOL_FLUSH'), ('SLEEP_DBSTARTUP'), ('SLEEP_DCOMSTARTUP'), ('SLEEP_MASTERDBREADY'), ('SLEEP_MASTERMDREADY'), ('SLEEP_MASTERUPGRADED'), ('SLEEP_MSDBSTARTUP'), ('SLEEP_SYSTEMTASK'), ('SLEEP_TASK'), ('SLEEP_TEMPDBSTARTUP'), ('SP_SERVER_DIAGNOSTICS_SLEEP'), ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP'), ('SQLTRACE_WAIT_ENTRIES'), ('WAIT_FOR_RESULTS'), ('WAIT_XTP_CKPT_CLOSE'), ('WAIT_XTP_HOST_WAIT'), ('WAIT_XTP_OFFLINE_CKPT_NEW_LOG'), ('WAIT_XTP_RECOVERY'), ('WAITFOR'), ('WAITFOR_TASKSHUTDOWN'), ('XE_BUFFERMGR_ALLPROCESSED_EVENT'), ('XE_DISPATCHER_JOIN'), ('XE_DISPATCHER_WAIT'), ('XE_LIVE_TARGET_TVF'), ('XE_TIMER_EVENT') ------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------ --Populate the wait types into our table --, so we have an id for the wait stats instead of a name for each log entry --We also have the ignored ones, but we do the ignore when we capture the value. --This way the ignore_waits table is the "active" config for the ignore list. ------------------------------------------------------------------------------------------------------------------------------ INSERT INTO ws.types(wait_type_name) SELECT wait_type FROM sys.dm_os_wait_stats WHERE wait_type NOT IN (SELECT wait_type_name FROM ws.types) ------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------ --INSERT INTO captured_wait_stats --Below should go in an Agent job, scheduled at the frequency that you want. --For instance every minute ------------------------------------------------------------------------------------------------------------------------------ INSERT INTO ws.the_log SELECT wt.wait_type_id ,SYSDATETIME() ,ws.waiting_tasks_count ,ws.wait_time_ms ,ws.max_wait_time_ms ,ws.signal_wait_time_ms FROM sys.dm_os_wait_stats AS ws INNER JOIN ws.types AS wt ON ws.wait_type = wt.wait_type_name WHERE NOT EXISTS (SELECT * FROM ws.ignores AS iw WHERE iw.wait_type_name = wt.wait_type_name ) ------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------ --Delete old data --This should go in an Agent job, scheduled once per day (or more frequently, but not less) ------------------------------------------------------------------------------------------------------------------------------ DECLARE @dense_days_to_keep int, @sparse_days_to_keep int, @dense_cut_off_date datetime2(0), @sparse_cut_off_date datetime2(0) SELECT @dense_days_to_keep = dense_days_to_keep, @sparse_days_to_keep = sparse_days_to_keep FROM ws.config SET @dense_cut_off_date = DATEADD(DAY, @dense_days_to_keep * -1, SYSDATETIME()) SET @sparse_cut_off_date = DATEADD(DAY, @sparse_days_to_keep * -1, SYSDATETIME()) --Delete old rows (typically older than 365 days) DELETE FROM ws.the_log WHERE capture_ts < @sparse_cut_off_date --For rows more recent than a year, but older than 3 days, we only keep one row per day DELETE FROM ws.the_log WHERE capture_ts NOT IN(SELECT MIN(capture_ts) FROM ws.the_log GROUP BY CAST(capture_ts AS date)) AND capture_ts < @dense_cut_off_date ------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------ --Is there any data there? ------------------------------------------------------------------------------------------------------------------------------ SELECT t.wait_type_name, l.capture_ts, l.waiting_tasks_count, l.wait_time_ms, l.max_wait_time_ms, l.signal_wait_time_ms FROM ws.the_log AS l INNER JOIN ws.types AS t ON l.wait_type_id = t.wait_type_id WHERE wait_time_ms > 0 ORDER BY wait_type_name ASC, capture_ts DESC ------------------------------------------------------------------------------------------------------------------------------ --How did wait stats evolve over time? ------------------------------------------------------------------------------------------------------------------------------ DECLARE @wait_name nvarchar(60) = 'LATCH_SH' SELECT t.wait_type_name ,l.capture_ts ,l.wait_time_ms ,l.wait_time_ms - LAG(l.wait_time_ms, 1, NULL) OVER (PARTITION BY t.wait_type_name ORDER BY l.capture_ts) AS wait_time_ms_diff ,l.signal_wait_time_ms ,l.signal_wait_time_ms - LAG(l.signal_wait_time_ms, 1, NULL) OVER (PARTITION BY t.wait_type_name ORDER BY l.capture_ts) AS signal_wait_time_ms_diff FROM ws.the_log AS l INNER JOIN ws.types AS t ON l.wait_type_id = t.wait_type_id -- WHERE t.wait_type_name = @wait_name ORDER BY wait_type_name, capture_ts