SQL點滴13—收集SQLServer線程等待信息

要知道線程等待時間是制約SQL Server效率的重要原因，這一個隨筆中將學習怎樣收集SQL Server中的線程等待時間，類型等信息，這些信息是進行數(shù)據(jù)庫優(yōu)化的依據(jù)。 sys.dm_os_wait_stats 這是一個系統(tǒng)視圖，里面存儲線程所遇到的所有的等待信息，具體的列如下表

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

sys.dm_os_wait_stats

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

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

新建線程等待信息表

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

use AdventureWorks
CREATETABLE dbo.WaitStats
(
dt DATETIMENOTNULLDEFAULT (CURRENT_TIMESTAMP),
wait_type NVARCHAR(60) NOTNULL,
waiting_tasks_count BIGINTNOTNULL,
wait_time_ms BIGINTNOTNULL,
max_wait_time_ms BIGINTNOTNULL,
signal_wait_time_ms BIGINTNOTNULL
);
CREATEUNIQUECLUSTEREDINDEX idx_dt_type ON dbo.WaitStats(dt, wait_type);
CREATEINDEX idx_type_dt ON dbo.WaitStats(wait_type, dt);

新建job填充數(shù)據(jù)

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

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

   圖1

2. 
   
   圖2

3. 
   圖3
   Command屬性是設(shè)置要執(zhí)行的SQL語句或存儲過程等等，這里設(shè)置如下的SQL Script:
   

   INSERTINTO 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. 
   
   圖4

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

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

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

IFOBJECT_ID('dbo.IntervalWaits', 'IF') ISNOTNULL
DROPFUNCTION dbo.IntervalWaits;
GO
CREATEFUNCTION dbo.IntervalWaits
(@fromdtASDATETIME, @todtASDATETIME)
RETURNSTABLE
AS
RETURN
WITH Waits AS
(
SELECT dt, wait_type, wait_time_ms,
ROW_NUMBER() OVER(PARTITION BY wait_type
ORDERBY 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 GO

這個函數(shù)接受兩個參數(shù)，開始統(tǒng)計時間，結(jié)束統(tǒng)計時間，返回等待變化的時間，并按照類型排序。調(diào)用這個函數(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)用這個函數(shù)，所以可以吧這個查詢放在一個視圖里面，外部只需要使用視圖來查詢數(shù)據(jù)就可以了：

IFOBJECT_ID('dbo.IntervalWaitsSample', 'V') ISNOTNULL
DROPVIEW dbo.IntervalWaitsSample;
GO
CREATEVIEW dbo.IntervalWaitsSample
AS
SELECT wait_type, start_time, interval_wait_s
FROM dbo.IntervalWaits('20090212', '20090215') AS F;
GO

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com