요구사항: SQL Server 데이터베이스가 교착 상태를 일으키는 이유는 다양하며, 운영 환경에서는 모두가 교착 상태를 겪을 수 있고, 애플리케이션 교착 상태의 비정상 정보만으로는 구체적인 원인을 찾기 어려울 수 있으므로, SQL Server의 확장 이벤트를 사용하여 교착 상태를 추적하고 교착 발생 시점을 구체적으로 기록하는 것이 필요합니다.
확장 행사 개요
확장 이벤트는 사용자가 모니터링하고 문제 해결에 필요한 데이터를 수집할 수 있게 해주는 경량 성능 모니터링 기능입니다. 이 기능은 성능 모니터링 및 문제 해결 관련 목적을 위해 데이터 엔진의 내부 운영에 대한 세부 정보를 확인할 수 있게 해줍니다.
확장 이벤트(XEvents) 아키텍처를 통해 사용자는 SQL Server, Azure SQL 데이터베이스, Azure SQL 관리 인스턴스의 성능을 모니터링, 식별 또는 문제 해결하는 데 필요한 만큼 많은 데이터를 또는 적게 사용할 수 있습니다. 확장 이벤트는 매우 구성 가능하고, 가볍고, 확장성이 뛰어납니다.
참조:
하이퍼링크 로그인이 보입니다.
하이퍼링크 로그인이 보입니다.
교착 상태의 원인
교착 상태는 데이터베이스 내에서 경쟁하는 동시성 잠금, 주로 다단계 거래에서 발생합니다. 각 사용자 세션은 하나 이상의 작업을 대신해 실행 중일 수 있으며, 각 작업은 다양한 자원을 가져오기 또는 대기할 수 있습니다. 다음 유형의 자원들은 차단을 일으키고 결국 교착 상태를 초래할 수 있습니다.
- 잠금장치: 객체, 페이지, 행, 메타데이터, 애플리케이션 등 자원을 가져오기 위해 잠금이 기다리면 교착 상태를 일으킬 수 있습니다. 예를 들어, 트랜잭션 T1은 r1 행에 공유 잠금(S 잠금)을 가지고 있으며, 행 r2에 대한 독점 잠금(X 잠금)을 받기 위해 기다립니다. 트랜잭션 T2는 r2 행에 공유 잠금(S 잠금)을 가지고 있으며, r1 행에 대한 독점 잠금(X 잠금)을 기다립니다. 이로 인해 T1과 T2가 서로가 잠긴 자원을 해제하기를 기다리는 락 루프가 발생합니다.
- 워커 스레드: 작업 대기열에 작업 스레드가 있으면 교착 상태가 발생할 수 있습니다. 대기열에 있는 작업의 자원이 모든 워커 스레드를 차단하면 교착 상태가 발생합니다. 예를 들어, 세션 S1이 트랜잭션을 시작하고 r1 라인에 대한 공유 잠금(S-락)을 획득한 후 절전 모드로 전환됩니다. 모든 사용 가능한 워커 스레드에서 실행 중인 활성 세션이 r1 라인에 대한 독점 잠금(X-lock)을 얻으려 시도하고 있습니다. 세션 S1이 워커 스레드를 받을 수 없기 때문에 트랜잭션을 커밋하고 r1 라인의 잠금을 해제할 수 없습니다. 이로 인해 교착 상태가 발생할 수 있습니다.
- 메모리: 동시 요청이 메모리를 얻기 위해 기다렸을 때 현재 사용 가능한 메모리가 필요를 충족하지 못할 때 교착 상태가 발생할 수 있습니다. 예를 들어, 두 개의 동시 쿼리(Q1과 Q2)는 사용자 정의 함수로 실행되며, 각각 10MB와 20MB의 메모리를 획득합니다. 각 쿼리가 30MB를 요구하고 사용 가능한 총 메모리가 20MB라면, Q1과 Q2는 서로의 메모리가 비워질 때까지 기다려야 하며, 이로 인해 교착 상태가 발생합니다.
- 병렬 쿼리 및 실행 관련 자원: 일반적으로 스위치 포트와 연관된 처리 조정자, 생성기 또는 소비자 스레드는 병렬 쿼리에 포함되지 않은 프로세스가 적어도 하나 이상 있을 때 서로 차단하여 교착 상태가 발생할 수 있습니다. 또한, 병렬 쿼리가 실행을 시작하면 SQL Server는 현재 워크로드에 따라 병렬성 정도나 워커 스레드 수를 결정합니다. 교착 상태는 예: 서버에서 새로운 쿼리가 실행되기 시작하거나 워커 스레드가 소진되는 등 시스템 워크로드에 예상치 못한 변화가 발생할 수 있습니다.
- 다중 활동 결과 집합(MARS) 자원: 이 자원들은 MARS에서 다중 활동 요청의 교차 실행을 제어하는 데 사용됩니다.
참조:
하이퍼링크 로그인이 보입니다.
교착 상태 확장 이벤트 기록
다음 명령어로 교착 상태를 기록하는 확장 이벤트를 생성하세요:
교착 상태 이벤트 세션 시작
이벤트 세션 중단
이벤트 세션을 삭제합니다
이벤트 세션 데이터 조회
테스트 교착 상태
테스트용 새로운 Tab1 테이블을 만들고, 두 개의 새로운 실행 창을 만들고, 각각 다음 명령어를 실행하세요:
교착 상태는 다음과 같이 발생합니다:
다른 프로세스와의 트랜잭션(프로세스 ID 68)이 잠긴 자원에서 교착 상태에 빠져 교착 상태로 선택되었습니다. 거래를 다시 실행해 주세요.
트랜잭션(프로세스 ID 68)은 다른 프로세스와 함께 잠금 자원에 교착 상태가 되어 교착 상태가 되었습니다. 거래를 다시 실행하세요. 
다음 그림에 표시된 이벤트 세션 데이터를 확인하세요:
상세한 XML 데이터는 다음과 같습니다:
(끝)
|
게시됨 2024. 3. 2. 오후 7:13:04
|
|
|
|
집주인|
게시됨 2024. 3. 2. 오후 7:14:35
|
