アクティブなクエリ通知サブスクリプションに関する情報を出力する：SQL Server動的管理ビューレファレンス（144）

「Microsoft SQL Server」が稼働するデータベースシステムを運用する管理者に向け、「動的管理ビュー」の活用を軸にしたトラブル対策のためのノウハウを紹介していきます。今回は、アクティブなクエリ通知サブスクリプションに関する情報の出力について解説します。

[伊東敏章，＠IT]

SQL Server動的管理ビュー一覧

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は動的管理ビュー「sys. dm_qn_subscriptions」における、アクティブなクエリ通知サブスクリプションに関する情報の出力について解説します。対応バージョンは、SQL Server（サポートされている全てのバージョン）です。

概要

　SQL Serverでは、クエリ通知と呼ばれるクライアントアプリケーションでデータベース上のデータの変更を検出できる仕組みが用意されています。

　クエリ通知では、あらかじめクライアントアプリケーションがデータ変更を検出したいクエリを、クエリ通知サブスクリプションとしてSQL Serverに登録します。クライアントアプリケーションは、データベースでデータ変更が発生すると、Service Brokerの機能を通してイベント通知を受け取り、任意の処理を実行できるようになります。クエリ通知の機能は、Webアプリケーションなどデータベースからの情報のキャッシュを使用するアプリケーションで、データソースの更新の検出などに使用できます。

　「sys.dm_qn_subscriptions」動的管理ビューでは、SQL Serverインスタンスに現在登録されているアクティブなクエリ通知サブスクリプションの一覧を出力できます。

• クエリ通知−sys.dm_qn_subscriptions

出力内容

列名	データ型	説明
id	int	サブスクリプションID
database_id	int	通知クエリが実行されたデータベースのID
sid	varbinary（85）	このサブスクリプションを作成して所有しているサーバプリンシパルのセキュリティID
object_id	int	サブスクリプションパラメーターに関する情報を格納する内部テーブルのID
created	datetime	サブスクリプションが作成された日時
timeout	int	サブスクリプションのタイムアウト（秒単位） この時間が経過すると、通知のフラグが設定される
status	int	サブスクリプションの状態を示すコード コードの一覧については、「クエリ通知−sys.dm_qn_subscriptions」を参照

動作例

　クエリ通知の準備のため、データベースでサービスブローカーを有効化します（図1）。

図1　ALTER DATABASEコマンドによりサービスブローカーを有効化したところ

　クエリ通知はT-SQLからの使用はできないため、C#で.NET Frameworkを使用したアプリケーションを作成します。複雑なクエリ通知は使用しないため、SqlDependencyクラスを使用して、既定のService Brokerキューを使用するテストコードを作成しました（図2）。

図2　SqlDependencyクラスを使用してクエリ通知を実装したところ

　作成したアプリケーションコードを実行し、「sys.dm_qn_subscriptions」動的管理ビューを出力すると、クエリ通知サブスクリプションの情報が1行表示されました（図3）。「status」列の値からこのサブスクリプションの状態が「199168」（サブスクリプションがアクティブ）であることが確認できます。

図3　「sys.dm_qn_subscriptions」動的管理ビューを出力したところ

　クエリ通知対象のテーブルにデータを挿入すると、アプリケーションのSqlDependencyオブジェクトで通知イベントが発生しました。通知イベントが発生するとサブスクリプションは削除されるため、「sys.dm_qn_subscriptions」動的管理ビューを出力しても情報は出力されなくなっていました（図4）。

図4　通知イベントの発生後に「sys.dm_qn_subscriptions」動的管理ビューを出力したところ

※本Tipsは、「Windows Server 2019」上に「SQL Server 2019」をインストールした環境を想定して解説しています。

筆者紹介

椎名 武史（しいな たけし）

BIPROGY株式会社（ビプロジー）所属。Microsoft MVP for Data Platform（2017〜）。入社以来 SQL Serverの評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。

伊東 敏章（いとう としあき）

BIPROGY株式会社（ビプロジー）所属。入社以来SQL Server一筋で評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。社内のプログラミングコンテストで4回の優勝経験も持つ。趣味は輪行で週末は自転車を持っての旅行。目標は色々な日本百選を制覇すること。