SE の雑記

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

.Net SqlClient Data Provider のコネクションプールを DMV 等で調べてみる

leave a comment

コネクションプールについて、いろいろと思うところがあり、備忘録として。

開発に携わっている方ですと、トランザクション スコープを使用した暗黙的なトランザクションの実装 と同じように、意識されているのかもしれませんが、DMVの 等で取得できる情報を簡単にまとめておきたいと思います。

ADO.NET の .Net SqlClient Data Provider で、SQL Server に対して接続を行った場合を想定しています。

テスト用のスクリプト


今回、使用するスクリプトは次のようなものです。

Clear-Host
$constr = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$constr["Data Source"] = "localhost"
$constr["Integrated Security"] = $true
$constr["Application Name"] = "PoolTest"
$constr["Initial Catalog"] = "master"
# $constr["Pooling"] = $false

Write-Host "Query #1 Start"
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = $constr.ToString()
$con.Open()
$tran = $con.BeginTransaction([System.Data.IsolationLevel]::RepeatableRead)
$cmd = $con.CreateCommand()
$cmd.Transaction = $tran
$cmd.CommandText = @"
SELECT @@VERSION
WAITFOR DELAY '00:00:10'
"@
[void]$cmd.ExecuteNonQuery()
$tran.Commit()
$con.Close()
$con.Dispose()
Write-Host "Query #1 End"
Start-Sleep -Seconds 10

Write-Host "Query #2 Start"
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = $constr.ToString()
$con.Open()
$cmd = $con.CreateCommand()
$cmd.CommandText = @"
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@VERSION;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
WAITFOR DELAY '00:00:10'
"@
[void]$cmd.ExecuteNonQuery()
$con.Close()
$con.Dispose()
Write-Host "Query #2 End"

Write-Host "Query #3 Start"
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = $constr.ToString()
$con.Open()
$cmd = $con.CreateCommand()
$cmd.CommandText = @"
SELECT @@VERSION
"@
[void]$cmd.ExecuteNonQuery()
$con.Close()
$con.Dispose()
Write-Host "Query #3 Start"

# [System.Data.SqlClient.SqlConnection]::ClearAllPools()

 

クエリで実行している内容


あとで、使用する記述については、コメント化しているものがありますが、

  1. Query #1
    • トランザクション分離レベルを、コネクション側で Repeatable Read に設定し、クエリを実行
  2. Query #2
    • トランザクション分離レベルを、クエリ内で、Repeatable Read で設定し、クエリを実行
    • その後、クエリ内で、Read Uncommitted に設定
  3. Query #3
    • トランザクション分離レベルは明示的に指定せず、クエリを実行

というような流れで処理を実行しています。クエリについては、「@@version」を実行するだけで、細かなロック系については、考慮していません。

 

コネクションプールを使用しない場合の接続の動作


最初に、コネクションプールを使用しない場合の接続の動作を見ていきたいと思います。

上記のスクリプトの「$constr[“Pooling”] = $false」のコメントを外して実行してみます。

Profiler でトレースをした結果が以下になります。

(赤枠がクエリの区切りを表したものです)

「Audit Login」「Batch Completed」「Audit Logout」がセットになって出力されており、ログイン時の「EventSubClass」については、「Nonpooled」となっています。

image

「Nonpooled」なログイン/ログアウトについては、コネクションプールが使用されない接続になりますので、接続を行う都度、接続のフェーズを一から実施していることになります。

(SPID については、使えればすべて同一になることもありますが、上記の例では、異なる SPID になっており、同一の接続が使用されていないことが明確になっていますね)

接続が完了した後は、接続がクローズされますので、次のクエリを実行して、接続の状態を確認しても、クエリを実行している最中のみ、接続の存在が確認でき、クエリが終了して、クローズされた後には接続も削除されている状態となります。

