【SQL Server】データコレクションのレポート出力の仕方

システムデータコレクション(MDW)を利用するとパフォーマンス情報を定期的に取得しレポートとして出力でき分析時に便利です。

レポートの出力の仕方は「管理」-「データコレクション」を右クリック、「レポート（P)」-「管理データウェアハウス」で各種レポートを選択することで表示できます。

また、各種グラフについては右クリックでエクスポートすることが可能です。

各レポートのための情報収集間隔と保存期間は以下の通りです。

尚、出力可能なグラフは多く便利ですが表示する方法がわかりにくいので、出力の仕方は別途記載します。

【SQL server】ラッチ状況の確認

SQLserver ラッチ待ちの解説と確認

SQLserverには3種類のラッチが存在します。

1.LACTH_[xx] (非バッファーラッチ)
2.PAGELATCH_[xx] (バッファーラッチ)
3.PAGEIOLATCH_[xx] (バッファーラッチ)
※[xx]にはSH(参照),UP(更新),EX(排他)などが入ります。

これらについて主にどのような時に発生するのか解説します。
ラッチ全体としては以下のようなイメージになるかと思います。

ラッチ全体の状況を確認するSQLは以下でざっくり確認できます。

SELECT 
		CASE 
			WHEN [wait_type] LIKE N'LATCH%'		THEN N'Non_Buffer_Latch'
			WHEN [wait_type] LIKE N'PAGELATCH%'	THEN N'Buffer_Latch'
			WHEN [wait_type] LIKE N'PAGEIOLATCH%'	THEN N'Buffer_Latch'
		END AS [type]
		,* 
	FROM sys.dm_os_wait_stats
	WHERE 　[wait_type] LIKE N'LATCH%' 
	　　OR	[wait_type] LIKE N'PAGELATCH%' 
	　　OR	[wait_type] LIKE N'PAGEIOLATCH%' 

1.LACTH_[xx]
メモリ上の非バッファーキャッシュの読み取り時に発生します。様々な種類がありますが、例えばインデックスのB-treeの拡張等で発生します。

上記SQLのtypeが「Non_Buffer_Latch」であるレコードを確認すればどのくらい待ちが発生しているかは判断できますが、さらにラッチの詳細を確認する場合は以下のようなSQLで確認するとよいでしょう。

SELECT  [latch_class],
　　　　[wait_time_ms] AS [wait_time_ms],
	[waiting_requests_count] AS [wait_counts],
	100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [wait_time_Percentage]
　FROM sys.dm_os_latch_stats 
	WHERE [latch_class] NOT IN ( N'BUFFER') AND [wait_time_ms] > 0 
	ORDER BY [wait_time_Percentage] DESC

2.PAGELATCH_[xx]
バッファーキャッシュ上に存在するページの読み取り時に発生します。
tempdbのPFS(Page Free Space)に対する一時オブジェクトのCREATE,DROPや、インデックスの末尾ページへのデータの追加等で見受けられます。

対処法としては以下のようなものが考えられます。
tempdbのPFSに対しては
①tempdbのデータファイル数をCPU数に一致させる。
②tempdbのデータファイル容量をすべて同じサイズにする。
詳しくはMSのData Platform Support Team Blogにtempdbの考え方についての記載がありますのでこちらを見ていただければよいかと。
https://blogs.msdn.microsoft.com/jpsql/2013/01/16/dosdonts-17-tempdb-259/

インデックスページの末尾で発生するPAGELATCH_[xx]については
①同じファイルサイズのデータファイル(.ndf)を追加して処理対象となるデータファイルを分散させる。
②インデックスのキー項目がシーケンシャルな値の場合には追加処理が末尾ページに集中するので、GUIDなどの処理対象が分散するような値をインデックスのキーとして検討する。（業務要件として可能な場合に限る）
③テーブルやインデックスをパーティション分割する。

PAGEIOLATCH_[xx]
これはよく見かけるLATCHかと思います。
読み取り操作時にディスクからバッファーキャッシュへ移動する際に発生します。ゆえにインスタンス起動直後やキャッシュをクリアした後には発生してしまいます。

尚ディスクからキャッシュへのデータページの移動については以下のような順序で実施されます。
①データページの要求
②バッファーキャッシュにデータが存在しない
③バッファーにページ用領域を割り当て
④ディスクからバッファーキャッシュへ移動

