SQL Server Connection Pool 대하여

보통 Mysql은 Max Connection 관련 옵션 정보를 제어하여 메모리를 관리하고

데이터베이스에 연결하는 Server에서 Connection Pool을 관리하도록 하고 있다.

하지만 유독 SQL Server에서는 Connection Pool에 대한 내용을 들어본 기억이 없었다.

그래서 SQL Server가 어떻게 Connection을 관리하는지 알아보고자 하였다.

 

일단 기본적이 내용은 SQL Server에 연결하는 ADB.NET에서 Connection Pool을 자동 생성하고 관리하고 있다는 내용이었다.

1.     ADO.NET은 기본적으로 Connection Pooling을 On 하고 있다.

2.     기본적으로 최소 1개, 최대 32767개의 Connection을 가질 수 있다.

 

SQL Connection 생성 과정

쳐음 Client Program이 SqlConnection 객체를 생성하고 Open하게 되면

Connection String에 따라 연결 및 로그인을 하게 되는데,

이 때 SQL Server상에 Connection과 관련된 Resource를 할당하고 Context를 생성하게 된다.

이를 SQL Server 상에서는 SPID(Server Process ID)로 표현한다.

 

Connection Pool 생성 과정

1.     Process 별

2.     한 Process 내에 AppDomain별

3.     Connection String 별

A.     예를 들어, Data Source=(local)을 쓰는 것과 Data Source=MyLocalServer를 쓰는 것은 비록 의미상 같다 하더라도 다른 Pool을 갖는다.

B.      만약 Connection String이 Windows 인증(Integrated Security=SSPI)를 사용하는 경우, 해당 로그인 유저 별로 다른 Pool을 갖는다.

 

명시적 Connection Pooling 옵션

Connection String에 Pooling ON/OFF, MIN-MAX SIZE, Timeout 등의 옵션을 설정하여 전달할 수 있다.

그 외에 Connection String에 입력 가능한 정보는 MS DOCS 에서 확인 가능하다.

https://docs.microsoft.com/ko-kr/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=dotnet-plat-ext-5.0 

SqlConnection.ConnectionString 속성 (System.Data.SqlClient)

 

다음과 같은 예제를 실행해본 결과값을 보면

$ConnectionString = "Data Source=.\sqlexpress;

initial catalog=master;

User Id=<userid>;

Password=<pwd>;

Pooling=true;

application name=ConnPoolTest;

Min Pool Size=20;

Max Pool Size=100;

Connection Timeout=15"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

$SqlConnection.Open()

$Command = $SqlConnection.CreateCommand()

$Command.CommandText = "SELECT session_id, PROGRAM_NAME, status

FROM sys.dm_exec_sessions

WHERE PROGRAM_NAME LIKE 'ConnPoolTest%'"

$Reader = $Command.ExecuteReader()

while($Reader.Read() = "True"){write-host $Reader[0]',' $Reader[1]',' $Reader[2]}

$Reader.Close()

$SqlConnection.Close()

$SqlConnection.Dispose()

 

52 , ConnPoolTest , running

53 , ConnPoolTest , sleeping

57 , ConnPoolTest , sleeping

58 , ConnPoolTest , sleeping

59 , ConnPoolTest , sleeping

60 , ConnPoolTest , sleeping

61 , ConnPoolTest , sleeping

63 , ConnPoolTest , sleeping

64 , ConnPoolTest , sleeping

65 , ConnPoolTest , sleeping

66 , ConnPoolTest , sleeping

68 , ConnPoolTest , sleeping

69 , ConnPoolTest , sleeping

70 , ConnPoolTest , sleeping

71 , ConnPoolTest , sleeping

72 , ConnPoolTest , sleeping

73 , ConnPoolTest , sleeping

74 , ConnPoolTest , sleeping

75 , ConnPoolTest , sleeping

76 , ConnPoolTest , sleeping

 

위와 같이 최소 Connection Pool 값인 20개가 생성되는 것을 볼 수 있다.

 

