SQL Server 執行緒等待統計收集與分析：建立監控作業識別效能瓶頸

sys.dm_os_wait_stats 是診斷 SQL Server 效能的金鑰 DMV，記錄所有執行緒等待類型、次數、總等待時間、最大等待時間及訊號等待時間。但服務重啟會歸零，需持久化收集才能趨勢分析。

1. 建立持久化資料表

USE AdventureWorks;
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);

2. 建立 SQL Server Agent 作業定時收集（如每 5 分鐘）

INSERT 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;

3. 計算區間等待增量（自連接 + ROW_NUMBER）

CREATE FUNCTION dbo.IntervalWaits (@fromdt DATETIME, @todt DATETIME)
RETURNS TABLE AS RETURN
WITH Waits AS (
    SELECT dt, wait_type, wait_time_ms,
           ROW_NUMBER() OVER (PARTITION BY wait_type ORDER BY dt) AS rn
    FROM dbo.WaitStats
)
SELECT Prv.wait_type, Prv.dt AS start_time,
       CAST((Cur.wait_time_ms - Prv.wait_time_ms) / 1000. AS NUMERIC(12,2)) AS interval_wait_s
FROM Waits AS Cur
JOIN Waits AS Prv ON Cur.wait_type = Prv.wait_type AND Cur.rn = Prv.rn + 1
WHERE Prv.dt >= @fromdt AND Prv.dt < DATEADD(day, 1, @todt);

4. 包裝成檢視表供報表查詢

CREATE VIEW dbo.IntervalWaitsSample AS
SELECT wait_type, start_time, interval_wait_s
FROM dbo.IntervalWaits('20090212', '20090215') AS F;

後續可匯出至 Excel 繪製時間序列圖，直觀識別繁忙時段與高等待類型（如 PAGEIOLATCH_SH、LCK_M_X、WRITELOG），指導索引優化、鎖調整或硬體升級決策。