SELECT 
	ec.session_id,
	es.host_name,
	es.status,
	CASE es.transaction_isolation_level
		WHEN 0 THEN 'Unspecified'
		WHEN 1 THEN 'ReadUncomitted'
		WHEN 2 THEN 'ReadCommitted'
		WHEN 3 THEN 'Repeatable'
		WHEN 4 THEN 'Serializable'
		WHEN 5 THEN 'Snapshot'
	END AS transaction_isolation_level,
	es.program_name,
	es.client_interface_name,
	DATEDIFF(ss, ec.connect_time,GETDATE()) AS con_keep_sec,
	DATEDIFF(ss, es.login_time,GETDATE()) AS con_reuse_sec,
	ec.connect_time,
	es.login_time,
	es.last_request_start_time,
	es.last_request_end_time,
	ec.net_transport,
	ec.protocol_type,
	ec.last_read,
	ec.last_write,
	ec.client_net_address,
	ec.client_tcp_port,
	es.client_version,
	es.context_info,
	es.lock_timeout,
	es.open_transaction_count -- SQL Server 2012 以降
FROM 
	sys.dm_exec_connections AS ec
	LEFT JOIN sys.dm_exec_sessions AS es
		ON es.session_id = ec.session_id
WHERE
	es.is_user_process = 1
	AND
	es.program_name = 'PoolTest'
ORDER BY
	session_id

 

クエリが完了した場合は、以下のように接続が切断された状態となっています。

image

 

コネクションプールを使用した場合の接続の動作


それでは「$constr[“Pooling”] = $false」をコメント化し、コネクションプーリングを有効化して再度、スクリプトを実行してみます。

初回については、「Nonpooled」となっており、プール外から接続が取得されていますが、プールを使用していない場合と異なり、「Logout」については実施されていません。

image

ここからがコネクションプーリングを有効にした場合の特徴になるのですが、プールが有効な場合は、クローズをしてもプールに戻されるだけで、ログアウトの処理は実施されません。

ログアウトが発生するのは、プールから接続が再利用されたタイミングとなり、ログアウトを実施する際には「sp_reset_connection」も併せて実行し、「接続の初期化」→「ログアウト」→「ログイン」というような処理の流れとなります。

2 回目以降の接続について、プール内から利用されたものなのかどうかについては、EventSubclassが「Pooled」になっているかどうかで確認できます。

プールが無効な状態では、処理が完了した後に、接続の情報を取得しても何も取得されませんでしたが、プールを使用している場合は、プール内での保持期間であれば、処理が完了しても、SQL Server 上の接続については削除されず待機状態として保持されていることが確認できます。

(プールの保持期間が過ぎると Audit Logout が実行され、SQL Server 上の接続も削除されます)

image

コネクションプールにより保持されている接続については、上記のクエリの「connect_time」と「login_time」を比較することで判断できるかと。

「connect_time」は接続を実施したタイミングとなり、接続が作成されたタイミングと考えることができます。

「login_time」は最後にログインを実施したタイミングになりますので、コネクションプールから接続が取得され、ログインが行われた場合、初回については「connect_time」と差は少ないのですが、ある程度の差が出ている場合は、プーリングされた後に再利用された接続と考えることができますので、ここから再利用された接続なのかを判断することができます。

 

sp_reset_connection で初期化される内容


SQL Server Profiler でクエリのトレースを取得すると、よく見ることがある「sp_reset_connection」ですが、どのような情報 / コンテキストが初期化されているかというと、詳細な情報は公式では公開されていません。

詳しい情報としては Few Things About Pooled Connections が該当するのではないでしょうか。

この記事の中で、sp_reset_connection が実行されたことにより、初期化される設定について、次のように記載されています。

sp_reset_connection resets the following aspects of a connection:

  1. It resets all error states and numbers (like @@error)
  2. It stops all EC’s (execution contexts) that are child threads of a parent EC executing a parallel query
  3. It will wait for any outstanding I/O operations that is outstanding
  4. It will free any held buffers on the server by the connection
  5. It will unlock any buffer resources that are used by the connection
  6. It will release all memory allocated owned by the connection
  7. It will clear any work or temporary tables that are created by the connection
  8. It will kill all global cursors owned by the connection
  9. It will close any open SQL-XML handles that are open
  10. It will delete any open SQL-XML related work tables
  11. It will close all system tables
  12. It will close all user tables
  13. It will drop all temporary objects
  14. It will abort open transactions
  15. It will defect from a distributed transaction when enlisted
  16. It will decrement the reference count for users in current database; which release shared database lock
  17. It will free acquired locks
  18. It will releases any handles that may have been acquired
  19. It will reset all SET options to the default values
  20. It will reset the @@rowcount value
  21. It will reset the @@identity value
  22. It will reset any session level trace options using dbcc traceon()