최대 연결 Overflow

mysql 과 마찬가지로 기본적으로 셋팅되어 있는 최대값인 32767 개의 Connection 이 넘어가면

오류가 발생된다.

해당 최대 값을 SSMS에서 인스턴스 속성 > 연결 > 최대 동시 연결 수 로 설정되며

0은 제한이 없다고 가이드 되어 있으나 최대값은 32767 이다.

 

다음과 같이 쿼리 설정이 가능하다.

EXEC sp_configure 'show advanced options', 1;

GO  

RECONFIGURE ;  

GO  

EXEC sp_configure 'user connections', 325 ;  

GO  

RECONFIGURE;  

GO  

 

또한 Connection string에서 한번에 생성 가능한 최대값은 100이다.

100개 이상 생성하려고 하면 다음과 같은 Error가 발생된다.

$ConnectionString = "Data Source=.\sqlexpress;

initial catalog=master;

User Id=<userid>;

Password=<pwd>;

Pooling=true;

application name=ConnPoolTest;

Min Pool Size=101"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

New-Object : "1"개의 인수가 있는 ".ctor"을(를) 호출하는 동안 예외가 발생했습니다.

 "풀 크기의 최소값 또는 최대값이 잘못되었습니다. 최소 풀 크기는 최대 풀 크기보다

클 수 없습니다."

위치 줄:1 문자:18

+ ... onnection = New-Object

System.Data.SqlClient.SqlConnection($Connectio ...

+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (:) [New-Object],

MethodInvocationException

    + FullyQualifiedErrorId :

ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.

NewObjectCommand

 

sp_reset_connection 은 무언가?

그렇다면 Connection Pool을 어떻게 재사용이 되는 것일까?

먼저 Connection 5개를 같은 String으로 생성 후 제거해 보면 다음과 같다,

$ConnectionString = = "Data Source=.\sqlexpress;

initial catalog=master;

User Id=<userid>;

Password=<pwd>;

Pooling=true;

application name=ConnPoolTest;"

$SqlConnection1 = New-Object

System.Data.SqlClient.SqlConnection($ConnectionString)

$SqlConnection2 = New-Object

System.Data.SqlClient.SqlConnection($ConnectionString)

$SqlConnection3 = New-Object

System.Data.SqlClient.SqlConnection($ConnectionString)

$SqlConnection4 = New-Object

System.Data.SqlClient.SqlConnection($ConnectionString)

$SqlConnection5 = New-Object

System.Data.SqlClient.SqlConnection($ConnectionString)

 

$SqlConnection1.Open()

$SqlConnection2.Open()

$SqlConnection3.Open()

$SqlConnection4.Open()

$SqlConnection5.Open()

 

$SqlConnection1.close()

$SqlConnection2.close()

$SqlConnection3.close()

$SqlConnection4.close()

$SqlConnection5.close()

 

$SqlConnection1.Dispose()

$SqlConnection2.Dispose()

$SqlConnection3.Dispose()

$SqlConnection4.Dispose()

$SqlConnection5.Dispose()

 

Write-Host "Connection1 State: $($SqlConnection1.State)" -ForegroundColor Magenta

Write-Host "Connection2 State: $($SqlConnection2.State)" -ForegroundColor Magenta

Write-Host "Connection3 State: $($SqlConnection3.State)" -ForegroundColor Magenta

Write-Host "Connection4 State: $($SqlConnection4.State)" -ForegroundColor Magenta

Write-Host "Connection5 State: $($SqlConnection5.State)" -ForegroundColor Magenta

 

Connection1 State: Closed

Connection2 State: Closed

Connection3 State: Closed

Connection4 State: Closed

Connection5 State: Closed

 

다음과 같이 모두 제거 되었지만 DMV를 조회해보면 아직 session이 살아있는 것을 볼 수 있다.

(현재 DMV 조회를 위해 새로 연결한 것 까지 6개가 조회된다.)

$ConnectionString = "Data Source=.\sqlexpress;

initial catalog=master;

User Id=<userid>;

Password=<pwd>;

Pooling=true;

application name=ConnPoolTest"

$SqlConnection1 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)

