要知道線程等待時間是制約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語句填充數據，下面介紹步驟
-
點擊數據庫中的SQL Server Agent，展開，右擊jobs文件夾，右擊新建。如圖1
圖1 -
在新建job界面，General標簽中填寫job名稱，描述，如圖2
圖2 -
點擊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 -
點擊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_sFROM 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
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
