要知道線程等待時(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)清除，下次打開SQL Server的時(shí)候又會(huì)重新開始統(tǒng)計(jì)。

新建線程等待信息表

如果想得到連續(xù)的信息，在固定時(shí)間間隔內(nèi)收集信息比如一個(gè)小時(shí)一次，這樣就可以分析系統(tǒng)分配的等待時(shí)間，識(shí)別出繁忙時(shí)間段。這里我們將這些信息收集到一個(gè)數(shù)據(jù)表中保存并進(jìn)行分析。使用下面的語句新建一個(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來定時(shí)地執(zhí)行insert語句填充數(shù)據(jù)，下面介紹步驟

點(diǎn)擊數(shù)據(jù)庫(kù)中的SQL Server Agent，展開，右擊jobs文件夾，右擊新建。如圖1

在新建job界面，General標(biāo)簽中填寫job名稱，描述，如圖2

點(diǎn)擊Setps標(biāo)簽，點(diǎn)擊新建按鈕，在新建step界面內(nèi)設(shè)置Step Name，Type，DataBase，Command等屬性，如下圖3。這些屬性一看就知道是要做什么的，就不一一的解釋了。還要說的 是截圖是建好的job，如果你自己做的話對(duì)話框顯示的標(biāo)題可能不一樣。

Command屬性是設(shè)置要執(zhí)行的SQL語句或存儲(chǔ)過程等等，這里設(shè)置如下的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 

點(diǎn)擊Shedules標(biāo)簽，設(shè)置job的執(zhí)行計(jì)劃，這里設(shè)置的是每天的每5分鐘執(zhí)行一次如圖4

上述步驟包含了新建一個(gè)job的主要設(shè)置，其他的細(xì)節(jié)信息沒有包含，遇到具體問題再具體分析吧。

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

過一段時(shí)間之后在表WaitStats中就會(huì)有一些數(shù)據(jù)，每隔5分鐘就會(huì)在這個(gè)表中寫入一些數(shù)據(jù)，這些數(shù)據(jù)會(huì)不斷的增加。這里為了得到相鄰間隔之間線程等待時(shí)間的變化就要使用自連接，連接條件是等待類型相同，當(dāng)前行號(hào)等于上一個(gè)的行號(hào)加上1，然后就可以用上一次等待時(shí)間減去這一次的等待時(shí)間得到這個(gè)變化值，下面使用一個(gè)函數(shù)來實(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 < DATE 
ADD(day, 1, @todt)  
GO 

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

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;

但是我們不能每次都去調(diào)用這個(gè)函數(shù)，所以可以吧這個(gè)查詢放在一個(gè)視圖里面，外部只需要使用視圖來查詢數(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 

從視圖中查詢得到的數(shù)據(jù)就是我們要得到的數(shù)據(jù)。但是這些并不明顯，先寫到這里，下一個(gè)隨筆我將在EXECL中把這些數(shù)據(jù)制作成一個(gè)直方圖或者連線圖，橫軸是時(shí)間，縱軸是等待間隔時(shí)間。這樣就會(huì)更加直觀地看到在那些時(shí)間SQL Server的線程等待時(shí)間最長(zhǎng)，也就是最繁忙的時(shí)候。

原文鏈接：http://www.cnblogs.com/tylerdonet/archive/2011/05/10/2042447.html

【編輯推薦】

1. 淺述遠(yuǎn)程Service Broker的實(shí)現(xiàn)
2. Service Broker基礎(chǔ)應(yīng)用(下）
3. Service Broker基礎(chǔ)應(yīng)用(上）
4. 簡(jiǎn)述Service Broker事件通知功能

5. SQL使用中的點(diǎn)滴回憶