$SqlConnection1.Open()

$Command1 = $SqlConnection1.CreateCommand()

$Command1.CommandText = "SELECT session_id, PROGRAM_NAME, status

FROM sys.dm_exec_sessions

WHERE PROGRAM_NAME LIKE 'ConnPoolTest%'"

$Reader1 = $Command1.ExecuteReader()

while($Reader1.Read() = "True")

{write-host $Reader1[0]',' $Reader1[1]',' $Reader1[2]}

$Reader1.Close()

$SqlConnection1.Close()

$SqlConnection1.Dispose()

 

56 , ConnPoolTest , sleeping

60 , ConnPoolTest , running

86 , ConnPoolTest , sleeping

87 , ConnPoolTest , sleeping

88 , ConnPoolTest , sleeping

89 , ConnPoolTest , sleeping

 

이 내용을 xEvent로 조회해보면 다음과 같다.

중간에 내용과 같이 logout이 진행되지 않고 연결이 다시 붙고 sp_reset_connection 이라는

프로시저가 호출되는 것을 볼 수 있다.

해당 시스템 프로시저는 docs에 기술되어 있지 않아 리서치를 해보니

Connection Pool의 Connection을 재사용하기 위한 사전 작업을 진행하는 시스템 프로시저였다.

그렇다면 해당 프로시저는 어떤 작업을 하는가?

1.     sp_reset_connection 가 재설정 하는 것

A.     Connection 에서 임시 저장해 두었던 에러 상태 및 숫자를 초기화 한다.
(@@error과 같음)

B.      실행 컨텍스트의 병령 쿼리를 실행하던 모든 자식 실행 컨텍스트를 중지한다.

C.      완료되지 않은 IO작업을 기다린다.

D.     이 전 Connection으로 생성된 고정 버퍼를 반환한다.

E.      Connection에 사용될 모든 Buffer Resouece Lock을 해제한다.

F.      이 전 Connection에서 사용된 작업 내역과 임시 작업을 제거한다.

G.     이 전 Connection에서 사용된 Global Cursor를 삭제한다.

H.     이 전 Connection에서 열어둔 SQL-XML 핸들을 닫고 제거한다.

I.       이 전 Connection에서 열어둔 시스템 및 사용자 테이블을 닫는다.

J.       이 전 Connection에서 실행했던 Transaction 중 진행 중인 Transaction을 종료한다.

K.      이 전 Connection에서 실행한 DTC에 결함 발생

L.      현재 데이터베이스의 사용자 참조와 공유 데이터베이스 연결을 해제한다

M.    이 전 Connection에서 획득한 모든 Lock을 해제한다.

N.     이 전 Connection에서 획득한 모든 handle을 해제한다.

O.     Session의 모든 SET 옵션을 기본값으로 재설정한다.

P.      Session의 @@rowcount, @@identity 를 초기화 한다.

Q.     Session의 모든 추적 옵션을 초기화 한다.

2.     sp_reset_connection 가 재설정 하지 못하는 것

A.     다시 연결된 Connection Pool의 Connection String이 이전과 완전히 똑 같은 경우에 보안 컨텍스트를 초기화하지 못한다.

B.      sp_setapprole을 사용하여 클라이언트의 Role을 생성한 경우 해당 Role을 반환하지 못한다.

 

참조

http://web.archive.org/web/20100730003952/http://sqldev.net/articles/sp_reset_connection/default.html

https://www.sqlshack.com/sql-connection-strings-tips/

https://www.csharpstudy.com/Data/SQL-connection-pooling.aspx

https://sungwookkang.com/871

https://docs.microsoft.com/ko-kr/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=dotnet-plat-ext-5.0

https://docs.microsoft.com/ko-kr/sql/database-engine/configure-windows/configure-the-user-connections-server-configuration-option?view=sql-server-ver15#Recommendations