対処法としては、以下の観点で確認をするとよいかと思います。

(1)ログからSQLserverが再起動していないか確認
再起動後はキャッシュが空のため、クエリ実行時にPAGEIOLATCHが多く発生します。
これに起因するPAGEIOLATCHの発生はやむを得ないかと思います。

(2)IOが多いクエリがないか確認
不要なIOを伴うクエリはディスクから大量ページをバッファーキャッシュに読み込むため、IOを減らすようチューニングしましょう。

(3)インデックスの断片化が発生していないか確認
断片化により1ページ当たりのデータ格納量が減っている場合、断片化を解消することで読み取る必要があるページ数を削減できます。
こちらに関してはメンテナンスプランで自動化する等して定期的に断片化を解消しましょう。

(4)SQLserverへの割り当てメモリが不足していないか確認
バッファーキャッシュの上限設定(MaxServerMemory)が必要としているメモリと比べ不足している場合、データページがキャッシュアウトされやすいため、ディスクからの読み取りが増加してしまいます。メモリ割り当てを増やすことを検討しましょう。
メモリが不足しているかどうかは別途分析が必要かと思いますが、簡単に以下だけでも閾値として確認しておくとよいでしょう。

メトリック	閾値
バッファーキャッシュヒット率(Buffer cache hit ratio)	97%以上※
Page life expectancy	300秒

※環境によっては90%以上でもよい場合があります。目安としては97%くらいあるとよいですが、普段のベースラインから大幅に変化しているかどうか確認して、変化があれば詳細を確認するとよいかと思います。

【SQL server】デッドロックの調査方法

デッドロック発生に際し、分析時の確認の仕方について紹介します。

エラーログやSQL Plofilerなどで調査可能ですが、個人的には拡張イベントがお勧めです。デフォルト設定であれば、拡張イベントの「system_health」でデッドロック情報は取得しているので、事後で確認する際に便利です。

拡張イベントで採取されたデータはデフォルトであれば以下のようにログファイル格納先と同様の場所にありますので、ダブルクリックして開きます。
尚、ファイル形式での拡張イベント「system_health」は、5MB：4世代しかデフォルトで残らないため、事後で確認可能とはいえデッドロック発生後は速やかに本ファイルを退避しておく方がよいでしょう。

上部のメニューで「フィルター」を選択し、以下のようにフィルタします。

フィールド	演算子	値
name	=	xml_deadlock_report

表示されたイベントのデッドロックタブを開いてデッドロックの詳細を確認します。ここではデッドロックとなったページ情報等(※1)が出力され、原因となったインデックスやクエリが表示できます。
※1 ページに格納されている情報を確認したい場合は、【SQL server】ロックの種類とページ情報 - 小物SEのメモ帳を参照

尚、クエリの表示はデッドロックグラフにマウスを当てると表示されます。

また、詳細タブに出力されているレコードをダブルクリックすることで、デッドロック時の詳細情報を確認することができます。
こちらにはsql_handleも残っているので、キャッシュ上にデータが残っていればこのsql_handleからデッドロック時の実行プランを確認できるかと思います。

拡張イベント「system_health」のring buffer上に残っているものは以下クエリで確認することも可能です。

SELECT
		XEventData.XEvent.query('.')						AS [XEvent]
	FROM (SELECT
						CAST(target_data AS XML)		AS [TargetData]
			FROM			[sys].[dm_xe_session_targets]		AS [targets]	WITH(NOLOCK)
			INNER JOIN 	[sys].[dm_xe_sessions]				AS [sessions]   WITH(NOLOCK)
			ON 			[sessions].[address] = [targets].[event_session_address]
			WHERE		[sessions].[name]		= N'system_health'
			AND			[targets].[target_name] = N'ring_buffer'
		) AS [system_health]
	CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)

デッドロックの発生箇所や原因となるクエリが特定できたら、次のアクションとしては対象クエリの実行プランや処理順序を見直してデッドロックの発生率を減少をしていきましょう。
デッドロックあるあるとしては、実行プランを確認したら暗黙型変換(CONVERT_IMPLICIT)が発生しておりインデックスがきいていなかったため、不要にページロック獲得をしていた等があるかと思います。decimal/numeric、varchar/nvarchar等は基本的なことですが、注意しましょう。

◆参考情報
・system_health セッションの使用
https://msdn.microsoft.com/ja-jp/library/ff877955.aspx