sp_reset_connection will NOT reset:

  1. Security context, which is why connection pooling matches connections based on the exact connection string
  2. If you entered an application role using sp_setapprole, since application roles can not be reverted

Microsoft のブログでは、sp_reset_connection Does NOT Reset TRANSACTION ISOLATION LEVEL: Unexpected Behavior By Design という記事も公開されており、トランザクション分離レベルも、リセットされない対象に入っているのですよね。

ということで、今回は分離レベルにフォーカスにあててみます。

 

コネクションのリセットとトランザクション分離レベル


コネクションプールを有効にした状態で、スクリプトを実行してみます。

  1. Query #1
    • トランザクション分離レベルを、コネクション側で Repeatable Read に設定し、クエリを実行
  2. Query #2
    • トランザクション分離レベルを、クエリ内で、Repeatable Read で設定し、クエリを実行
    • その後、クエリ内で、Read Uncommitted に設定
  3. Query #3
    • トランザクション分離レベルは明示的に指定せず、クエリを実行

という順で実行していますが、3 のタイミングで、トランザクション分離レベルとしては何が使用されるでしょうか。

各クエリ実行時のトランザクション分離レベルが以下になります。

Query #1 では、「Repeatble Read」を Connection のトランザクション分離レベルとして設定していますので、DMV から取得した情報もそうなっていますね。

image

Query #2 では、クエリ内でトランザクション分離レベルを変更しており、連続的な変更を行っていますので DMV の情報を取得しタイミングでは 2 回目の変更である「Read Uncommitted」となっています。

image

それでは、最後の Query #3 はどうでしょう。

image

デフォルトの「Read Committed」になるかというとそうではなく、「プールから再利用された接続が最後に設定していたトランザクション分離レベル」が引き継がれており、プールが最後に実行されていた分離レベルの状態に依存しています。

以降、トランザクション分離レベルの変更が行われないと、プール内のコネクションに設定されている分離レベルが再利用される形となります。

 

これが「sp_reset_connection」により、トランザクション分離レベルがリセットされないという事象になるかと。

(SQL Server 2014 で一時的にトランザクション分離レベルもリセットされる (Read Committed に戻る) ようになったのですが、2014 CU6 / 2014 CU1 で以前の動作に戻りました)

これも、デザイン的にどういうものが良いのかは悩ましいですが、コネクションプールを使用する場合、同一の接続文字については、トランザクション分離レベルがプールに戻される前の状態が引き継がれることを意識した、トランザクション設定をするのが現状の解となるのでしょうかね。。。

  • トランザクション分離レベルを考慮して、接続文字列を使い分ける (Application Name 等を変えて別プールにさせる)
  • トランザクション分離レベルを意識した処理を実行する必要がある箇所は、明示的に指定する
  • コネクションプールを使用しない

等、いろいろな考え方があるかと思いますが、自分のシステムではどのようになっているのかは、定期的に上記のクエリを実行して、ReadCommitted 以外の分離レベルが使用されている場合、その分離レベルがプール再利用時に、異なる処理に影響を与える可能性があるのかは、一度考えてみてもよいのかもしれません。

 

おまけ) コネクションプールのクリア


コネクションを生成していたプロセスを落とせば、接続も解放されますが、プロセスを終了させなくても「ClearPool」「ClearAllPools」を使用したコネクションプールのクリア という方法も用意されています。

「ClearAllPools」については、プロセスが使用していたコネクションプールをすべてクリアするものになります。

「ClearPool」については、「メソッド実行時に指定した接続のプール」だけクリアをするというのではなく、「接続が使用していた接続文字列の、コネクションプールグループ内のプールをクリアする」というような動作となるようですね。

同一の接続文字列が使用しているプールが、クリア可能なようにマークされるので、「該当の接続のプールだけではない」ということは意識しておいた方が良いのかもしれません。

Written by masayuki.ozawa

8月 8th, 2018 at 5:45 pm

Posted in SQL Server

Tagged with

Leave a Reply

*