[MSSQL] Tempdb 래치경합 확인 및 해결방법(PAGELATCH_EX, PAGELATCH_SH)

시스템 데이터베이스 중 tempdb는 아래와 같은 작업을 할 경우 사용되는 공용 공간이다.

따라서 이 공간에 문제가 생긴다면 해당 공간을 사용하는 쿼리들에 문제가 생기게 된다.

tempdb를 사용하는 경우

- 임시 테이블을 생성,삭제 시

- 테이블 변수를 사용 시

- 커서(Cursors)와 관련된 테이블

- 정렬작업(order by), 그룹작업(group by), 해시계획(hash plans)

---

Tempdb 경합확인

Tempdb 사용이 많을 경우 SQL Server에서 페이지를 할당하려고 할 때 경합이 발생 한다. 

- 이는 메모리 접근에 대한 스레드 병목현상으로 I/O 병목(PAGEIOLATCH_*)과는 관련이 없다.

- 높은 처리량/높은 동시성 트랜잭션 처리(OLTP) 워크로드의 경우 특히 가능성이 높습니다

 

병목 현상을 간단하게 확인하는 명령어는 다음과 같다.

--전체를 포괄적으로
SELECT * FROM SYS.SYSPROCESSES

--1 세션 ID를 기준으로 정렬된 sys.dm_os_waiting_tasks 쿼리
SELECT wt.session_id, wt.wait_type
, er.last_wait_type AS last_wait_type
, wt.wait_duration_ms
, wt.blocking_session_id, wt.blocking_exec_context_id, resource_description
FROM sys.dm_os_waiting_tasks wt
JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id
JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms desc
GO

--2 대기 시간을 기준으로 정렬된 sys.dm_os_waiting_tasks 쿼리
SELECT wt.session_id, wt.wait_type
, er.last_wait_type AS last_wait_type
, wt.wait_duration_ms
, wt.blocking_session_id, wt.blocking_exec_context_id, resource_description
FROM sys.dm_os_waiting_tasks wt
JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id
JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms desc;

 

 

 

 

 

 

 

아래는 처리하지 못하고 대기하고 있는 쿼리의 요청 정보를 확인하는 상세쿼리이다.

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

 

file ID를 확인하기 위한 쿼리

--tempdb 파일그룹 조회(file ID를 확인가능)
use tempdb
go
sp_helpfile

wait_type이 PAGELATCH_UP 또는 PAGELATCH_EX를 출력하는 라인 중 resource_description 이 

2:1:1(databaseID 2 – tempdb, file ID 1 - 1번째 tempdb.mdf 파일, page ID 1) 인경우 PFS(페이지 여유 공간) 페이지,

2:1:3인경우 SGAM(공유 전역 할당 맵) 페이지 병목을 확인 할 수 있다.

(아래 결과는 Tempdb 경합 내용이 없음)

통계	Description
Session_id	태스크와 연결된 세션의 ID입니다.
Wait_type	SQL Server가 엔진에서 기록한 대기 유형으로, 현재 요청이 실행되지 않도록 차단합니다.
Last_wait_type	이 요청이 이전에 차단된 경우 이 열은 마지막 대기의 유형을 반환합니다. Null을 허용하지 않습니다.
Wait_duration_ms	SQL Server 인스턴스가 시작된 이후 또는 누적 대기 통계가 다시 설정된 이후 이 대기 유형에서 대기하는 데 소요된 총 대기 시간(밀리초)입니다.
Blocking_session_id	요청을 차단하고 있는 세션의 ID입니다.
Blocking_exec_context_id	태스크와 연결된 실행 컨텍스트의 ID입니다.
Resource_description	resource_description 열에는 대기 중인 정확한 페이지가 <database_id>:<file_id>:<page_id> 형식으로 나열됩니다. page_id=1 : PFS, page_id=2 : GAM, page_id=3 : SGAM

---

Tempdb 경합 해결책

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보다 적을 경우 논리코어의 수 만큼 분리하는 것을 제시 하였다

---

참고사항

백서: 래치 경합 진단 및 해결 - SQL Server