・暗黙型変換について
https://blogs.msdn.microsoft.com/jpsql/2011/02/01/dosdonts-2/

【SQL server】ロックエスカレーションの閾値について

悪者扱いされることの多いロックエスカレーションの発生する閾値と設定の確認方法についてです。

・ロック上限が動的な場合（locks オプションが既定値）のロックエスカレーションの閾値
（1）1 つの Transact-SQL ステートメントが 1 つのテーブルまたはインデックスに対して少なくとも 5,000 個のロックを獲得した場合
（2）ロックによって使用されるメモリ※がデータベースによって使用されるバッファープールの24%以上となった場合
※ロックオブジェクトのサイズは1ロックにつき96バイト

また、ロックの競合によりロックをエスカレートできない場合、新たなロックを 1,250 個獲得するごとにロックのエスカレーションが行われます。
尚、変化するワークロードに合わせてメモリを動的に確保・解放するため、上記閾値は動的に変わります。

・ロック上限が固定の場合（locks オプションが既定値でない）のロックエスカレーションの閾値
（1）ロック数が設定した値の上限の40％※に達した場合
※メモリに負荷がかかっている場合は 40% 未満

ロックエスカレーションはテーブル単位で無効化することは可能ですが、バッファープールのうち60%以上のロックを獲得した場合、もしくは使用できるメモリがなくなった場合にロックを獲得しようとするとエラーが発生しますので注意が必要です。

ちなみにロックエスカレーションの設定確認・変更は以下で行えます。

◆設定確認

SELECT 
	[name],
	[lock_escalation],
	[lock_escalation_desc]
FROM [sys].[tables]

◆設定変更

ALTER TABLE sales.product.test SET (LOCK_ESCALATION = AUTO)

lock_escalation	lock_escalation_desc	説明
0	TABLE	ロックエスカレーションはテーブルレベルの粒度で行われる ※デフォルト
1	DISABLE	ほとんどの場合のロックエスカレーションを禁止する テーブルレベルのロックは完全には禁止されない
2	AUTO	テーブルスキーマに適したロックエスカレーションの粒度が選択される また、パーティション分割されている場合、ロックエスカレーションをパーティション分割できる

◆参考資料

・locks オプションについて
https://technet.microsoft.com/ja-jp/library/ms175978(v=sql.105).aspx

・ロックのエスカレーションについて
https://technet.microsoft.com/ja-jp/library/ms184286(v=sql.105).aspx

【SQL server】ロックの種類とページ情報

デッドロックを調査するにあたりロックの種類について調べた内容を記しておきます。

ロックの粒度
上から順にロックの粒度は細かくなります。

Lock Type	説明
DB	データベース単位のロック
TAB	テーブル単位のロック
PAGE	ページ単位のロック
ROW	行単位のロック
KEY	キー単位のロック

代表的なロックモード

Request Mode	Lock Mode	説明
S	共有ロック	データの変更や更新を伴わない参照操作
U	更新ロック	更新可能なデータに対するロック
X	排他ロック	DMLを実行してデータを変更する際のロック
IS	インテント共有ロック	下位のLock Typeに位置する全てのリソースに対し他からの共有ロック要求から保護する
IU	インテント更新ロック	下位のLock Typeに位置する全てのリソースに対し他からの更新ロック要求から保護する 更新操作時にIXロックに変換される
IX	インテント排他ロック	下位のLock Typeに位置する全てのリソースに対し他からの排他ロック要求から保護する
SIU	共有インテント更新ロック	共有ロックとインテント更新ロックを組み合わせたロック
SIX	インテント排他付き共有ロック	下位のLock Typeに位置する全てのリソースに対し他からの共有ロック要求から保護し、一部のリソースに対するインテント排他ロックから保護する
UIX	更新インテント排他ロック	更新ロックとインテント排他ロックを組み合わせたロック
Sch-S	スキーマ安定度	テーブルのスキーマに依存する操作を行う時のロック トランザクションのロック要求をブロックはしない
Sch-M	スキーマ修正	テーブルのスキーマに依存する操作を行う時のロック 外部からのすべてのロック要求をブロックする
BU	一括更新ロック	データを一括で投入する際にTABLOCKヒント句を使用してロック

