今年は基本的なことをきちんと見直していこうかと。
SQL Server / SQL Database の「コネクション」(接続) と「セッション」を確認するための動的管理ビュー (DMV) としては以下のようなものがあります。
Contents
■コネクション
最初に、以下のようなスクリプトを実行した場合に、各状態がどのようになるかを見てみたいと思います。
$Query = "SELECT @@VERSION" $constring = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $constring.psbase.DataSource = "localhost" $constring.psbase.IntegratedSecurity = $true $constring.psbase.ApplicationName = "PoshSQLTest-01" $con = New-Object System.Data.SqlClient.SqlConnection $con.ConnectionString = $constring $con.Open() $cmd = $con.CreateCommand() $cmd.CommandType = [System.Data.CommandType]::Text $cmd.CommandTimeout = 5 $cmd.CommandText = $Query $rs = $cmd.ExecuteReader() $rs.read() Write-Output $rs[0] $con.Close() $con.Dispose()
このスクリプトは単純に「SELECT @@VERSION」を実行して表示するものとなります。
上記のスクリプトでは「ApplicationName」として「PoshSQLTest-01」を指定していますので、以下のようなクエリを実行することで、上記のスクリプトのコネクションとセッションの情報を確認することができます。
select s.session_id, c.net_transport, c.connection_id, c.parent_connection_id, c.session_id, c.most_recent_session_id, s.program_name, c.local_tcp_port, c.client_tcp_port from sys.dm_exec_sessions AS s inner join sys.dm_exec_connections AS c on s.session_id = c.session_id where s.session_id > 50 and program_name = 'PoshSQLTest-01' order by s.session_id
今回は、シンプルなクエリの実行方法ですので、以下のような状態となっていることを確認することができます。
- 一つの接続から、一つのセッションを用いて、クエリが実行されている
- TCP を使用して、サーバーのポート (local_tcp_port) 1433 に、クライアントのポート (client_tcp_port) 57468 を使用して接続が行われている
スクリプトを複数同時に実行した場合は以下のようになります。
サーバーのポートは単一ですので、1433 が使用されていますがクライアントのポートに関しては複数が使用されていることが確認できますね。
コネクションにはいくつかの上限がありますので、これについても見ていきたいと思います。
サーバー側の設定による上限 (サーバー側の接続数上限)
一つがサーバー側の接続数の上限となります。
SQL Server であれば、デフォルトの設定は同時接続は無制限となっていますが、サーバー側でコネクションの上限を設定することも可能です。
SQL Database の場合は、パフォーマンスレベルに応じて変わってきます。
Azure SQL Database のリソース制限
パフォーマンスレベルに応じてコネクションとセッションの上限が決まっており、この節の内容はコネクションの上限と関連がある内容となります。
サーバー側のコネクションの設定の上限に達している接続がすでに行われている場合、新規の接続を完了することができなくなります。
以下は一例ですが、接続数の上限に達している状態で接続を行おうとした場合は以下のようなエラーが発生します。
# 上が SQL Server / 下が SQL Database の接続上限のエラーとなります。
サーバーとの接続を正常に確立しましたが、ログイン前のハンドシェイク中にエラーが発生しました。 (provider: TCP Provider, error: 0 – 指定されたネットワーク名は利用できません。)
Resource ID : 1. The request limit for the database is 30 and has been reached. See ‘http://go.microsoft.com/fwlink/?LinkId=267637′ for assistance.
このようなエラーが発生している場合は、サーバーで許容できる接続の数が枯渇している状態になっていると判断することができるかと。
クライアント (接続元) の設定による上限 (コネクションプール)
もう一つ気にしておく上限としては「コネクションプール」(接続プール) の上限があります。
コネクションプールについて以下の情報が参考になります。
「タイムアウトに達しました。プールから接続を取得する前にタイムアウト期間が過ぎました。プールされた接続がすべて使用中で、プール サイズの制限値に達した可能性があります。」エラーの対処方法
SQL Server の接続プール (ADO.NET)
先ほどの設定は「SQL Server 側」(接続先サーバー) の設定でしたが、コネクションプールに関しては「クライアント側」(接続元アプリケーション) の設定となります。
コネクションプールでは一度開かれたコネクションを、一定の期間プールとして維持します。
そのためコネクションプールが有効な場合は、Connection.Close を実行しても、接続はプールに返却がされる動作となり、バックグラウンドは接続中の状態となるため、プールが破棄されるまでは、実際の接続の解放は行われていない状態となります。
SQL Server の接続プール (ADO.NET) では以下のように記載されています。
接続プールは、新しく開く必要のある接続の数を減らします。 プーラーには、物理的な接続の所有権が保持されます。プーラーは、任意の接続構成それぞれのアクティブな接続のセットをそのまま保持して、接続を管理します。この接続の Open を呼び出すと、プーラーは、プールに使用可能な接続があるかどうかを確認します。プールされた接続が使用できる場合は、新しい接続を開く代わりに、プールされた接続を呼び出し元に返します。アプリケーションが接続で Close を呼び出すと、プーラーは接続を閉じる代わりに、プールされたアクティブな接続のセットに接続を返します。接続がプールに返されると、その接続は、次の Open 呼び出しで再度使用できる状態になります。
ADO.NET では、コネクションプールはデフォルトでは有効な状態になっています。
そのため、先ほどのスクリプトの実行が完了しても一定の期間は、コネクションプールにより接続が行われた状態となっています。
先ほどのスクリプトに、
$constring.psbase.Pooling = $false
を追加して実行してみると違いが判るかと思います。
コネクションプールが有効な場合、スクリプトの実行が完了しても一定期間はコネクションが残った状態となります。
しかし「Pooling」を「false」に設定することでコネクションプールが無効となり、クローズ時に接続が解放される動作となりますので、スクリプトの実行が完了した後に、コネクションの状態を確認するクエリを実行してみると、コネクションがなくなっていることが確認できます。
一般的に、コネクションの作成 / 解放についてはコストが高いと言われているかと思います。
コネクションプールは一度作成したコネクションを再利用することで上記のコストを緩和させるための仕組みですので、通常はコネクションプールを無効にすることはないかと思いますが、動作の違いについては覚えておいた方がよいかと。
コネクションプールの解放
具体的なロジックについては記載されていませんが、SQL Server の接続プール (ADO.NET) では、コネクションプールからコネクションを解放するための動作として、以下のように記載されています。
接続プール機能は、アイドル状態の時間が約 4-8 分になったか、サーバーとの接続が切断されたことをプール機能が検出した場合に、プールからの接続を削除します。サーバーとの通信を試みた後にのみ、切断されたサーバー接続が検出可能になることに注意してください。接続がサーバーに接続していないことがわかると、その接続は無効としてマークされます。無効な接続は、閉じられるか、または再利用された場合のみ、接続プールから削除されます。
既に存在しないサーバーへの接続が存在する場合は、接続プーラーが、その接続が切断されていることをまだ検出せず、無効というマークを付けていない状況のときでも、プールからその接続を削除できます。この機能は、接続がまだ有効であることを確認するオーバーヘッドによってサーバーへのラウンド トリップが実行されることにより、プーラーの利点が失われてしまうことを防ぐためにあります。この状況が発生した場合は、接続の使用を最初に試みたときに接続が切断されていることが検出され、例外がスローされます。
基本的にはプール側の動作にお任せする感じとはなります。
それでは、AlwaysOn 可用性グループで以下のような動作を行ってみます。
- Server 1 がプライマリの状態にする
- スクリプトから接続を行う
- Server 2 にプライマリをフェールオーバー
- スクリプトから接続を行う
- スクリプトから接続を行う
1.2.3. については、特筆すべき動作はありません。
4. を実行した際には以下のようなエラーとなることがあります。
サーバーから結果を受信しているときに、トランスポート レベルのエラーが発生しました。 (provider: TCP Provider, error: 0 – セマフォがタイムアウトしました。)
Server 1 → Server 2 にフェールオーバーしたタイミングで、接続元の実体が変わりますが、コネクションプールではフェールオーバー前のサーバーに対して接続が行われています。
そのため、4. を実行したタイミングではエラーとなりプールの解放が行われ、2 回目の実行である 5. を実行したタイミングで成功をするケースがあります。
フェールオーバー直後に再接続を行った場合などは、上記のケースに該当するかと思います。
少し時間をおいてから、スクリプトを実行した場合はエラーは発生しないかと思いますので、フェールオーバー直後の接続のリトライについては意識をしておいた方がよいかもしれないですね。
これについては Entity Framework 6 で Azure の SQL データベースにアクセスするとセマフォがうんたら言われる問題への対処 も参考になります。
ADO.NET 2.0 では、以下のような機能が追加されています。
ADO.NET 2.0 では、プールをクリアするために、ClearAllPools と ClearPool という 2 つの新しいメソッドが導入されました。 ClearAllPools は、指定されたプロバイダーの接続プールをクリアし、ClearPool は、特定の接続に関連付けられた接続プールをクリアします。これらのメソッドが呼び出されたときに使用中の接続がある場合は、適切にマーク付けされ、接続が閉じられるとプールに返されずに破棄されます。
以下のような方法で、コネクションプールの明示的なクリアを実施できるようになっていますので、検証時などにプールに接続のあり/なしを意識する必要がある場合は、明示的なクリアを検討してもよいかと。
[System.Data.SqlClient.SqlConnection]::ClearAllPools()
コネクションプールの上限
ADO.NET では、デフォルトでコネクションプールの最大は 100 が設定されているため、一つのアプリケーション (クライアント) からは、同一の接続文字列で最大で 100 の接続をコネクションプールとしてプールできる形となります。
先人の知恵をお借りして作成してみた Start-AsyncSQLQueryTest.ps1 を、以下のパラメーターで実行してみます。
以下を参考人させていただきました！！
$Param = @{
ConnectionString = "Data Source=localhost;Integrated Security=true;Pooling=True;Connection Timeout=15;Application Name=AsyncSQLQueryTest;"
Query = "WAITFOR DELAY '00:00:50'"
RunspaceSize = 120
CommandTimeout = 60
}
$sw = [system.diagnostics.stopwatch]::startNew()
Start-AsyncSQLQueryTest @Param
$sw | fl
$sw.Stop()
上記のパラメーターで実行した際のスクリプトからの接続数は「100」となっています。
今回は 50 秒待機するようなクエリを実行しています。
一度クエリを実行すると実行が完了するまでは時間がかかるため、接続が長時間利用されている状態がコネクションプールの上限である 100 接続維持されます。
RunspaceSize を 120 にしているため、最終的には、120 の接続が行われた状態となります。
最初の 100 接続については、コネクションプールの範囲内なので、SQL Server 側の接続数の設定範囲内であれば、接続を行うことができます。
残りの 20 接続については、上限が 100 に設定されたコネクションプールの確保待ちの状態となり、確保待ちが続くとコネクションプールの確保に関してのタイムアウトとなります。
タイムアウトに達しました。プールから接続を取得する前にタイムアウト期間が過ぎました。プールされた接続がすべて使用中で、プール サイズの制限値に達した可能性があります。
これについては上で紹介した、SQL Server の接続プール (ADO.NET) の以下の記述の動作ですね。
接続プーラーは、接続がプールに解放されたときに接続の再割り当てを行って、接続に対する要求に応えます。最大プール サイズに達すると、使用可能な接続を取得できなくなり、要求はキューに置かれます。プーラーは、タイムアウト (既定は 15 秒) に達するまで接続の再利用を試みます。接続がタイムアウトになる前に、プーラーが要求を満たすことができない場合は、例外がスローされます。
この状態になった場合は、コネクションプールの上限を変更するか、各接続で実行されているクエリが短時間で終了するようにして、コネクションが再利用できるようにします。
SQL Database の場合は、パフォーマンスレベルに応じて接続数が変わってきますので、SQL Database の接続元のコネクションプールの数を考慮して、何接続が行われるかの検討が必要になってくるかと。
コネクションプールの利用状況
最後に、コネクションプールの利用状況を確認してみたいと思います。
今回使用しているスクリプトでは、「.NET Data Provider for SqlServer」を使用して接続を行っています。
この場合、接続元となるアプリケーションを実行しているクライアントで、ADO.NET でのパフォーマンス カウンター の情報を確認することで、コネクションプールがどの程度利用されているのかを確認することができます。
# この辺は接続に使用しているライブラリに応じて取得方法が変わってくるかと思いますが。
以下の画像は「WAITFOR DELAY」を使用して、100 の接続が同時の行われた状態のコネクションプールの状態となります。
コネクションプールが 100 使われ、SQL Server 側のコネクションとしても 100 コネクションあることが確認できますね。
コネクションプールは接続文字列単位となるため、上記の状態から接続文字列を一部変更して接続を行うと以下のような状態となります。
「ConnectionPoolGroups」や「ConnectionPools」から、プールがどの程度確保されており (使用している接続文字列の数の判断に使えるかもしれませんね)、プール内のコネクションの数を「NumberOfPooledConnections」から確認することができます。
Windows 認証を使用して接続を行っている場合は、ユーザー単位にプールが作成されるようですので、接続の方式がプールの数が変化する要因になるというのも意識をしておいた方がよさそうですね。
接続は、接続文字列とユーザー ID に基づいてプールされます。したがって、Web サイトで基本認証または Windows 認証を使用していて、統合セキュリティ ログインを使用している場合は、1 ユーザーにつき 1 つのプールが作成されます。これによって、単一のユーザーによる後続のデータベース要求のパフォーマンスが向上しますが、他のユーザーによって作成された接続は使用できません。また、1 ユーザーにつき少なくとも 1 つの接続がデータベース サーバーに存在することになります。これは、開発者がセキュリティおよび監査要件に対して、特定の Web アプリケーションのアーキテクチャに重点を置く必要があるために起こる副作用です
■セッション
上記では、一接続が一セッションとなるようなシンプルな実行を行っていました。
SQL Server では、MARS (Multiple Active Result Sets) を使用すると、単一の接続を利用して、複数のセッションを使用することができるようになります。
単純なスクリプトとしては以下のようなものとなるでしょうか。
$Query = "SELECT @@VERSION" $constring = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $constring.psbase.DataSource = "localhost" $constring.psbase.IntegratedSecurity = $true $constring.psbase.MultipleActiveResultSets = $true $constring.psbase.ApplicationName = "PoshSQLTest-02" $con = New-Object System.Data.SqlClient.SqlConnection $con.ConnectionString = $constring $con.Open() $cmd = $con.CreateCommand() $cmd2 =$con.CreateCommand() $cmd.CommandType = $cmd2.CommandType = [System.Data.CommandType]::Text $cmd.CommandTimeout = $cmd2.CommandTimeout = 5 $cmd.CommandText = $cmd2.CommandText = $Query $rs = $cmd.ExecuteReader() $rs.read() Write-Output $rs[0] $rs2 = $cmd2.ExecuteReader() $rs2.read() Write-Output $rs2[0] $con.Close() $con.Dispose()
実行後に、以下のクエリで情報を取得してみます。
select s.session_id, c.net_transport, c.connection_id, c.parent_connection_id, c.session_id, c.most_recent_session_id, s.program_name, c.local_tcp_port, c.client_tcp_port from sys.dm_exec_sessions AS s inner join sys.dm_exec_connections AS c on s.session_id = c.session_id where s.session_id > 50 and program_name = 'PoshSQLTest-02' order by s.session_id
親となる接続を一つ持ち、その接続を利用した複数のセッションが設定されているという状態が確認できます。
この時のポートの利用状況ですが、各セッションはサーバー/クライアントともに接続に使用したポートを利用しているため、一つのコネクションを複数のセッションで利用している状態となっているかと思います。
それでは、SQL Server の接続を 50 にした状態で、以下のパラメーターで Start-AsyncSQLQueryTest を実行してみたいと思います。
$Param = @{
ConnectionString = "Data Source=localhost;Integrated Security=true;Pooling=True;Connection Timeout=15;Application Name=AsyncSQLQueryTest;MultipleActiveResultSets=True"
Query = "WAITFOR DELAY '00:00:50'"
RunspaceSize = 100
CommandTimeout = 60
}
$sw = [system.diagnostics.stopwatch]::startNew()
Start-AsyncSQLQueryTest @Param
$sw | fl
$sw.Stop()
実行の結果としては、49 が成功し、51 がエラーとなりました。
この時の状態を以下のクエリで取得してみます。
select net_transport, count(*) from sys.dm_exec_sessions AS s inner join sys.dm_exec_connections AS c on s.session_id = c.session_id where s.session_id > 50 and program_name = 'AsyncSQLQueryTest' group by net_transport
このような実行形態の場合は、一接続から複数のセッションが使用された状態となります。
49 の TCP コネクションが接続され、98 のセッションが存在していることが確認できますね。
# 1 コネクション分は、上記のクエリを実行するために事前に使用しているため、上限 50 接続のうち 49 接続が利用できた形となります。
コネクションとセッションは別で管理されていることがこの情報から確認することができるかと思います。
# この情報から、接続数の上限はセッションではなくコネクション側で効いていることが確認できるかと。
MARS が有効でない場合は以下のような結果となります。
この場合は、コネクション内のセッションで処理が完結するイメージでしょうか。
MARS が有効な場合、単一のクエリ実行でも 1 TCP Connection / 2 Session は作成されているようで、単純に「MultipleActiveResultSets」を有効にしただけでもこのような動作の違いは出てくるようです。
接続数の上限がある場合、一つの接続の中で複数のセッションを使用したクエリ実行というのは、接続の上限がある中での複数クエリを実行する方法として、覚えておいた方がよいのかもしれないですね。
MARS の場合、 待ち事象の一つである、『 TRANSACTION_MUTEX 』 を取り上げてみる で言及されているような内部の MUTEX の待ちが発生することがあるそうなので、ここも意識しておく必要がありそうです。
SQL Database ではコネクションだけでなくセッションの上限も決まっているため、セッション数についても意識をしておく必要があるかと思います。
DMV のレコードの関係
現状は SQL Server 2012 までですが、SQL Server 2012 System Views Map で DMV をはじめとした各種ビューの関連性があらわされた資料が公開されています。
ここに、sys.dm_exec_connections と、sys.dm_exec_sessions の関連性も記載されているのですが、この二つのビューの関連性としては、
- 1 : sys.dm_exec_sessions
- 多 : sys.dm_exec_connections
となっており、session_id をキーとした、1 対 多の関係となっていることが確認できます。
複数セッションで実行した場合は、connections 側に複数レコードが作成されているので、この関係になるのですが、初見だと、connections : 1 に対して、sessions が複数というようなイメージを持ってしまうかもしれないですね。
基本的なことをきちんと理解しないとなと思った今日この頃です。