サーバ全体の接続情報を確認する：SQL Server動的管理ビューレファレンス（46）

「Microsoft SQL Server」が稼働するデータベースシステムを運用する管理者に向け、「動的管理ビュー」の活用を軸にしたトラブル対策のためのノウハウを紹介していきます。今回は、サーバ全体の接続情報の確認について解説します。

[伊東敏章，＠IT]

SQL Server動的管理ビュー一覧

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は動的管理ビュー「sys.dm_exec_connections」における、サーバ全体の接続情報の確認について解説します。対応バージョンは、SQL Server 2012以降です。

概要

　クライアントアプリケーションがSQL Serverで何らかの要求を実行するためには、事前にSQL Serverに接続する必要があります。

　クライアントアプリケーションからSQL Serverへ接続する方法は単一ではなく、共有メモリや名前付きパイプ、TCPなどの通信プロトコルやSQL認証、NTML認証、Kerberos認証といった認証方式の選択が明示的、または暗黙的に行われています。

　そのため、SQL Serverで通信や接続の問題が発生した場合には、クライアントアプリケーションとSQL Serverがどのような方法で接続されているのか、確認が必要となる場合があります。クエリの実行で問題が発生した場合にも、接続元についての情報を調査するために、接続に関する情報が必要となるでしょう。

　「sys.dm_exec_connections」動的管理ビューでは、サーバ全体で、現在存在している接続情報の一覧の出力が可能です。接続情報には、接続に使用されている通信プロトコルや認証方式、接続元、接続先のIPアドレスやポート番号などの情報が含まれています。

• sys.dm_exec_connections（Transact-SQL）

出力内容

列名	データ型	説明
session_id	int	この接続に関連付けられているセッションの識別 NULL値が許可される
most_recent_session_id	int	この接続に関連付けられた最新の要求セッションID（SOAP接続は別のセッションで再利用できる） NULL値が許可される
connect_time	datetime	接続が確立されたタイムスタンプ
net_transport	nvarchar（40）	この接続で使用される物理的な転送プロトコル 接続で複数のアクティブな結果セット（MARS）が有効な場合、「Session」が返される
protocol_type	nvarchar（40）	ペイロードのプロトコルタイプ 「TDS」（TSQL）と「SOAP」が区別される
protocol_version	int	この接続に関連付けられたデータアクセスプロトコルのバージョン
endpoint_id	int	接続の種類を区別する識別子
encrypt_option	nvarchar（40）	この接続で暗号化が有効かどうかを表すブール値を表す文字列（'TRUE'または'FALSE'）
auth_scheme	nvarchar（40）	この接続のSQL Server／Windows認証スキーム
node_affinity	smallint	接続がアフィニティを持つメモリノードを識別
num_reads	int	この接続で発生した読み取りバイト数
num_writes	int	この接続で発生した書き込みバイト数
last_read	datetime	この接続で最後の読み取りが発生したときのタイムスタンプ
last_write	datetime	この接続で最後の書き込みが発生したときのタイムスタンプ
net_packet_size	int	情報、データ転送に使用されるネットワークパケットサイズ
client_net_address	varchar（48）	クライアントコンピュータのホストアドレス V12より前のAzure SQLデータベースでは常に「NULL」
client_tcp_port	int	クライアントコンピュータ上のポート番号 Azure SQLデータベースでは常に「NULL」
local_net_address	varchar（48）	この接続の対象となったサーバIPアドレス TCPトランスポートを使用した接続の場合に表示される Azure SQLデータベースでは常に「NULL」
local_tcp_port	int	この接続で使用されるサーバ側のTCPポート番号 TCPトランスポートを使用した接続の場合に表示される Azure SQLデータベースでは常に「NULL」
connection_id	uniqueidentifier	各接続を一意に識別する識別子
parent_connection_id	uniqueidentifier	「MARS」セッションが使用しているプライマリ接続の識別子
most_recent_sql_handle	varbinary（64）	この接続で実行された最新の要求のSQLハンドル 常に「most_recent_session_id」列と同期される

出力例

　SQL Server構成マネジャーからSQL Serverネットワーク（サーバ側）およびSQL Native Client（クライアント側）で、共有メモリと名前付きパイプ、TCP／IPを有効化します（図1）。

図1　SQL Server側（上）とクライアント側（下）で各転送プロトコルを有効化したところ

　それぞれの転送プロトコルを使用してSQL Serverに接続し、「sys.dm_exec_connections」動的管理ビューを出力します（図2）。

図2　「sys.dm_exec_connections」動的管理ビューにより接続の一覧が表示された

　存在している全ての接続が表示されました。接続が使用する転送プロトコルや認証方法などの情報を一覧から確認できます。「endpoint_id」列の値と「sys.endpoints」システムビューの情報を照らし合わせることで、接続の種類についての詳細を確認できます（図3）。1つの接続は管理者接続（DAC）を使用して接続されていることが分かります。

図3　「sys.endpoints」では、接続の種類の一覧や接続の待機情報を確認できる

　各接続を終了し、複数のアクティブな結果セット（MARS：Multiple Active Result Set）を有効化して出力を確認してみます。複数の結果セットを保持した状態で「sys.dm_exec_connections」動的管理ビューを出力すると、「net_transport」列の値が「Session」となり、「session_id」が他の行と重複しています（図4、図5）。

図4　「PowerShell」でMARS機能を使用して2つの結果セットを使用したところ

図5　MARSにより、同じ「session_id」を持つ4つの行が出力された

　「sys.dm_exec_connections」動的管理ビューを使用して、アプリケーションの接続プールの動作も確認できます。接続プールを有効化して接続した後、接続が出力されたら終了し、「sys.dm_exec_connections」動的管理ビューを出力すると、終了した接続の情報が残っていることが分かります（図6、図7）。

図6　PowerShellで接続し、「52」というセッションIDを確認して接続を終了した

図7　接続を終了した「52」というセッションIDが残っていた

　もう一度、同じ接続文字列を使用して接続し、「sys.dm_exec_connections」動的管理ビューを出力します（図8、図9）。

図8　PowerShellの同じ画面を使用して、もう一度接続して「52」というセッションIDを確認した

図9　「52」というセッションIDが再利用された

　再接続する前の「connection_id」列や「connect_time」列の値から更新されておらず、接続プールを使用した接続の再利用時には、接続時刻は更新されないようです。また、「num_reads」列や「num_writes」列の値が増加していることから、読み取り、書き込みバイト数は再利用前からの累積値となるようです。

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

筆者紹介

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

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

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

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