基本的には共有ロック、更新ロック、排他ロックを押さえておけばよいと思います。
現在ロック中の情報を得るためには以下のようなクエリで確認できます。

◆簡易版

sp_lock

◆詳細版

-- ===============================================
-- 現在のDBのロック詳細
-- ===============================================

USE Param_testDB
SELECT 
		[locks].[request_session_id]													AS	[spid],
		ISNULL(DB_NAME([resource_database_id]), '')							AS	[db_name],
		CASE	
			WHEN	[resource_type] = 'OBJECT'				THEN	OBJECT_NAME([resource_associated_entity_id])
			WHEN	[resource_associated_entity_id] =	0	THEN	''
			ELSE	OBJECT_NAME([p].[object_id])
		END																													AS [entity_name],
		[ind].[index_id]																									AS [index_id],
		[ind].[name]																										AS [index_name],
		ISNULL([locks].[resource_type], '')																		AS [resource_type],
		[locks].resource_description																				AS [resource_description],
		[locks].request_mode																							AS [request_mode],
		[locks].request_type																							AS [request_type],
		ISNULL([wait].[wait_duration_ms], 0)																	AS [wait_duration_ms],
		ISNULL([wait].[wait_type], N'')																			AS [wait_type],
		ISNULL([wait].[resource_description], N'')															AS [resource_description],
		ISNULL(CONVERT (varchar,[wait].[blocking_session_id]), '')									AS [blocking_session_id],
		ISNULL(REPLACE(REPLACE([query_text].[text],CHAR(13), ''), CHAR(10), ' '), N'')	AS [query_text],
		[plan].[query_plan]																							AS [query_plan]
FROM			[sys].[dm_tran_locks]													AS [locks] WITH (NOLOCK)
	LEFT JOIN	[sys].[partitions]														AS [p] WITH (NOLOCK)
			ON	[p].[partition_id]					= [locks].[resource_associated_entity_id]
	LEFT JOIN	[sys].[dm_os_waiting_tasks]										AS [wait] WITH (NOLOCK)
			ON	[locks].[lock_owner_address]	= [wait].[resource_address]
	LEFT JOIN	[sys].[indexes]															AS [ind] WITH (NOLOCK)
			ON	[p].[object_id]						= [ind].[object_id]
			AND	[p].[index_id]						= [ind].[index_id]
	LEFT JOIN	[sys].[dm_exec_requests]											AS [requests] WITH (NOLOCK)
			ON	[locks].[request_session_id]	= [requests].[session_id]
    OUTER APPLY	[sys].[dm_exec_sql_text]([requests].[sql_handle])	AS [query_text] 
	OUTER APPLY [sys].[dm_exec_query_plan]([requests].[plan_handle]) AS [plan] 
WHERE	resource_database_id = DB_ID()
	AND	resource_type <> 'DATABASE'
ORDER BY spid
OPTION (RECOMPILE)

ロックされているページに格納されているデータを参照する場合、undocumentedな以下クエリで参照できます。

-- page内容の取得
DBCC TRACEON(3604)
--	DBCC PAGE(<Dabase_name>, <filenum>, <pagenum>, <printopt>)
	DBCC PAGE(N'Param_testDB', 1, 5294, 3) WITH TABLERESULTS
DBCC TRACEOFF(3604)

filenum, pagenumは現在のDBのロック詳細で取得したresource_description が filenum:pagenumで表示されています。
またページ内の詳細情報を参照する場合のprintoptは0～3を選択します。

printopt	出力情報
0	ページヘッダーのみ
1	ページヘッダー + ページスロット
2	ページヘッダー + ページダンプ
3	ページヘッダー + 行ごとに詳細情報

またその他のページ情報が参照したい場合、下記にてページ番号を取得することができます。

-- page番号の取得
--  DBCC IND(<Database_name>,<Table_name>,<Index_id>)
    DBCC IND(N'Param_testDB',[Lock_test],1)

【SQL server】トランザクション分離レベルについて

SQL serverで設定できるトランザクション分離レベルについて整理しました。
※デフォルトはREAD COMMITTEDになります。

トランザクション分離レベル	ロックの種類	ダーティリード	ノンリピータブルリード	ファントムリード
READ UNCOMMITTED	悲観的ロック	○	○	○
READ COMMITTED	悲観的ロック	×	○	○
REPEATABLE READ	悲観的ロック	×	×	×
SERIALIZABLE	悲観的ロック	×	×	×
SNAPSHOT	楽観的ロック	×	×	×
READ COMMITTED SNAPSHOT	楽観的ロック	×	○	○

