利用状況モニターのプロセスを出力するSQL文。
┌──────────────────────────────────────┐
│SELECT s.[session_id]                        AS [セッションID],             │
│       CONVERT(char(1), s.[is_user_process]) AS [ユーザープロセス],         │
│       s.[login_name]                        AS [ログイン],                 │
│       CASE                                                                 │
│         WHEN p.[dbid] = 0 THEN N''                                         │
│         ELSE                   ISNULL(DB_NAME(p.[dbid]), N'')              │
│       END AS [データベース],                                               │
│       ISNULL(t.[task_state], N'')           AS [タスクの状態],             │
│       ISNULL(r.[command], N'')              AS [コマンド],                 │
│       ISNULL(s.[program_name], N'')         AS [アプリケーション],         │
│       ISNULL(w.[wait_duration_ms], 0)       AS [待機時間(ミリ秒)],         │
│       ISNULL(w.[wait_type], N'')            AS [待機の種類],               │
│       ISNULL(w.[resource_description], N'') AS [待機リソース],             │
│       ISNULL(CONVERT(varchar, w.[blocking_session_id]), '')                │
│           AS [ブロック元],                                                 │
│       CASE                                                                 │
│         WHEN r2.[session_id] IS NOT NULL                                   │
│          AND (r.[blocking_session_id] = 0 OR r.[session_id] IS NULL) THEN  │
│           '1'                                                              │
│         ELSE                                                               │
│           ''                                                               │
│       END AS [先頭ブロック],                                               │
│       s.[cpu_time]                          AS [Total CPU(ms)] ,           │
│       (s.[reads] + s.[writes]) * 8 / 1024   AS [Total Physical I/O(MB)],   │
│       s.[memory_usage] * (8192 / 1024)      AS [メモリ使用量(KB)],         │
│       ISNULL(r.[open_transaction_count], 0) AS [Open Transactions],        │
│       s.[login_time]                        AS [Login Time],               │
│       s.[last_request_start_time]           AS [Last Request Start Time],  │
│       ISNULL(s.[host_name], N'')            AS [ホスト名],                 │
│       ISNULL(c.[client_net_address], N'')   AS [Net Address],              │
│       ISNULL(t.[exec_context_id], 0)        AS [Execution Context ID],     │
│       ISNULL(r.[request_id], 0)             AS [Request ID],               │
│       ISNULL(g.[name], N'')                 AS [Workload Group]            │
│    FROM [sys].[dm_exec_sessions] AS s                                      │
│        LEFT JOIN [sys].[dm_exec_connections] AS c                          │
│          ON s.[session_id] = c.[session_id]                                │
│        LEFT JOIN [sys].[dm_exec_requests] AS r                             │
│          ON s.[session_id] = r.[session_id]                                │
│        LEFT JOIN [sys].[dm_os_tasks] AS t                                  │
│          ON  r.[session_id] = t.[session_id]                               │
│          AND r.[request_id] = t.[request_id]                               │
│        LEFT JOIN (                                                         │
│            SELECT *,                                                       │
│                   ROW_NUMBER() OVER (PARTITION BY [waiting_task_address]   │
│                                      ORDER BY [wait_duration_ms] DESC      │
│                   ) AS [row_num]                                           │
│                FROM [sys].[dm_os_waiting_tasks]                            │
│        ) AS w                                                              │
│          ON  t.[task_address] = w.[waiting_task_address]                   │
│          AND w.[row_num]      = 1                                          │
│       LEFT JOIN [sys].[dm_exec_requests] AS r2                             │
│         ON s.[session_id] = r2.[blocking_session_id]                       │
│       LEFT JOIN [sys].[dm_resource_governor_workload_groups] AS g          │
│         ON g.[group_id] = s.[group_id]                                     │
│       LEFT JOIN [sys].[sysprocesses] AS p                                  │
│         ON s.[session_id] = p.[spid]                                       │
│    WHERE s.[is_user_process] = 1                                           │
│    ORDER BY s.[session_id];                                                │
└──────────────────────────────────────┘