SQL點滴13—收集SQLServer線程等待資訊

要知道線程等待時間是制約SQL Server效率的重要原因，這一個隨筆中將學習怎樣收集SQL Server中的線程等待時間，類型等資訊，這些資訊是進行資料庫最佳化的依據。

　　

sys.dm_os_wait_stats

這是一個系統檢視表，裡面儲存線程所遇到的所有的等待資訊，具體的列如下表

列名	資料類型	說明
Wait_type	Nvarchar(60)	等待類型名稱
waiting_tasks_count	Bigint	等待類型的等待數。該計數器在每開始一個等待時便會增加。
Wait_time_ms	Bigint	該等待類型的總等待時間。
Max_wait_time_ms	Bigint	該等待類型的最長等待時間。
Signal_wait_time_ms	Bigint	正在等待的線程從收到訊號通知到開始運行之間的時差。

要注意的是，這個視圖的資訊每次關閉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語句填充資料，下面介紹步驟

1. 點擊資料庫中的SQL Server Agent，展開，右擊jobs檔案夾，右擊建立。1
   

   圖1

2. 在建立job介面，General標籤中填寫job名稱，描述，2
   
   圖2
3. 點擊Setps標籤，點擊建立按鈕，在建立step介面內設定Step Name，Type，DataBase，Command等屬性，如3。這些屬性一看就知道是要做什麼的，就不一一的解釋了。還要說的 是是建好的job，如果你自己做的話對話方塊顯示的標題可能不一樣。

   
   圖3
   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

4. 點擊Shedules標籤，設定job的執行計畫，這裡設定的是每天的每5分鐘執行一次4
                                                               
   圖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 < DATEADD(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;
GO
CREATE VIEW dbo.IntervalWaitsSample
AS
SELECT wait_type, start_time, interval_wait_s
FROM dbo.IntervalWaits('20090212', '20090215') AS F;
GO

從視圖中查詢得到的資料就是我們要得到的資料。但是這些並不明顯，先寫到這裡，下一個隨筆我將在EXECL中把這些資料製作成一個長條圖或者連線圖，橫軸是時間，縱軸是等待間隔時間。這樣就會更加直觀地看到在那些時間SQL Server的線程等待時間最長，也就是最繁忙的時候。