SQL serverのREAD COMMITTED SNAPSHOTについては、更新時の断面をTempdbにSNAPSHOTをとってから処理する動きになりますのでOracleのREAD COMMITTEDに似たような処理になるかと思います。

尚、悲観的ロックと楽観的ロックについては一般的には以下のような理解でよいかと思います。

悲観的ロック：
更新処理時に更新対象のデータを参照してから更新が完了するまでの間、他のトランザクションからの参照をブロックします。

楽観的ロック：
更新対象のデータを参照した時点ではロックをかけず、更新直前に他のトランザクションによって更新されていないことを確認してから対象をロックします。すでに更新されてしまっていた場合は、エラーとなります。

悲観的ロックについては上記の通りデフォルトの設定となります。SQL serverにおいては更新時に排他ロックを取得しますので、参照するための共有ロックがブロックされ待たされるような形になります。複数のトランザクションを順番に処理できますので、トランザクション時間が短く、頻繁に同時更新が発生するような処理（例えば金額計算処理）に向いているとされています。ただし、更新完了までのロック解放待ちが発生しますのでパフォーマンスは悪くなりますし、ロックを保持する時間が長い分デッドロックの危険も増えます。

一方、楽観的ロックについては更新頻度が少なく同時に更新されにくいようなデータに対する処理に向いています。ただし更新が競合してしまった場合のロールバック後処理も考慮する必要があります。

現状の設定を確認・変更するSQLは以下の通りとなります。

-- 設定確認
DBCC USEROPTIONS

-- Sessionレベルの設定の変更
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- DatabaseレベルのREAD COMMITTED SNAPSHOT 設定の有効化

ALTER DATABASE Database_name SET READ_COMMITTED_SNAPSHOT ON

【SQL server】利用状況モニターの情報取得について

個人的にパフォーマンス障害時によく利用する「利用状況モニター」ですが、いまいち情報取得の方法や周期について理解が不足しておりましたので、ここで整理をしてみます。
（※プロファイラーの取得結果SQLをそのまま記載してありますので、内容問題がある場合はSQL部分の削除を致しますのでご一報願います。）

SQL server2016のSSMSで利用状況モニターを開くと以下のようになっています。
※SQL server2014までですと、一番下の「アクティブなコストの高いクエリ」はありません。
以下、ペインを開いている間は、開いているペインそれぞれに対し、一定の周期で内部でSQLを発行して情報を採取する仕組みとなっています。

◆概要
「概要」ペインでは、全体のサマリのような形でグラフィカルに内部を確認できます。
「最新のコストの高いクエリ」ペインを除くそれぞれの情報採取周期は、ここの「概要」ペインで指定します。

情報採取の周期は右クリック⇒更新間隔を選択して、任意の時間を選択します。
尚、デフォルトでは、10秒に設定されております。

上記で指定した更新間隔に基づき、「概要」ペインを開いている間のみ情報を採取します。

情報採取で内部で実行されるSQLは以下となります。

EXEC #am_generate_waitstats;

SELECT 
    SUM (interval_wait_time_per_sec) / 1000 AS avg_waiting_task_count
FROM #am_resource_mon_snap;



SET NOCOUNT ON;

DECLARE @previous_collection_time datetime;
DECLARE @previous_total_io_mb numeric (28, 1);
DECLARE @current_collection_time datetime;
DECLARE @current_total_io_mb numeric (28, 1);
DECLARE @mb_per_sec numeric (20, 1);

-- Get the previous snapshot's total I/O
SELECT TOP 1 @previous_collection_time = collection_time, @previous_total_io_mb = total_io_bytes 
FROM #am_dbfileio
ORDER BY collection_time DESC;

-- Get the current total I/O.  
SET @current_collection_time = GETDATE();
SELECT @current_total_io_mb = SUM(num_of_bytes_read + num_of_bytes_written) / 1024.0 / 1024.0
FROM sys.dm_io_virtual_file_stats(default, default);

-- Calc the total I/O rate (MB/sec) for the just-completed time interval. 
-- Round values larger than 2MB/sec to the nearest MB.
SET @mb_per_sec = (@current_total_io_mb - @previous_total_io_mb) / DATEDIFF (millisecond, @previous_collection_time, @current_collection_time) * 1000;
IF @mb_per_sec > 2 
BEGIN
  SET @mb_per_sec = ROUND (@mb_per_sec, 0);
