要知道線程等待時間是制約SQL Server效率的重要原因，這一個隨筆中將學(xué)習(xí)怎樣收集SQL Server中的線程等待時間，類型等信息，這些信息是進行數(shù)據(jù)庫優(yōu)化的依據(jù)。
sys.dm_os_wait_stats
這是一個系統(tǒng)視圖，里面存儲線程所遇到的所有的等待信息，具體的列如下表
|
列名 |
數(shù)據(jù)類型 |
說明 |
|
Wait_type |
Nvarchar(60) |
等待類型名稱 |
|
waiting_tasks_count |
Bigint |
等待類型的等待數(shù)。該計數(shù)器在每開始一個等待時便會增加。 |
|
Wait_time_ms |
Bigint |
該等待類型的總等待時間。 |
|
Max_wait_time_ms |
Bigint |
該等待類型的最長等待時間。 |
|
Signal_wait_time_ms |
Bigint |
正在等待的線程從收到信號通知到開始運行之間的時差。 |
要注意的是，這個視圖的信息每次關(guān)閉SQL Server的時候都會自動清除，下次打開SQL Server的時候又會重新開始統(tǒng)計。
新建線程等待信息表
如果想得到連續(xù)的信息，在固定時間間隔內(nèi)收集信息比如一個小時一次，這樣就可以分析系統(tǒng)分配的等待時間，識別出繁忙時間段。這里我們將這些信息收集到一個數(shù)據(jù)表中保存并進行分析。使用下面的語句新建一個表：
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ù)
要收集信息最好是用一個job來定時地執(zhí)行insert語句填充數(shù)據(jù)，下面介紹步驟
-
點擊數(shù)據(jù)庫中的SQL Server Agent，展開，右擊jobs文件夾，右擊新建。如圖1
圖1 -
在新建job界面，General標(biāo)簽中填寫job名稱，描述，如圖2
圖2 -
點擊Setps標(biāo)簽，點擊新建按鈕，在新建step界面內(nèi)設(shè)置Step Name，Type，DataBase，Command等屬性，如下圖3。這些屬性一看就知道是要做什么的，就不一一的解釋了。還要說的 是截圖是建好的job，如果你自己做的話對話框顯示的標(biāo)題可能不一樣。
圖3
Command屬性是設(shè)置要執(zhí)行的SQL語句或存儲過程等等，這里設(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 -
點擊Shedules標(biāo)簽，設(shè)置job的執(zhí)行計劃，這里設(shè)置的是每天的每5分鐘執(zhí)行一次如圖4
????????????????????????????????????????????????????????????
圖4
上述步驟包含了新建一個job的主要設(shè)置，其他的細(xì)節(jié)信息沒有包含，遇到具體問題再具體分析吧。
收集等待信息數(shù)據(jù)
過一段時間之后在表WaitStats中就會有一些數(shù)據(jù)，每隔5分鐘就會在這個表中寫入一些數(shù)據(jù)，這些數(shù)據(jù)會不斷的增加。這里為了得到相鄰間隔之間線程等待時間的變化就要使用自連接，連接條件是等待類型相同，當(dāng)前行號等于上一個的行號加上1，然后就可以用上一次等待時間減去這一次的等待時間得到這個變化值，下面使用一個函數(shù)來實現(xiàn)這個邏輯：
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
這個函數(shù)接受兩個參數(shù)，開始統(tǒng)計時間，結(jié)束統(tǒng)計時間，返回等待變化的時間，并按照類型排序。調(diào)用這個函數(shù)如下：
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;
但是我們不能每次都去調(diào)用這個函數(shù)，所以可以吧這個查詢放在一個視圖里面，外部只需要使用視圖來查詢數(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ù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
