以前、待ち事象を時系列で解析するためのデータを取得する という投稿をしたのですがその別バージョンとなります。
以前の投稿は、定期的の wait_stats を取得していましたが、取得したタイミングの情報をそのまま格納していたため、右肩上がりの情報しか作成することができませんでした。
そこで以下のようなクエリを作成し、前回実行時との差分を求めてテーブルに格納する仕組みにしてみました。
SET NOCOUNT ON
GO
DECLARE @waittime nvarchar(8) = '00:00:10'
DECLARE @initilalize bit = 0
IF OBJECT_ID('tempdb..#tmp_wait_stats_old') IS NULL
BEGIN
SELECT *
INTO #tmp_wait_stats_old
FROM sys.dm_os_wait_stats
CREATE CLUSTERED INDEX IX_tmp_wait_stats_old
ON #tmp_wait_stats_old (wait_type)
END
ELSE
BEGIN
Truncate Table #tmp_wait_stats_old
END
IF OBJECT_ID('tempdb..#tmp_wait_stats') IS NULL
BEGIN
SELECT
GETDATE() AS DateTime,
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
INTO #tmp_wait_stats
FROM sys.dm_os_wait_stats
WHERE 1 = 0
CREATE CLUSTERED INDEX IX_tmp_wait_stats
ON #tmp_wait_stats (wait_type)
END
ELSE
BEGIN
IF @initilalize = 1
Truncate Table #tmp_wait_stats
END
WHILE (1=1)
BEGIN
INSERT INTO
#tmp_wait_stats
SELECT
GETDATE() AS DateTime,
T1.wait_type,
ISNULL(T1.waiting_tasks_count - T2.waiting_tasks_count, 0) AS waiting_tasks_count,
ISNULL(T1.wait_time_ms - T2.wait_time_ms, 0) AS wait_time_ms,
ISNULL(T1.max_wait_time_ms - T2.max_wait_time_ms, 0) AS max_wait_time_ms,
ISNULL(T1.signal_wait_time_ms - T2.signal_wait_time_ms, 0) AS signal_wait_time_ms
FROM
sys.dm_os_wait_stats T1
LEFT JOIN
#tmp_wait_stats_old T2
ON
T1.wait_type = T2.wait_type
ORDER BY
T1.wait_type ASC
TRUNCATE TABLE #tmp_wait_stats_old
INSERT INTO #tmp_wait_stats_old
SELECT * FROM sys.dm_os_wait_stats
EXEC ('WAITFOR DELAY ''' + @waittime + '''')
END
実行結果は、[#tmp_wait_stats] に格納されますので、実行後にこのテーブルを検索することで情報を確認することができます。
上記のクエリで 10 秒間隔で情報を取得している間に以下のクエリを SQLQueryStress で実行してみます。
SET NOCOUNT ON
DECLARE @delay int = (SELECT RAND() * 10)
EXEC ('WAITFOR DELAY ''00:00:' + @delay + '''')
WAITFOR DELAY '00:00:01'
以下は [WAITFOR] 待ち事象の情報を取得したものですが、このようなグラフを作成することができます。
格納先の一時テーブルを作成しない場合は以下のようなクエリでしょうか。
# 結果をファイルに保存して履歴を取得する必要がありますが。
SET NOCOUNT ON
GO
DECLARE @waittime nvarchar(8) = '00:00:10'
IF OBJECT_ID('tempdb..#tmp_wait_stats_old') IS NULL
BEGIN
SELECT *
INTO #tmp_wait_stats_old
FROM sys.dm_os_wait_stats
CREATE CLUSTERED INDEX IX_tmp_wait_stats_old
ON #tmp_wait_stats_old (wait_type)
END
ELSE
BEGIN
Truncate Table #tmp_wait_stats_old
END
WHILE (1=1)
BEGIN
SELECT
GETDATE() AS DateTime,
T1.wait_type,
ISNULL(T1.waiting_tasks_count - T2.waiting_tasks_count, 0) AS waiting_tasks_count,
ISNULL(T1.wait_time_ms - T2.wait_time_ms, 0) AS wait_time_ms,
ISNULL(T1.max_wait_time_ms - T2.max_wait_time_ms, 0) AS max_wait_time_ms,
ISNULL(T1.signal_wait_time_ms - T2.signal_wait_time_ms, 0) AS signal_wait_time_ms
FROM
sys.dm_os_wait_stats T1
LEFT JOIN
#tmp_wait_stats_old T2
ON
T1.wait_type = T2.wait_type
ORDER BY
T1.wait_type ASC
TRUNCATE TABLE #tmp_wait_stats_old
INSERT INTO #tmp_wait_stats_old
SELECT * FROM sys.dm_os_wait_stats
EXEC ('WAITFOR DELAY ''' + @waittime + '''')
END
今までは 2 点の差分しか出していなかったのですが、いろいろな確認方法を持っていたいなと。