END; 

-- Save off current total I/O
INSERT INTO #am_dbfileio (collection_time, total_io_bytes) 
VALUES (@current_collection_time, @current_total_io_mb);

-- Return the I/O rate for the just-completed time interval. 
SELECT ISNULL (@mb_per_sec, 0) AS mb_per_sec;

-- Get rid of all but the most recent snapshot's data
DELETE FROM #am_dbfileio WHERE collection_time < @current_collection_time;



SET NOCOUNT ON;

DECLARE @previous_collection_time datetime;
DECLARE @previous_request_count bigint;
DECLARE @current_collection_time datetime;
DECLARE @current_request_count bigint;
DECLARE @batch_requests_per_sec bigint;
DECLARE @interval_sec bigint;

-- Get the previous snapshot's time and batch request count
SELECT TOP 1 @previous_collection_time = collection_time, @previous_request_count = request_count 
FROM #am_request_count
ORDER BY collection_time DESC;

-- Get the current total time and batch request count
SET @current_collection_time = GETDATE();
SELECT @current_request_count = cntr_value 
FROM sys.sysperfinfo
WHERE counter_name = 'Batch Requests/sec' COLLATE Latin1_General_BIN;

SET @interval_sec = 
    -- Avoid divide-by-zero
    CASE
        WHEN DATEDIFF (second, @previous_collection_time, @current_collection_time) = 0 THEN 1
        ELSE DATEDIFF (second, @previous_collection_time, @current_collection_time)
    END;

-- Calc the Batch Requests/sec rate for the just-completed time interval. 
SET @batch_requests_per_sec = (@current_request_count - @previous_request_count) / @interval_sec;

-- Save off current batch count
INSERT INTO #am_request_count (collection_time, request_count) 
VALUES (@current_collection_time, @current_request_count);

-- Return the batch requests/sec rate for the just-completed time interval. 
SELECT ISNULL (@batch_requests_per_sec, 0) AS batch_requests_per_sec;

-- Get rid of all but the most recent snapshot's data
DELETE FROM #am_request_count WHERE collection_time < @current_collection_time;

◆プロセス
「プロセス」ペインでは、セッションID毎の実行プロセスが確認できます。

さらに右クリックを押下することで、以下を確認することができます。
詳細 ⇒実行されているSQLテキスト
ライブ実行プランの表示 ⇒後述
強制終了 ⇒kill によるセッションkill

「概要」ペインで指定した更新間隔に基づき、「プロセス」ペインを開いている間のみ情報を採取します。

情報採取で内部で実行されるSQLは以下となります。

