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

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://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/
• http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/
• http://www.sqlskills.com/blogs/paul/tempdb-configuration-survey-results-and-advice/
• tempdb 데이터베이스에 대한 동시성 강화 : http://support.microsoft.com/kb/328551
• tempdb 데이터 파일 추가 및 물리적 위치 이동 :

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

 

 

2013-07-31 / 강성욱 / http://sqlmvp.kr