SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

sys.sysprocesses を使用しないで「AWAITING COMMAND」となっている接続の情報を取得する

without comments

SQL Server の処理の実行状況を取得するためのシステムテーブルとして sys.sysprocesses というシステムビューが存在しています。

このシステムテーブルは下位互換のビューとして含まれているため、このテーブルを利用しているクエリについては、対応した DMV への移行が推奨されています。

image

sysprocesses については、3 種類の DMV を使用することになるのですが、各 DMV から単純にデータをとるだけでは、sysprocesses で「AWAITING COMMAND」となっているセッションを取得することはできません。

次のクエリで sysprocesses から情報を取得してみます。

select spid, program_name, cmd from sys.sysprocesses where cmd = 'AWAITING COMMAND' 

 

image

「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 種類の情報で管理がされていますので、これらを組み合わせることで下位互換のシステムテーブル相当の情報を取得することができるのではないでしょうか。

Written by Masayuki.Ozawa

4月 22nd, 2020 at 9:38 pm

Posted in SQL Server

Tagged with

Leave a Reply