要知道線程等待時(shí)間是制約SQL Server效率的重要原因，這一個(gè)隨筆中將學(xué)習(xí)怎樣收集SQL Server中的線程等待時(shí)間，類型等信息，這些信息是進(jìn)行數(shù)據(jù)庫(kù)優(yōu)化的依據(jù)。

sys.dm_os_wait_stats

這是一個(gè)系統(tǒng)視圖，里面存儲(chǔ)線程所遇到的所有的等待信息，具體的列如下表

要注意的是，這個(gè)視圖的信息每次關(guān)閉SQL Server的時(shí)候都會(huì)自動(dòng)清除，下次打開(kāi)SQL Server的時(shí)候又會(huì)重新開(kāi)始統(tǒng)計(jì)。

新建線程等待信息表

如果想得到連續(xù)的信息，在固定時(shí)間間隔內(nèi)收集信息比如一個(gè)小時(shí)一次，這樣就可以分析系統(tǒng)分配的等待時(shí)間，識(shí)別出繁忙時(shí)間段。這里我們將這些信息收集到一個(gè)數(shù)據(jù)表中保存并進(jìn)行分析。使用下面的語(yǔ)句新建一個(gè)表：

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填充數(shù)據(jù)

要收集信息最好是用一個(gè)job來(lái)定時(shí)地執(zhí)行insert語(yǔ)句填充數(shù)據(jù)，下面介紹步驟

收集等待信息數(shù)據(jù)

過(guò)一段時(shí)間之后在表WaitStats中就會(huì)有一些數(shù)據(jù)，每隔5分鐘就會(huì)在這個(gè)表中寫(xiě)入一些數(shù)據(jù)，這些數(shù)據(jù)會(huì)不斷的增加。這里為了得到相鄰間隔之間線程等待時(shí)間的變化就要使用自連接，連接條件是等待類型相同，當(dāng)前行號(hào)等于上一個(gè)的行號(hào)加上1，然后就可以用上一次等待時(shí)間減去這一次的等待時(shí)間得到這個(gè)變化值，下面使用一個(gè)函數(shù)來(lái)實(shí)現(xiàn)這個(gè)邏輯：

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 < DATEADD(day, 1, @todt)
GO

這個(gè)函數(shù)接受兩個(gè)參數(shù)，開(kāi)始統(tǒng)計(jì)時(shí)間，結(jié)束統(tǒng)計(jì)時(shí)間，返回等待變化的時(shí)間，并按照類型排序。調(diào)用這個(gè)函數(shù)如下：

FROM dbo.IntervalWaits('20110509', '20110510') AS F

ORDER BY SUM(interval_wait_s) OVER(PARTITION BY wait_type) DESC,wait_type,start_time;

但是我們不能每次都去調(diào)用這個(gè)函數(shù)，所以可以吧這個(gè)查詢放在一個(gè)視圖里面，外部只需要使用視圖來(lái)查詢數(shù)據(jù)就可以了：

IF OBJECT_ID('dbo.IntervalWaitsSample', 'V') IS NOT NULL
DROP VIEW dbo.IntervalWaitsSample;
GO
CREATE VIEW dbo.IntervalWaitsSample
AS
SELECT wait_type, start_time, interval_wait_s
FROM dbo.IntervalWaits('20090212', '20090215') AS F;
GO