要知道線程等待時間是制約SQL Server效率的重要原因，這一個随筆中将學習怎樣收集SQL Server中的線程等待時間，類型等資訊，這些資訊是進行資料庫優化的依據。
sys.dm_os_wait_stats
這是一個系統視圖，裡面存儲線程所遇到的所有的等待資訊，具體的列如下表
列名
資料類型
說明
Wait_type
Nvarchar(60)
等待類型名稱
waiting_tasks_count
Bigint
等待類型的等待數。該計數器在每開始一個等待時便會增加。
Wait_time_ms
該等待類型的總等待時間。
Max_wait_time_ms
該等待類型的最長等待時間。
Signal_wait_time_ms
正在等待的線程從收到信号通知到開始運作之間的時差。
要注意的是，這個視圖的資訊每次關閉SQL Server的時候都會自動清除，下次打開SQL Server的時候又會重新開始統計。
建立線程等待資訊表
如果想得到連續的資訊，在固定時間間隔内收集資訊比如一個小時一次，這樣就可以分析系統配置設定的等待時間，識别出繁忙時間段。這裡我們将這些資訊收集到一個資料表中儲存并進行分析。使用下面的語句建立一個表：
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);
建立job填充資料
要收集資訊最好是用一個job來定時地執行insert語句填充資料，下面介紹步驟
點選資料庫中的SQL Server Agent，展開，右擊jobs檔案夾，右擊建立。如圖1
在建立job界面，General标簽中填寫job名稱，描述，如圖2
點選Setps标簽，點選建立按鈕，在建立step界面内設定Step Name，Type，DataBase，Command等屬性，如下圖3。這些屬性一看就知道是要做什麼的，就不一一的解釋了。還要說的 是截圖是建好的job，如果你自己做的話對話框顯示的标題可能不一樣。
Command屬性是設定要執行的SQL語句或存儲過程等等，這裡設定如下的SQL Script:
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
點選Shedules标簽，設定job的執行計劃，這裡設定的是每天的每5分鐘執行一次如圖4
上述步驟包含了建立一個job的主要設定，其他的細節資訊沒有包含，遇到具體問題再具體分析吧。
收集等待資訊資料
過一段時間之後在表WaitStats中就會有一些資料，每隔5分鐘就會在這個表中寫入一些資料，這些資料會不斷的增加。這裡為了得到相鄰間隔之間線程等待時間的變化就要使用自連接配接，連接配接條件是等待類型相同，目前行号等于上一個的行号加上1，然後就可以用上一次等待時間減去這一次的等待時間得到這個變化值，下面使用一個函數來實作這個邏輯：
IF OBJECT_ID('dbo.IntervalWaits', 'IF') IS NOT NULL
DROP FUNCTION dbo.IntervalWaits;
GO
CREATE FUNCTION dbo.IntervalWaits
(@fromdt AS DATETIME, @todt AS 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
AND Prv.dt >= @fromdt
AND Prv.dt < DATE
ADD(day, 1, @todt)
GO
這個函數接受兩個參數，開始統計時間，結束統計時間，傳回等待變化的時間，并按照類型排序。調用這個函數如下：
SELECT wait_type, start_time, interval_wait_s
FROM dbo.IntervalWaits('20110509', '20110510') AS F
ORDER BY SUM(interval_wait_s) OVER(PARTITION BY wait_type) DESC,wait_type,start_time;
但是我們不能每次都去調用這個函數，是以可以吧這個查詢放在一個視圖裡面，外部隻需要使用視圖來查詢資料就可以了：
IF OBJECT_ID('dbo.IntervalWaitsSample', 'V') IS NOT NULL
DROP VIEW dbo.IntervalWaitsSample;
CREATE VIEW dbo.IntervalWaitsSample
SELECT wait_type, start_time, interval_wait_s
FROM dbo.IntervalWaits('20090212', '20090215')
AS F;
從視圖中查詢得到的資料就是我們要得到的資料。但是這些并不明顯，先寫到這裡，下一個随筆我将在EXECL中把這些資料制作成一個直方圖或者連線圖，橫軸是時間，縱軸是等待間隔時間。這樣就會更加直覺地看到在那些時間SQL Server的線程等待時間最長，也就是最繁忙的時候。
原文連結：http://www.cnblogs.com/tylerdonet/archive/2011/05/10/2042447.html