SQL Server 커넥션 풀링

jevida(강성욱) 2016.10.08 07:02 Views : 4358

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으로 확인 가능하다.

 

확장이벤트를 사용하여 풀링을 확인 할 수 있다.

 

 

지금까지 연결 풀링에 대해서 알아 보았으며 작동하는 기본 구조를 파악하여 접근할 수 있도록 하여야 한다.

 

 

[참고자료]

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

No. Subject Author Date Views
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 1198
1866 누락된 인덱스 확인하기 jevida(강성욱) 2016.10.08 3105
1865 비클러스터 인덱스 페이지 내용 jevida(강성욱) 2016.10.08 2041
1864 ATTACH DATABASE 오류 1314 jevida(강성욱) 2016.10.08 1184
» SQL Server 커넥션 풀링 jevida(강성욱) 2016.10.08 4358
1862 가상 SQL Server에 Hot Add vCPU 사용하기 jevida(강성욱) 2016.10.08 1140
1861 DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기 jevida(강성욱) 2016.10.08 1380
1860 쉐어포인트의 SQL Server 접속 문제 jevida(강성욱) 2016.10.08 1491
1859 Lazy Log Truncation jevida(강성욱) 2016.10.08 1354
1858 인덱스 구성과 상황에 따른 인덱스 성능 jevida(강성욱) 2016.10.08 1509
1857 Max worker thread 초과 이슈 jevida(강성욱) 2016.10.08 2848
1856 SQL Server Failover 클러스터 설치 트러블슈팅 jevida(강성욱) 2016.10.08 2404
1855 MAXDOP 극대화 하기 jevida(강성욱) 2016.10.08 2185
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2055
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1876
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3515
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2413
1850 프로파일러를 이용한 중첩된 프로시저 디버깅 jevida(강성욱) 2016.10.07 1623
1849 SAN 스토리지 성능 모니터 - SAN 스토리지를 사용하는 경우 성능 카운터를 어떻게 모니터링 할까? jevida(강성욱) 2016.10.07 1795
1848 저장된 Plan Cache 확인 및 활용 jevida(강성욱) 2016.10.07 4725
1847 Xp_fixeddrives 세부 정보 확인하기 jevida(강성욱) 2016.10.07 1874





XE Login