WITH profiled_sessions as (
	SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT 
   [Session ID]    = s.session_id, 
   [User Process]  = CONVERT(CHAR(1), s.is_user_process),
   [Login]         = s.login_name,   
   [Database]      = case when p.dbid=0 then N'' else ISNULL(db_name(p.dbid),N'') end, 
   [Task State]    = ISNULL(t.task_state, N''), 
   [Command]       = ISNULL(r.command, N''), 
   [Application]   = ISNULL(s.program_name, N''), 
   [Wait Time (ms)]     = ISNULL(w.wait_duration_ms, 0),
   [Wait Type]     = ISNULL(w.wait_type, N''),
   [Wait Resource] = ISNULL(w.resource_description, N''), 
   [Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
   [Head Blocker]  = 
        CASE 
            -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
            WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1' 
            -- session is either not blocking someone, or is blocking someone but is blocked by another party
            ELSE ''
        END, 
   [Total CPU (ms)] = s.cpu_time, 
   [Total Physical I/O (MB)]   = (s.reads + s.writes) * 8 / 1024, 
   [Memory Use (KB)]  = s.memory_usage * (8192 / 1024), 
   [Open Transactions] = ISNULL(r.open_transaction_count,0), 
   [Login Time]    = s.login_time, 
   [Last Request Start Time] = s.last_request_start_time,
   [Host Name]     = ISNULL(s.host_name, N''),
   [Net Address]   = ISNULL(c.client_net_address, N''), 
   [Execution Context ID] = ISNULL(t.exec_context_id, 0),
   [Request ID] = ISNULL(r.request_id, 0),
   [Workload Group] = ISNULL(g.name, N''),
   [Profiled Session Id] = profiled_session_id
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN 
(
    -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as 
    -- waiting for several different threads.  This will cause that thread to show up in multiple rows 
    -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread, 
    -- and use it as representative of the other wait relationships this thread is involved in. 
    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
    FROM sys.dm_os_waiting_tasks 
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
LEFT OUTER JOIN profiled_sessions ON profiled_session_id = s.session_id
ORDER BY s.session_id;

◆リソースの待機
「リソースの待機」ペインでは、待機イベント毎に待機している時間および待機数を表示できます。

「概要」ペインで指定した更新間隔に基づき、「リソースの待機」ペインを開いている間のみ情報を採取します。

情報採取で内部で実行されるSQLは以下となります。

EXEC #am_generate_waitstats;

SELECT 
    category_name AS [Wait Category], 
    SUM (interval_wait_time_per_sec) AS [Wait Time (ms/sec)], 
    SUM (weighted_average_wait_time_per_sec) AS [Recent Wait Time (ms/sec)], 
    SUM (interval_avg_waiter_count) AS [Average Waiter Count], 
    SUM (resource_wait_time_cumulative) /1000 AS [Cumulative Wait Time (sec)]
FROM #am_resource_mon_snap
GROUP BY category_name 
ORDER BY SUM (weighted_average_wait_time_per_sec) DESC;

◆データファイル I/O
「データファイル I/O」ペインでは、各データベースのデータファイルおよびログファイルなどにおけるI/O情報を確認することができます。

「概要」ペインで指定した更新間隔に基づき、「データファイル I/O」ペインを開いている間のみ情報を採取します。

情報採取で内部で実行されるSQLは以下となります。

DECLARE @current_collection_time datetime;
SET @current_collection_time = GETDATE();

-- Grab a snapshot
INSERT INTO #am_dbfilestats
SELECT 
    @current_collection_time AS collection_time, 
    d.name AS [Database], 
    f.physical_name AS [File], 
    (fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read], 
    (fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written], 
    (fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count], 
    fs.io_stall AS [Total I/O Wait Time (ms)], 
    fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)]
FROM sys.dm_io_virtual_file_stats(default, default) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id
INNER JOIN sys.databases d ON d.database_id = fs.database_id; 

-- Get the timestamp of the previous collection time
DECLARE @previous_collection_time datetime;
SELECT TOP 1 @previous_collection_time = collection_time 
FROM #am_dbfilestats 
WHERE collection_time < @current_collection_time
ORDER BY collection_time DESC;

DECLARE @interval_ms int;
SET @interval_ms = DATEDIFF (millisecond, @previous_collection_time, @current_collection_time); 

-- Return the diff of this snapshot and last
SELECT 
    cur.[Database], 
    cur.[File] AS [File Name], 
    CONVERT (numeric(28,1), (cur.[Total MB Read] - prev.[Total MB Read]) * 1000 / @interval_ms) AS [MB/sec Read], 
    CONVERT (numeric(28,1), (cur.[Total MB Written] - prev.[Total MB Written]) * 1000 / @interval_ms) AS [MB/sec Written], 
    -- protect from div-by-zero
    CASE 
        WHEN (cur.[Total I/O Count] - prev.[Total I/O Count]) = 0 THEN 0
        ELSE
            (cur.[Total I/O Wait Time (ms)] - prev.[Total I/O Wait Time (ms)]) 
                / (cur.[Total I/O Count] - prev.[Total I/O Count])
    END AS [Response Time (ms)]
FROM #am_dbfilestats AS cur
INNER JOIN #am_dbfilestats AS prev ON prev.[Database] = cur.[Database] AND prev.[File] = cur.[File]
WHERE cur.collection_time = @current_collection_time 
    AND prev.collection_time = @previous_collection_time;

-- Delete the older snapshot
DELETE FROM #am_dbfilestats
WHERE collection_time != @current_collection_time;

◆最新のコストの高いクエリ
「最新のコストの高いクエリ」ペインでは、クエリ毎のCPUTimeや平均実行時間などのパフォーマンス情報を確認することができます。

右クリックを押下すると以下を確認できます。

クエリテキストの編集　　　⇒SQLテキストを一部出力
実行プランの表示　　　　　⇒推定の実行プランを表示

「概要」ペインで指定した更新間隔ではなく、こちらは「最新のコストの高いクエリ」ペインを開いている間のみ30秒ごとに情報を採取します。

※SQLプロファイラーでトレースした結果、20秒ごとに採取しているように見えましたが、公式には30秒ごととのこと。
情報採取で内部で実行されるSQLは以下となります。

EXEC #am_get_querystats

◆アクティブなコストの高いクエリ
「アクティブなコストの高いクエリ」ペインでは、現在実行中のクエリのみに対して、セッション毎のSQLのCPUTimeや平均実行時間などのパフォーマンス情報を確認することができます。

右クリックを押下すると以下を確認できます。

クエリテキストの編集　　　⇒SQLテキストを一部出力
ライブ実行プランの表示　　⇒後述
実行プランの表示　　　　　⇒推定の実行プランを表示

「概要」ペインで指定した更新間隔に基づき、「アクティブなコストの高いクエリ」ペインを開いている間のみ情報を採取します。

情報採取で内部で実行されるSQLは以下となります。

WITH profiled_sessions as (
	SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT TOP 10 SUBSTRING(qt.TEXT, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2)+1) as [Query],
er.session_id as [Session Id],
er.cpu_time as [CPU (ms/sec)],
db.name as [Database Name],
er.total_elapsed_time as [Elapsed Time],
er.reads as [Reads],
er.writes as [Writes],
er.logical_reads as [Logical Reads],
er.row_count as [Row Count],
mg.granted_memory_kb as [Allocated Memory],
mg.used_memory_kb as [Used Memory],
mg.required_memory_kb as [Required Memory],
/* We must convert these to a hex string representation because they will be stored in a DataGridView, which can't handle binary cell values (assumes anything binary is an image) */
master.dbo.fn_varbintohexstr(er.plan_handle) AS [sample_plan_handle], 
er.statement_start_offset as [sample_statement_start_offset],
er.statement_end_offset as [sample_statement_end_offset],
profiled_session_id as [Profiled Session Id]
FROM 
sys.dm_exec_requests er
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg 
	ON er.session_id = mg.session_id
LEFT OUTER JOIN profiled_sessions
	ON profiled_session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) qt,
