SQL Server の待ち事象は sys.dm_os_wait_stats / sys.dm_os_latch_stats というような動的管理ビューを利用して確認することができます。
パフォーマンスモニターの SQL Server: Wait Statistics からも確認をすることができますが、こちらは一部の項目しか取得することができません。
一時テーブルや PowerShell 等を使用すれば定期的に DMV の情報を出力するということもできますが、環境に依存せずに SSMS だけで実行するための方法をまとめてみたいと思います。
待ち事象の情報は SQL Server のサービスが最後に起動してからの累計値を取得することができます。
sys.dm_os_wait_stats を初期化して、情報を取得するのであれば以下のようになります。
DBCC SQLPERF(N'sys.dm_os_wait_stats', CLEAR) SELECT GETDATE() time, * FROM sys.dm_os_wait_stats ORDER BY wait_type ASC
一時テーブルを使用する場合は以下のようなクエリとなります。
SELECT GETDATE() time, * INTO #waitstats FROM sys.dm_os_wait_stats ORDER BY wait_type ASC GO INSERT INTO #waitstats SELECT GETDATE() time, * FROM sys.dm_os_wait_stats ORDER BY wait_type ASC WAITFOR DELAY '00:00:05' GO 10000 SELECT * FROM #waitstats
SSMS だけで実施する場合には、クエリの実行結果をファイルに保存することで対応ができるかと思います。
SSMS からクエリを実行した結果はファイルに保存することができます。
この時の保存の形式ですが、SSMS のオプションの [結果をテキストで表示] の設定に依存します。
今回は取り込みをしやすいように、タブ区切りでヘッダーを含めない設定で設定しています。
この設定をして、クエリを実行して実行結果をファイルに保存します。
SET NOCOUNT ON DBCC SQLPERF(N'sys.dm_os_wait_stats', CLEAR) WITH NO_INFOMSGS GO SELECT GETDATE() time, * FROM sys.dm_os_wait_stats ORDER BY wait_type ASC WAITFOR DELAY '00:00:01' GO 100
先頭に実行ループの開始が出力されているのでこれは削除します。
今回はタブ区切りのテキストとして出力がされていますのでこれをテーブルに取り込みます。
以下のようなクエリでテーブルを作成して、
SELECT GETDATE() time, * INTO tempdb..waitstats FROM sys.dm_os_wait_stats WHERE 1=0
作成されたコピーテーブルは NULL 許容が無効になっているので有効にしておきます。
以下のクエリテーブルを作成してもいいかもしれないですね。
CREATE TABLE [dbo].[waitstats]( [time] [datetime] NULL, [wait_type] [nvarchar](60) NULL, [waiting_tasks_count] [bigint] NULL, [wait_time_ms] [bigint] NULL, [max_wait_time_ms] [bigint] NULL, [signal_wait_time_ms] [bigint] NULL ) ON [PRIMARY] GO
あとはインポートウィザードでインポートします。
今回は、ヘッダに列名を出していませんので、[先頭データ行を列名として使用する] を無効にして、
エラー時や切り捨て時は無視するようにしておきます。
これで時系列のデータがテーブルに格納されますので、あとはクエリで集計します。
SELECT time, SUM(waiting_tasks_count) waiting_tasks_count, SUM(wait_time_ms) wait_time_ms FROM tempdb..waitstats WHERE wait_type LIKE 'PAGEIOLATCH_%' GROUP BY time
情報を収集するタイミングや期間は、[WAITFOR DELAY] で取得間隔を [GO] で取得期間を調整することができます。
途中でクエリを終了した場合はそこまでの結果がファイルに出力されますので、途中で止めても影響はないかと。
これでしたら、クエリを実行している側の環境へのファイル書き込みになりますので、本番系への影響は抑えることができるかなと。