SQL Server の Connection Pool について

Database に接続する時に SqlConnection を使っていたんだけど、

毎回インスタンス作っていいの？
Pool とか再利用されてるの？

みたいなが疑問が出てきたのでまとめる。

learn.microsoft.com

をベースに色々試していく。

The pooler maintains ownership of the physical connection.

pooler と呼ばれるオブジェクトが物理レベルでの接続を管理している。接続の生成や破棄は全てこのオブジェクトがやっているのだろう。ここでいう物理接続とは何なのだろうか。

Closed method が呼び出されても、実際には接続を閉じるわけではなく Pool に返しているだけ。んで次に Open が呼び出された時に Pool 内の Active な接続を Pooler くんが渡してくれますと。って事は同じ接続文字列を使うと、Pool 内の接続を使いまわしてくれるって事だと思う。

var connectionString = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=user;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

using (var connection1 = new SqlConnection(connectionString))
{
    await connection1.OpenAsync();
    connection1.ClientConnectionId.Dump();
    connection1.WorkstationId.Dump();
}

using var connection2 = new SqlConnection(connectionString);
await connection2.OpenAsync();
connection2.ClientConnectionId.Dump(); // Dispose してるから1 と一緒
connection2.WorkstationId.Dump();


using var connection3 = new SqlConnection(connectionString);
await connection3.OpenAsync();
connection3.ClientConnectionId.Dump(); // 2 が開きっぱなしなので 1, 2 と異なる
connection3.WorkstationId.Dump();

同じ接続文字列を使って SqlConnection を生成するサンプルコードを書いてみた。

connection1 は scope を抜けたタイミングで Dispose されているので、connection2 と Id は同じである
connection 2 は Dispose されてないので、connection3 とは Id が異なっている

ことがわかった。この例でいうと同じ Database に対する接続は 2 つ作られている。丁寧に Dispose していると同じ接続文字列であれば物理レベルでの接続を使いまわしてくれるって事が分かった。

If Min Pool Size is either not specified in the connection string or is specified as zero, the connections in the pool will be closed after a period of inactivity. However, if the specified Min Pool Size is greater than zero, the connection pool is not destroyed until the AppDomain is unloaded and the process ends. Maintenance of inactive or empty pools involves minimal system overhead.

Pool には寿命があるみたい。MinSize を 0 以外に指定して再生成のコストを安くするのか、一定期間使われない接続は破棄してリソースを節約させるのか、どっちを取るかって判断をしていくかなぁ。

The connection pooler removes a connection from the pool after it has been idle for approximately 4-8 minutes, or if the pooler detects that the connection with the server has been severed. Note that a severed connection can be detected only after attempting to communicate with the server. If a connection is found that is no longer connected to the server, it is marked as invalid. Invalid connections are removed from the connection pool only when they are closed or reclaimed

当たり前かもだけど、切断されたら Pool からは排除されるみたい。しかしアイドルタイムが 4-8 分に達して場合ってのが意外と長かった。

Sever されるのって接続文字列で指定した Connection Timeout の時間が経過した時かと思ったけど違った。SQL Server のタイムアウトには複数種類があって、接続文字列で指定するやつは接続の試行時でのタイムアウトのこと。この記事が分かりやすかった。

接続タイムアウト
クエリタイムアウト

https://ichiroku11.hatenablog.jp/entry/2016/02/25/213502

This is also a side-effect of the application design. There is a relatively simple way to avoid this side effect without compromising security when you connect to SQL Server. Instead of connecting to a separate database for each user or group, connect to the same database on the server and then execute the Transact-SQL USE statement to change to the desired database. The following code fragment demonstrates creating an initial connection to the master database and then switching to the desired database specified in the databaseName string variable.

テナント毎に専用の Databse を作って提供するように設計されたアプリケーションだとどうしても接続の数は増えてしまう。そういう場合に行えるチップスって感じかな。

var connectionString = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=user;Integrated Security=True;Connect Timeout=1;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

var adminConnectionString = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=admin;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

using (var connection = new SqlConnection(connectionString))
{
    await connection.OpenAsync();
    connection.ClientConnectionId.Dump();
}

using (var connection = new SqlConnection(adminConnectionString))
{
    await connection.OpenAsync();
    connection.ClientConnectionId.Dump();
}

この場合それぞれ違う ID が出力される。じゃあ SQL で table を切り替えるようにしてみるとどうなるか。

var connectionString = "Data Source=(localdb)\\MSSQLLocalDB;Integrated Security=True;Connect Timeout=1;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

using (var connection = new SqlConnection(connectionString))
{
    await connection.OpenAsync();
    var command = new SqlCommand("USE etl", connection);
    command.ExecuteNonQuery();
    connection.ClientConnectionId.Dump();
}

using (var connection = new SqlConnection(connectionString))
{
    await connection.OpenAsync();
    var command = new SqlCommand("USE publishing", connection);
    command.ExecuteNonQuery();
    connection.ClientConnectionId.Dump();
}

こうやって table を切り替えると ID が同じになる。Database が多すぎて Pool が枯渇するって課題が見えてきたら使えるかもしれない。

色んな事を書く

シンプルさを極めたエンジニアになりたい

SQL Server の Connection Pool について