sys.databases db
WHERE db.database_id = er.database_id
AND er.session_id  <> @@spid

ライブ実行プランの表示は、拡張イベントでquery_post_complilation_showplanイベントを取得している場合、SQL server2016の新機能であるライブ実行プランの表示をさせることができます。
現在実行中のプランに対し、実際の実行プラン(actualなplan)のどのアクセスパスを実行中か確認することができる機能となっております。

利用状況モニターは、ペインを閉じている間は情報採取しないようですので、特にパフォーマンス障害中などは不要なペインは閉じて必要なもののみ開いておくことが必要です。
また、「最新のコストの高いクエリ」ペインは30秒ごとに更新とのことですので、リアルタイムで見る際には更新間隔に注意が必要ようそうです。
以下まとめ。

ペイン名	採取周期	採取トリガー
概要	"更新間隔"の設定値	ペインを開いているとき
プロセス	概要ペインと同じ	ペインを開いているとき
リソースの待機	概要ペインと同じ	ペインを開いているとき
データファイル I/O	概要ペインと同じ	ペインを開いているとき
最新のコストの高いクエリ	30秒毎	ペインを開いているとき
アクティブなコストの高いクエリ	概要ペインと同じ	ペインを開いているとき

MSの解説も以下に貼っておきます。
https://technet.microsoft.com/ja-jp/library/cc879320(v=sql.105).aspx

長時間リアルタイムで実行中の高負荷SQLを監視するのに、「アクティブなコストの高いクエリ」を監視するのは、非常に効果が高いと思います。
以下は、長時間化しているSQLを監視しているときの画面キャプチャですが、「最新のコストの高いクエリ」「アクティブなコストの高いクエリ」ペインそれぞれで同じSQLを監視しているはずなのに監視結果に大きな差が出てしまっています。
これは「アクティブなコストの高いクエリ」がセッション毎に情報を取得しているため、実質そのSQL単体をリアルタイムで監視できていることに対し、「最新のコストの高いクエリ」は、過去分や他セッション分を含めて実行回数で割っているので、結果がサマられてしまっているのではないかと考えられます。

同SQLを複数セッションで実行した場合が以下です。