SQLServer: 動的管理ビュー同士の関係を把握する

SQLServerで、動的管理ビュー(DMV)を使って情報を取得するとき、それぞれのDMVをどうやってJOINしたら良いか分からず、ググッて見つけたクエリをそのまま流す日々でした。
もちろんググッて見つけたクエリも正しい事がほとんどですが、中には結果が明らかにおかしいことも。。。

DMV同士の関係性を知ることができれば、
・ググッて見つけてきたクエリの妥当性の検証や修正、拡張
・ゼロベースでのDMV使用クエリの構築
等に使えそうです。探したところ、2008, 2012でそれらしきものが見つかりました。

SQLServer 2008 System Views Map
https://www.microsoft.com/en-us/download/details.aspx?id=9301

SQLServe 2012 System Views Map
https://www.microsoft.com/en-us/download/details.aspx?id=39083

早速、前から使っていたけど、結果が怪しいクエリを検証してみました。

下記のクエリは、現在実行中のクエリを、実行時間が長い順に100件取得するクエリです。
強力だな～とよく使っているのですが、結果が怪しいときがありました。
（出典：http://www.carehart.org/blog/client/index.cfm/2015/2/10/show_running_sql_server_queries）

--修正前
select top 100
   dest.text as [Command text]
  ,der.total_elapsed_time / 1000.0 / 60 as 'total_elapsed_time' --min
  ,DB_NAME(der.database_id) as DatabaseName
  ,der.command
  ,des.login_time
  ,des.[host_name]
  ,des.[program_name]
  ,der.session_id
  ,SUBSTRING(dest.TEXT, der.statement_start_offset / 2, (
      CASE 
        WHEN der.statement_end_offset = - 1
          THEN LEN(CONVERT(NVARCHAR(MAX), dest.TEXT)) * 2
        ELSE der.statement_end_offset
      END - der.statement_start_offset
      ) / 2) as current_running_stmt
--  ,qp.* -- プランもみたいときはコメント外す
from sys.dm_exec_requests der
inner join sys.dm_exec_connections dec on der.session_id = dec.session_id
inner join sys.dm_exec_sessions des on des.session_id = der.session_id
cross apply sys.dm_exec_sql_text(sql_handle) as dest
--cross apply sys.dm_exec_query_plan(plan_handle) as qp -- プランもみたいときはコメント外す
where des.is_user_process = 1
--  and　dest.text like '%*****%'
order by [total_elapsed_time] desc

図を参照したところ、dm_exec_requestsとdm_exec_connectionsのJOINにはsession_idではなくconnection_idを使うべきであることが分かり、修正したところ、正しそうな結果が得られるようになりました。
↓こんな感じの図です。かなり情報がギュッと詰まってますが、文字列検索できるので、目的のDMVにすぐたどり着けます。

--修正後
select top 100
   dest.text as [Command text]
  ,der.total_elapsed_time / 1000.0 / 60 as 'total_elapsed_time' --min
  ,DB_NAME(der.database_id) as DatabaseName
  ,der.command
  ,des.login_time
  ,des.[host_name]
  ,des.[program_name]
  ,der.session_id
  ,SUBSTRING(dest.TEXT, der.statement_start_offset / 2, (
      CASE 
        WHEN der.statement_end_offset = - 1
          THEN LEN(CONVERT(NVARCHAR(MAX), dest.TEXT)) * 2
        ELSE der.statement_end_offset
      END - der.statement_start_offset
      ) / 2) as current_running_stmt
--  ,qp.* -- プランもみたいときはコメント外す
from sys.dm_exec_requests der
inner join sys.dm_exec_connections dec on der.connection_id = dec.connection_id -- ←ここを修正!!
inner join sys.dm_exec_sessions des on des.session_id = der.session_id
cross apply sys.dm_exec_sql_text(sql_handle) as dest
--cross apply sys.dm_exec_query_plan(plan_handle) as qp -- プランもみたいときはコメント外す
where des.is_user_process = 1
--  and　dest.text like '%*****%'
order by [total_elapsed_time] desc

この図を使えば、今までよりDMVへの理解が深まると思います。