시스템 관리_35 SQL Server TempDB 할당 경합으로 인한 CPU 고부하 현상 원인 분석 및 완벽 해결 가이드

# 1. OS 관점: sqlservr 프로세스의 CPU 점유율 및 스레드별 부하 확인
$ top -b -n 1 -p $(pgrep sqlservr | head -n 1)
# PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
# 1420 mssql     20   0 18.520g 12.150g  32450 R 785.4  38.2  42:15.80 sqlservr
# -> 다중 코어 환경에서 CPU 점유율이 수백 %에 도달한 상태 확인

# 2. 현재 SQL Server 전체에서 가장 높은 대기 시간을 기록 중인 Wait Type 확인
$ sqlcmd -S localhost -U sa -P 'MyStrongPassword123!' -Q "
SELECT TOP 5 
    wait_type, 
    waiting_tasks_count, 
    wait_time_ms, 
    ROUND(wait_time_ms * 1.0 / NULLIF(waiting_tasks_count, 0), 2) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH_%'
ORDER BY wait_time_ms DESC;
"
# [출력 예시]
# wait_type        waiting_tasks_count  wait_time_ms         avg_wait_ms
# ---------------- -------------------- -------------------- ------------
# PAGELATCH_UP                  1542100             45214050        29.32
# PAGELATCH_EX                   421050             12540120        29.78
# -> PAGELATCH_UP/EX 대기 시간이 압도적이면 TempDB 할당 페이지 경합 가능성 매우 높음

# 3. 실시간으로 TempDB(database_id = 2) 할당 페이지에서 대기 중인 세션 추적
$ sqlcmd -S localhost -U sa -P 'MyStrongPassword123!' -Q "
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:%';
"
# [출력 예시]
# session_id wait_type     wait_duration_ms resource_description
# ---------- ------------- ---------------- --------------------
# 78         PAGELATCH_UP  45               2:1:1
# 84         PAGELATCH_UP  52               2:1:3
# -> resource_description이 '2:1:1'(PFS), '2:1:2'(GAM), '2:1:3'(SGAM)을 가리키면 전형적인 메타데이터 할당 경합임

# 4. 현재 TempDB 데이터파일 개수 및 크기/자동확장 균등 여부 확인
$ sqlcmd -S localhost -U sa -P 'MyStrongPassword123!' -Q "
SELECT 
    file_id, 
    name, 
    physical_name, 
    size * 8 / 1024 AS size_mb, 
    growth * 8 / 1024 AS growth_mb, 
    is_percent_growth
FROM sys.master_files
WHERE database_id = 2 AND type = 0;
"

#!/bin/bash
# diagnose_tempdb_contention.sh - Diagnose SQL Server TempDB Allocation Contention & High CPU

SQL_HOST=${1:-"localhost"}
SQL_USER=${2:-"sa"}
SQL_PASS=${3:-"MyStrongPassword123!"}

echo "=========================================="
echo " [SQL Server TempDB 경합 및 CPU 고부하 진단]"
echo " 대상 호스트: ${SQL_HOST}"
echo "=========================================="

if ! command -v sqlcmd &>/dev/null; then
    echo "  [오류] sqlcmd 명령어를 찾을 수 없습니다. mssql-tools 설치 여부를 확인하세요."
    exit 1
fi

# 1. CPU 논리 코어 수와 TempDB 데이터파일 수 대조
echo -e "\n[1] CPU 코어 수 대비 TempDB 데이터파일 구성 점검:"
CORE_COUNT=$(nproc 2>/dev/null || echo 4)

TEMPDB_FILE_COUNT=$(sqlcmd -S "$SQL_HOST" -U "$SQL_USER" -P "$SQL_PASS" -h -1 -W -Q "
SET NOCOUNT ON;
SELECT COUNT(*) FROM sys.master_files WHERE database_id = 2 AND type = 0;
" 2>/dev/null)

TEMPDB_FILE_COUNT=$(echo "$TEMPDB_FILE_COUNT" | tr -d '[:space:]')

echo "  - 시스템 논리 CPU 코어 수: ${CORE_COUNT}개"
echo "  - TempDB 데이터파일 개수 : ${TEMPDB_FILE_COUNT}개"

if [ "${TEMPDB_FILE_COUNT:-0}" -eq 1 ]; then
    echo "  [치명적 경고] TempDB 데이터파일이 1개뿐입니다!"
    echo "  -> 대량 동시 요청 시 PFS/SGAM 단일 페이지 병목으로 심각한 CPU 스핀 및 래치 경합 유발."
elif [ "${TEMPDB_FILE_COUNT:-0}" -lt "$CORE_COUNT" ] && [ "${CORE_COUNT}" -le 8 ]; then
    echo "  [주의] 데이터파일 개수가 논리 코어 수(${CORE_COUNT}개)보다 적습니다. 코어 수에 맞추어 증설을 권장합니다."
else
    echo "  [정상] 데이터파일 개수가 적정 수준으로 분산 구성되어 있습니다."
fi

# 2. 파일별 크기 및 자동확장(Growth) 동일성 점검
echo -e "\n[2] TempDB 파일 크기 및 증가폭 균등 여부 점검:"
FILE_CHECK=$(sqlcmd -S "$SQL_HOST" -U "$SQL_USER" -P "$SQL_PASS" -W -Q "
SET NOCOUNT ON;
SELECT 
    name, 
    size * 8 / 1024 AS size_mb, 
    growth * 8 / 1024 AS growth_mb, 
    CASE WHEN is_percent_growth = 1 THEN 'PERCENT' ELSE 'MB' END AS growth_type
FROM sys.master_files 
WHERE database_id = 2 AND type = 0;
" 2>/dev/null)
echo "$FILE_CHECK" | sed 's/^/  /'

# 3. 실시간 TempDB 할당 페이지 경합(PFS/GAM/SGAM) 발생 여부
echo -e "\n[3] 실시간 TempDB 할당 페이지(PFS/GAM/SGAM) 대기 세션 추적:"
WAITING_SESSIONS=$(sqlcmd -S "$SQL_HOST" -U "$SQL_USER" -P "$SQL_PASS" -W -Q "
SET NOCOUNT ON;
SELECT 
    wt.session_id, 
    wt.wait_type, 
    wt.wait_duration_ms, 
    wt.resource_description,
    SUBSTRING(st.text, (r.statement_start_offset/2)+1, 
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1) AS current_query
FROM sys.dm_os_waiting_tasks wt
JOIN sys.dm_exec_requests r ON wt.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE wt.wait_type LIKE 'PAGELATCH_%' 
  AND wt.resource_description LIKE '2:%';
" 2>/dev/null)

if echo "$WAITING_SESSIONS" | grep -q "PAGELATCH"; then
    echo "  [위험] 현재 TempDB 페이지 래치 경합에 걸린 세션이 포착되었습니다!"
    echo "$WAITING_SESSIONS" | head -n 15 | sed 's/^/    /'
else
    echo "  [정상] 현재 시점에 TempDB 시스템 페이지 경합은 감지되지 않았습니다."
fi

echo -e "\n=========================================="
echo " [진단 완료]"
echo "=========================================="