SQL Server 에서 Hang 현상이 발생하였다고 판단되었을 때, 확인하는 일반적인 절차에 대해서 아래와 같이 정리하였습니다.
빠르고 간단하게 최소한의 점검을 하기 위한 절차로 보면 될 것 같습니다.
SQL Server Hang 발생 시 일반적인 트러블슈팅 절차
2. Windows Server 상태 확인
3. SQL Server 서비스 실행 상태 확인
4. SQLCMD.EXE 명령을 사용하여 서버 로컬에서 SQL Server 연결 확인
5. sys.dm_exec_requests 쿼리 결과 확인
6. 성능 카운터를 통해 상태 확인
7. SQL Server ERRORLOG, stack dumps 확인
8. DAC(Dedicated Admin Connection) 연결 확인
각 단계별 세부 점검 사항에 대해서 아래와 같이 요약해 봅니다.
1. 명확한 문제 현상 파악
문제가 발생한 상황에 대해서 객관적인 현상을 파악합니다. 어플리케이션에서 발생한 오류 메시지 또는 데이터베이스 사용자가 겪은 문제 현상에 대해 명확히 정의해 봅니다.
2. Windows Server 상태 확인
Windows Server 에 로그인, 파일 복사, 프로그램 실행이 정상적인지 확인해 봅니다. 만약 이러한 테스트가 정상적이지 않다면 SQL Server Service 확인에 앞서 Windows Server 의 이벤트 로그, 작업 관리자를 통해 리소스(CPU, MEMORY, DISK, NETWORK) 상태를 점검합니다.
3. SQL Server 서비스 실행 상태 확인
작업 관리자를 통해 SQLSERVR.EXE 프로세스가 실행 중인지 확인합니다. 멀티 인스턴스를 사용하는 환경이라면 관리도구 - 서비스에서 해당 인스턴스가 실행 중인 상태인지 확인합니다.
4.SQLCMD.EXE 명령을 사용하여 서버 로컬에서 SQL Server 연결 확인
Command Prompt 에서 sqlcmd.exe(이전 버전의 osql.exe) 명령으로 로컬 서버에 위치한 SQL Server 에 연결이 되는데 원격 컴퓨터에서는 연결이 되지 않는다면 Network 관련 이슈를 점검해야 합니다.
5. sys.dm_exec_requests 쿼리 결과 확인
SQLCMD.EXE 명령으로 서버에 연결한 뒤 아래 쿼리를 수행하여 블로킹 문제가 발생하고 있지 않은지 확인합니다.
select * from sys.dm_exec_requests where blocking_session_id > 0 and wait_time > 0
블로킹이 발생하였다면 해당 쿼리를 확인하여 commit 또는 rollback 문제를 임시 조치할 수 있습니다.
6. 성능 카운터를 통해 상태 확인
기본적인 몇 가지 아래 카운터를 확인하여 리소스 상태를 점검해 봅니다.
Processor/% Processor Time
CPU 100% 사용 중이라면 hang 현상이 발생할 수 있습니다.
Process/% Processor Time (SQLSERVR.EXE 해당 인스턴스)
CPU 과점유가 SQLSERVR.EXE 프로세스에 의해서 발생하고 있는지 확인합니다.
Memory/Available MBytes
시스템에서 사용할 수 있는 가용 메모리를 확인합니다.
Logical Disk/% Idel Time
모든 디스크를 각각 확인하여 Disk IDLE 상태를 확인합니다. 0 에 가깝다면 DISK I/O 가 높은 원인을 찾아야 합니다.
SQL Server:SQL Statistics/Batch Requests/Sec
SQL Server engine 으로 요청되는 쿼리 요청이 얼마나 많은지 확인합니다. 만약 0 이라면 SQL Server 는 어떠한 요청도 처리할 수 없는 상태를 의미합니다.
7. SQL Server ERRORLOG, stack dumps 확인
SQLCMD 명령으로 로컬 서버에 연결할 수 없거나 sys.dm_exec_requests 쿼리를 수행할 수 없을 때, ERRORLOG 를 확인하여 어떠한 오류가 발생하는지 확인합니다. 또한 Stack Dump 가 발생하였다면 어떤 유형의 덤프인지 ERRORLOG 를 통해 확인할 수 있습니다.
8. DAC(Dedicated Admin Connection) 연결 확인
SQLCMD 명령으로 로컬 서버 연결이 실패한다면 DAC 를 사용하여 서버 연결을 시도해 볼 수 있습니다. DAC 로 접근이 된다면 sys.dm_exec_requests DMV 등을 활용하여 blocking, waiting 과 관련된 정보를 확인해 봅니다. 만약 blocking 문제에 의한 hang 현상이라면 해당 쿼리를 KILL 하여 문제를 임시 조치할 수 있습니다. 세션을 KILL 하기 앞서 아래 DMVs 정보를 수집하는 것도 추후 원인 분석에 도움이 될 수 있습니다.
sys.dm_exec_sessions
sys.dm_os_wait_stats
sys.dm_os_ring_buffers
sys.dm_os_schedulers
sys.dm_os_waiting_tasks
sys.dm_os_workers
sys.dm_tran_locks
sys.dm_io_pending_io_requests
기본적으로 위 단계에서 문제의 원인을 파악할 수 없거나 그 이외 범위의 문제(메모리 덤프가 필요하다던지....)라면 이제 본격적인 트러블슈팅 절차 단계로 넘어가야 할 것 같습니다. ^^;
[참고자료]
SQL Server 2005 Practical Troubleshooting by Ken Henderson
작성자 : Lai Go / 작성일자 : 2011.05.13
