데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

Tempdb 경합 확인 및 해결 (Tempdb Contention)

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server를 운영하는데 있어서 성능 문제를 일으키는 부분 중 하나가 임시데이터베이스라고 불리는 Tempdb 이다. Tempdb를 많이 사용하면 SQL Server에서 페이지를 할당하려고 할 때 경합이 발생 한다. 이는 메모리 접근에 대한 스레드 병목현상으로 I/O 병목과는 관련이 없다.

 

Tempdb는 다음과 같은 작업이 있을 경우 사용된다.

  • 임시 테이블(로컬 또는 전역)을 생성, 삭제
  • 테이블 변수
  • CURSORS와 관련된 작업 테이블
  • ORDER BY
  • GROUP BY
  • HASH PLANS

 

실제 운영환경에서 Tempdb로 인한 서비스 영향도가 매우 큰데도 불구하고 많은 DBA들이 Tempdb에 대한 문제를 인식하지 않고 있다. (또는 경합이 일어나는지를 모르고 있을 수도 있다.)

 

다음 스크립트는 처리하지 못하고 대기하고 있는 쿼리의 요청 정보를 나타낸다.

SELECT

[owt].[session_id],

[owt].[exec_context_id],

[owt].[wait_duration_ms],

[owt].[wait_type],

[owt].[blocking_session_id],

[owt].[resource_description],

CASE [owt].[wait_type]

WHEN N'CXPACKET' THEN

RIGHT ([owt].[resource_description],

CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)

ELSE NULL

END AS [Node ID],

[es].[program_name],

[est].text,

[er].[database_id],

[eqp].[query_plan],

[er].[cpu_time]

FROM sys.dm_os_waiting_tasks [owt]

INNER JOIN sys.dm_exec_sessions [es] ON

[owt].[session_id] = [es].[session_id]

INNER JOIN sys.dm_exec_requests [er] ON

[es].[session_id] = [er].[session_id]

OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]

OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]

WHERE

[es].[is_user_process] = 1

ORDER BY

[owt].[session_id],

[owt].[exec_context_id];

GO

 

(해당 예시 화면에서는 Tempdb 경합 내용이 없습니다.)

 

위의 스크립트를 사용한 정보에서 wait_type이 PAGELATCH_UP 또는 PAGELATCH_EX를 출력하는 라인 중 resource_description 이 2:1:1(databaseID 2 – tempdb, file ID 1, page ID 1) 인경우 PFS 페이지, 2:1:3인경우 SGAM이라 불리는 할당 페이지 병목을 확인 할 수 있다.

 

병목 여부 정보는 간단히 sys.sysprocess에서도 확인 할 수 있다.

SELECT * FROM SYS.SYSPROCESSES

 

 

Tempdb 경합이 발견 될 때는 DBA는 이를 해결하기 위해 다음과 같은 해결책을 제안 할 수 있다.

  1. 과도한 임시 테이블 사용을 금지하여 Tempdb 사용량을 줄인다. 하지만 쿼리(비즈니스 로직)을 변경한다는 것은 쉬운 일이 아니다. (여러 개발자를 설득해야 한다.)
  2. 추적 플래그 -T1118 설정으로 SGAM에서 경합을 방지
  3. Tempdb 데이터 파일 분할하여 여러 파일에 할당 작업을 분산시켜 PFS 경합 방지한다. 하지만 무리한 데이터 파일을 분리하는 경우 성능이 저하 될 수 있다. (적정량을 찾는 것이 중요함)

 

Tempdb 데이터 파일의 개수가 중요하다고 하였는데 마이크로소프트의 SQL 기술지원 에스컬레이션 엔지니어 Bob Ward는 다음과 같이 제시 하였다.

시스템 환경이 2 CPU * 4 core * 2 (hyper threading) = 16 logical core 환경에서 논리코어가 8개 이상일 때는 8개의 데이터 파일을 분리하고 논리코어가 8보다 적을 경우 논리코어의 수 만큼 분리하는 것을 제시 하였다.

 

아래 예시 그림은 100개의 Tempdb 커넥션에 대해 tempdb의 파일을 분리한 후 처리량이 증가한 것을 확인 할 수 있다.

 

 

지금까지 Tempdb의 영향도를 잘 몰랐다면 지금이라도 위의 스크립트를 통하여 경합이 발생하는지 알아 보자. DBA는 자신이 관리하는 시스템 모니터링을 꾸준히 하여 서비스 최적화를 할 수 있어야 한다.

 

[참고자료]

http://blog.naver.com/jevida/140149742633

 

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1830 SQL Server 2012 Sp1 설치 이슈 및 해결 jevida(강성욱) 2016.09.30 1578
1829 SSD에서 DBCC CHECKDB 성능 벤치마킹 jevida(강성욱) 2016.09.30 1931
1828 Collation에 따른 ALTER DATABASE 실패 jevida(강성욱) 2016.09.30 1942
1827 LDF 파일이 잘리지 않는 이유 jevida(강성욱) 2016.09.30 2054
» Tempdb 경합 확인 및 해결 (Tempdb Contention) jevida(강성욱) 2016.09.30 2220
1825 SQL Server IO 병목 확인과 오해 jevida(강성욱) 2016.09.30 2501
1824 SQL Version에 따른 sp_prepare 정보 반환 jevida(강성욱) 2016.09.30 1715
1823 DReplay 활성 세션 초과 에러 jevida(강성욱) 2016.09.30 1542
1822 저장 프로시저 내 임시 테이블 사용과 프로시저 재컴파일 jevida(강성욱) 2016.09.30 1869
1821 NUMA 노드와 추척플래그 8048 jevida(강성욱) 2016.09.30 820
1820 온라인 인덱스 리빌드와 조각화 증가 jevida(강성욱) 2016.09.30 1606
1819 VARCHAR(MAX) and NTEXT 쿼리 성능 jevida(강성욱) 2016.09.30 4474
1818 CPU 리소스 상태에 따른 병렬 처리 제한 jevida(강성욱) 2016.09.30 1077
1817 매개변수 값의 변경과 SQL 서버 성능 저하 jevida(강성욱) 2016.09.30 1271
1816 IN 절 사용시 예기치 못한 액세스 위반과 SQL Server 종료 jevida(강성욱) 2016.09.30 1060
1815 SQL 버전과 CLR (.NET Framework 버전에 따른 오류) jevida(강성욱) 2016.09.30 1189
1814 SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기 jevida(강성욱) 2016.09.30 1327
1813 테이블 반환 매개변수 사용과 SQL 2012의 향상된 캐싱 기능 jevida(강성욱) 2016.09.30 1231
1812 디스크 섹터 크기와 데이터베이스 성능 jevida(강성욱) 2016.09.29 1635
1811 CLR 사용시 CPU 사용률 증가 현상 jevida(강성욱) 2016.09.29 1761





XE Login