SQL Server の処理の実行状況を取得するためのシステムテーブルとして sys.sysprocesses というシステムビューが存在しています。
このシステムテーブルは下位互換のビューとして含まれているため、このテーブルを利用しているクエリについては、対応した DMV への移行が推奨されています。
sysprocesses については、3 種類の DMV を使用することになるのですが、各 DMV から単純にデータをとるだけでは、sysprocesses で「AWAITING COMMAND」となっているセッションを取得することはできません。
次のクエリで sysprocesses から情報を取得してみます。
select spid, program_name, cmd from sys.sysprocesses where cmd = 'AWAITING COMMAND'
「AWAITING COMMAND」となっているセッションが取得できていますね。
AWATING COMMAND ですが、コマンドの待機状態となっており、接続は行われているのですが、コマンドの要求待ちとなっている状態のセッションとなります。
例えば、SSMS から次のクエリを実行してみます。
BEGIN TRAN
SELECT TOP 1 * FROM T1 WITH(XLOCK) WHERE C1 = 10
このクエリは、トランザクションを開始しているのですが、COMMIT を実行することなく待機状態となっているクエリです。
このようなクエリの実行を行った場合、「接続が行われているが、アプリケーションからコマンドが送信されて来ず待機している」状態となり、sys.sysprocesses では、AWAITING COMMAND となります。
XACT_ABORT AND QUERY TIMEOUT IN SQL SERVER のような状態となった場合も、トランザクションが開かれているが AWAITING COMMAND となっている状態としても確認ができるかと思います。
sysprocesses は
- 接続 (sys.dm_exec_connections)
- セッション (sys.dm_exec_sessions)
- 実行中の要求 (sys.dm_exec_requests)
の 3 種類が組み合わさった情報が取得できますが、DMV については、各情報について独立して情報の取得が行われます。
AWAITING COMMAND となっていた状態のセッションについては、「セッションとして存在するが実行中の要求が存在しない」状態として把握することができます。
例としては次のようなクエリとなります。
select * from sys.dm_exec_sessions AS es where is_user_process = 1 and not exists (
select 1 from sys.dm_exec_requests AS er where er.session_id = es.session_id
)
select * from sys.dm_exec_sessions AS es where is_user_process = 1 and session_id not in(
select session_id from sys.dm_exec_requests
)
「sys.dm_exec_sessions には存在しているが、sys.dm_exec_requests には存在していないセッション」=「接続はされているがアプリケーションからの要求が開始されるのを待っている状態のセッション」となります。
SSMS からトランザクションを含んだクエリを実行していて、COMMIT or ROLLBACK を SSMS から実行せずに AWAITING COMMAND となっているセッションであれば、次のようなクエリで取得を行うことができます。
select * from sys.dm_exec_sessions AS es where is_user_process = 1 and not exists (
select 1 from sys.dm_exec_requests AS er where er.session_id = es.session_id
) and open_transaction_count > 0
DMV では接続 / セッション / 実行中の要求の 3 種類の情報で管理がされていますので、これらを組み合わせることで下位互換のシステムテーブル相当の情報を取得することができるのではないでしょうか。