Code

-- Isolate top waitsWITH Waits AS(  SELECT    wait_type,    wait_time_ms / 1000. AS wait_time_s,    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn,    100. * signal_wait_time_ms / wait_time_ms as signal_pct  FROM sys.dm_os_wait_stats  WHERE wait_time_ms > 0    AND wait_type NOT LIKE N'%SLEEP%'    AND wait_type NOT LIKE N'%IDLE%'    AND wait_type NOT LIKE N'%QUEUE%'        AND wait_type NOT IN(  N'CLR_AUTO_EVENT'                         , N'REQUEST_FOR_DEADLOCK_SEARCH'                         , N'SQLTRACE_BUFFER_FLUSH'                         /* filter out additional irrelevant waits */ ))SELECT  W1.wait_type,   CAST(W1.wait_time_s AS NUMERIC(12, 2)) AS wait_time_s,  CAST(W1.pct AS NUMERIC(5, 2)) AS pct,  CAST(SUM(W2.pct) AS NUMERIC(5, 2)) AS running_pct,  CAST(W1.signal_pct AS NUMERIC(5, 2)) AS signal_pctFROM Waits AS W1  JOIN Waits AS W2    ON W2.rn <= W1.rnGROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct, W1.signal_pctHAVING SUM(W2.pct) - W1.pct < 80 -- percentage threshold    OR W1.rn <= 5ORDER BY W1.rn;GO

 1.从系统最后一次重启或计数器清空，该等待类型的总等待时间（以秒为单位）

2.该类型的等待时间占等待时间的百分比

3.从最重量级的等待类型到当前等待类型的连续百分比。

4.信号等待时间占等待时间的百分比，、（记住，wait_time_ms包含signal_wait_time_ms）

收集等待信息

-- Create the WaitStats tableUSE Performance;IF OBJECT_ID('dbo.WaitStats', 'U') IS NOT NULL DROP TABLE dbo.WaitStats;CREATE TABLE dbo.WaitStats(  dt                  DATETIME     NOT NULL DEFAULT (CURRENT_TIMESTAMP),  wait_type           NVARCHAR(60) 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);CREATE UNIQUE CLUSTERED INDEX idx_dt_type ON dbo.WaitStats(dt, wait_type);CREATE INDEX idx_type_dt ON dbo.WaitStats(wait_type, dt);-- Load waitstats data on regular intervalsINSERT INTO Performance.dbo.WaitStats    (wait_type, waiting_tasks_count, wait_time_ms,     max_wait_time_ms, signal_wait_time_ms)  SELECT    wait_type, waiting_tasks_count, wait_time_ms,    max_wait_time_ms, signal_wait_time_ms  FROM sys.dm_os_wait_stats  WHERE wait_type NOT IN (N'MISCELLANEOUS');