tempdb 경합(SQL Server)

증상

PAGELATCH_UP/EX 대기, tempdb 할당 페이지(PFS/GAM/SGAM) 경합, 전반적인 느려짐.

1. 경합을 확인한다

tempdb 시스템 페이지(예: 2:1:1)에 대한 PAGELATCH 대기는 할당 경합을 나타낸다.

SQL Server

SELECT session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%' AND resource_description LIKE '2:%'
ORDER BY wait_duration_ms DESC;

2. tempdb 사용량을 분석한다

누가 tempdb를 얼마나 쓰는가? 내부 객체(sort/hash spill), version store, 사용자 객체 중 무엇인가?

SQL Server

SELECT * FROM sys.dm_db_file_space_usage;
SELECT TOP 10 session_id, user_objects_alloc_page_count, internal_objects_alloc_page_count
FROM sys.dm_db_task_space_usage ORDER BY internal_objects_alloc_page_count DESC;

3. 파일 구성을 수정한다

tempdb에 CPU 코어 수(최대 8개)만큼 크기가 같은 여러 데이터 파일을 사용한다. SQL 2016+는 설치 시 이를 권장하며, 이전 버전에서는 trace flag 1117/1118을 사용한다. 파일은 빠른 디스크(로컬 NVMe)에 둔다.

4. 폭주 쿼리를 찾는다

큰 sort/hash spill이 있는 쿼리가 tempdb를 폭증시킨다. memory grant가 부족하거나 계획이 나쁜 쿼리를 대상으로 인덱스/계획으로 spill을 줄인다.

SQL Server

SELECT TOP 10 t.text, mg.requested_memory_kb, mg.granted_memory_kb
FROM sys.dm_exec_query_memory_grants mg CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) t
ORDER BY mg.requested_memory_kb DESC;

5. 다른 데이터베이스의 대응 사례

같은 '임시 공간 경합'은 Oracle에서는 TEMP 테이블스페이스(v$tempseg_usage / v$sort_usage), PostgreSQL에서는 temp_files와 work_mem 초과다. 근본 해결은 항상 같다: spill하는 쿼리를 수정하고 임시 공간을 빠르게 한다.