SQL Server 커넥션 풀링
- Version : SQL Server 2005, 2008, 2008R2, 2012
SQL Server에서 사용자 연결을 할 때 오버헤드를 줄이기 위해서 커넥션 풀링을 통하여 재사용한다.
이번 포스트는 SQL Server Premier Field Engineer Blog에 게시된 내용으로 필자가 읽고 이해한 내용을 바탕으로 정리 하였으며 번역의 오류나 기술적 오류가 있음을 미리 알려둔다. 자세한 내용은 원문을 참고하길 바란다.
응용 프로그램은 SQL Server의 공급자를 사용하여 인스턴스에 연결 한다. 다음 스크립트는 PowerShell을 이용하여 SQLBOX1이라는 인스턴스에 커넥션을 하였다.
$ConnectionString = "data source=sqlbox1; initial catalog=master; trusted_connection=true; application name=ConnPoolTest" |
위의 연결 문자열은 비교적 간단하지만 우리가 쉽게 추가 연결 풀링을 증명하기 위해 아래 쿼리에 sys.dm_exec_session의 구문을 분석 할 수 있도록 응용 프로그램 이름 매개변수를 지정 하였다.
아래 스크립트는 5개의 System.Data.SqlClient.SqlConnection 개체를 만들 수 있다.
$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) |
.NET Data Provider 커넥션을 오픈하여 SQL Server개체의 인스턴스에 대한 성능 모니터(NumberOfPooledConnections)에서 풀링 연결 수의 점진적인 증가를 볼 수 있다.
$SqlConnection1.Open() Start-Sleep -Seconds 2 $SqlConnection2.Open() Start-Sleep -Seconds 2 $SqlConnection3.Open() Start-Sleep -Seconds 2 $SqlConnection4.Open() Start-Sleep -Seconds 2 $SqlConnection5.Open() |
Perfmon을 확인해 보면 풀링된 연결의 수가 0에서 5로 증가되는 것을 확인 할 수 있다.
또한 연결 문자열에 지정된 응용 프로그램의 이름을 필터링 하여 sys.dm_exec_sessions에서 확인 할 수 있다.
select session_id,program_name from sys.dm_exec_sessions where program_name = 'ConnPoolTest'; |
현재 5개의 연결이 있고 5개의 세션을 보여주고 있다. 다음 명령을 실행하여 연결을 닫아 보자.
$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 Green Write-Host "Connection2 State: $($SqlConnection2.State)" -ForegroundColor Green Write-Host "Connection3 State: $($SqlConnection3.State)" -ForegroundColor Green Write-Host "Connection4 State: $($SqlConnection4.State)" -ForegroundColor Green Write-Host "Connection5 State: $($SqlConnection5.State)" -ForegroundColor Green |
코드의 마지막 다섯 줄이 연결 상태를 표시하는 명령어이며 다음과 같은 출력을 나타낸다.
Connection1 State: Closed Connection2 State: Closed Connection3 State: Closed Connection4 State: Closed Connection5 State: Closed |
하지만 sys.dm_exec_sessions을 실행해 보면 다섯개의 세션이 살아 있는 것을 확인 할 수 있다.
select session_id, program_name from sys.dm_exec_sessions where program_name = 'ConnPoolTest'; |
이것이 연결 풀링이다. PerfMon에서도 5개의 연결이 살아 있다는 것을 나타내고 있으며 이는 나중에 재사용을 위해 풀에 있다는 것을 보여준다.
이제 공급자가 기존 연결 문자열을 사용하여 새 연결을 열 필요가 있을 때 인스턴스에 대한 연결을 설정하는 오버헤드를 발생 시키지 않고 풀에서 사용하지 않는 하나를 사용하여 연결을 수행 한다.
그렇다면 다른 연결 문자열을 사용하여 연결하여 보자.
$ConnectionString2 = "data source=sqlbox1; initial catalog=master; trusted_connection=true; application name=ConnPoolTest2" $SqlConnection6 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString2) $SqlConnection7 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString2) $SqlConnection6.Open() Start-Sleep -Seconds 2 $SqlConnection7.Open() $SqlConnection6.Close() $SqlConnection7.Close() $SqlConnection6.Dispose() $SqlConnection7.Dispose() |
성능 모니터에서 NumberOfActiveConnectionPools 카운터(녹색선)를 추가하여 확인한 결과 두 가지 새로운 연결로 풀이 생성된 것을 확인 할 수 있다.
다른 연결 문자열을 지정하면 원래의 연결을 재사용 할 수 없다. DMV에서도 확인할 수 있다.
select session_id, program_name from sys.dm_exec_sessions where program_name like 'ConnPoolTest%'; |
연결 풀링의 가장 큰 고려 사항 중 하나는 SqlConnection 개체의 Close를 확인하는 거이다. 어떤 클라이언트가 개체를 사용하고 닫거나 삭제하지 않는 경우 다음 재사용을 할 경우 풀링 된 연결을 소비하는 것이다.
하지만 이 풀링이 문제가 될 수 있다. 기본 대신 최대 풀 크기를 5로 설정하여 최대 풀의 크기에 도달 하였을 때 발생하는 문제를 확인 할 수 있다.
$ConnectionString3 = "data source=sqlbox1; initial catalog=master; trusted_connection=true; application name=ConnPoolTest; max pool size=5" $SqlConnection1 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3) $SqlConnection2 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3) $SqlConnection3 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3) $SqlConnection4 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3) $SqlConnection5 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3) $SqlConnection6 = New-Object System.Data.SqlClient.SqlConnection($ConnectionString3) $SqlConnection1.Open() $SqlConnection2.Open() $SqlConnection3.Open() $SqlConnection4.Open() $SqlConnection5.Open() # five connections opened with no issues $SqlConnection6.Open() # the above attempt for SqlConnection.Open() fails |
$SqlConnection6.open()을 했을 때 다음과 같은 오류가 발생 한다.
Exception calling "Open" with "0" argument(s): "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." |
웹 응용 프로그램은 기본 최대 풀 크기를 사용한다. 그들이 제대로 닫히거나 삭제되지 않았을 수 있기 때문에 101번째 시도는 오류 동작이 발생 한다. 이를 처리하는 일반적인 방법은 마지막 블록에 try/catch 를 호출하거나 C#에서 Disposable.Dispose() 함수를 자동으로 호출 할 수 있도록 한다.
SQL Server 내에서 일어나는 연결 풀링을 확인 하는 방법에는 프로파일러를 통하여 볼 수 있다.
Sp_reset_connection으로 확인 가능하다.
확장이벤트를 사용하여 풀링을 확인 할 수 있다.
지금까지 연결 풀링에 대해서 알아 보았으며 작동하는 기본 구조를 파악하여 접근할 수 있도록 하여야 한다.
[참고자료]
- SQL Server Connection Pooling :
http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
