SQL Server를 실행하는 Amazon RDS DB 인스턴스에서 교착 상태에 대한 정보를 캡처하려면 어떻게 해야 하나요?

해결 방법

각각 리소스가 잠겨 있는 2개 이상의 세션이 다른 세션의 잠긴 리소스에 액세스하려고 하면 교착 상태가 발생합니다. 그 결과 ‘순환 체인’이 발생합니다. 이 경우 다음의 상황이 될 때까지 아무 세션도 계속 실행되지 않습니다.

• 세션 중 하나가 잠금을 해제합니다.
• 그러면 다른 세션이 잠긴 리소스에 액세스할 수 있습니다.

SQL Server의 교착 상태 감지기는 리소스 기반 또는 비용 기반 메커니즘을 사용하여 세션 중 하나를 종료함으로써 이러한 상황을 해결합니다. 그런 다음 감지기는 이 세션과 관련된 모든 수정 사항을 롤백합니다. 세션 중 하나가 종료되면 세션에 의해 유지되는 잠금이 해제되고 다른 세션은 계속할 수 있습니다. 자세한 내용은 교착 상태에 대한 Microsoft 설명서를 참조하세요.

다음 방법 중 하나를 사용하여 DB 인스턴스의 교착 상태 이벤트에 대한 정보를 캡처할 수 있습니다.

추적 플래그 활성화

교착 상태 추적 플래그(1204,1222)를 활성화할 수 있습니다. 추적 플래그는 SQL Server 오류 로그에서 교착 상태 정보를 캡처하여 추가 모니터링을 하는 등 SQL Server 동작을 사용자 지정하는 데 사용합니다.

• 추적 플래그 1204는 교착 상태와 관련된 각 노드에 대한 교착 상태 정보를 제공합니다.
• 추적 플래그 1222는 XML 형식의 추적 플래그 1204보다 자세한 교착 상태 정보를 제공합니다.

두 추적 플래그를 활성화하여 각 교착 상태에 대해 두 개의 서로 다른 상태 정보를 가져올 수 있습니다. 이 설정에 대한 자세한 내용은 Amazon RDS SQL Server DB 인스턴스에서 교착 상태 이벤트가 발생할 때 알림을 받으려면 어떻게 해야 하나요?를 참조하세요. 추적 플래그를 활성화한 후 SQL Server 오류 로그를 검토하여 교착 상태 이벤트에 대한 자세한 내용을 확인할 수 있습니다.

system_health 세션 사용

확장 이벤트는 SQL Server의 문제를 모니터링하고 문제를 해결하기 위해 데이터를 수집하는 데 도움이 되는 간편한 성능 모니터링 시스템입니다. system_health 확장 이벤트 세션은 SQL Server에 포함되며 기본적으로 활성화됩니다. SQL Server 데이터베이스 엔진이 시작되고 기본 서버 상태 정보를 수집할 때 세션이 자동으로 시작됩니다. 이 정보를 사용하여 성능 문제를 해결하고 데이터베이스 엔진 내에서 교착 상태를 모니터링할 수 있습니다.

system_health 확장 이벤트 세션은 이벤트 파일과 링 버퍼라는 2개의 대상을 사용하여 데이터를 저장합니다.

기본적으로 각 개별 파일의 크기는 5MB, 최대 롤오버 파일 수는 4개입니다. 이렇게 하면 최대 20MB의 system_health 확장 이벤트 데이터가 제공됩니다. SQL Server 2016, 2017 및 2019의 경우 개별 파일 크기는 100MB이고 최대 파일 수는 10개로 늘어납니다. 이렇게 하면 최대 1GB의 데이터가 제공됩니다.

링 버퍼는 선입선출(FIFO) 기준으로 데이터를 저장하는 메모리의 특수한 데이터 구조입니다. 링 버퍼의 대상 메모리는 Amazon RDS for SQL Server에서 4MB를 초과할 수 없습니다. 따라서 사용 중인 인스턴스에서 system_health 세션이 이벤트를 회전할 수 있습니다.

system_health 세션을 사용하여 Microsoft SQL Server Management Studio(SSMS) 또는 Transact-SQL(T-SQL)을 통해 DB 인스턴스의 교착 상태에 대한 정보를 검색할 수 있습니다.

SSMS를 사용하여 교착 상태 정보를 검색하려면 다음을 수행하세요.

1. SSMS를 엽니다.
2. Object Explorer(객체 탐색기)에서 Management(관리)를 선택한 다음 Extended Events(확장 이벤트)를 선택합니다.
3. Sessions(세션)를 선택합니다.
4. system_health 세션을 찾은 다음 package0.event_file을 선택하여(두 번 클릭) 확장 이벤트 파일을 엽니다.
5. 파일 내용을 로드한 후 SSMS 메뉴에서 [확장 이벤트(Extended Events)]를 선택합니다.
6. [필터(Filters)]를 선택합니다.
7. [필터(Filters)] 창에서 다음을 수행합니다.
   [필드(Field)]로 [이름(name)]을 선택합니다.
   [연산자(Operator)]로 [포함(Contains)]을 선택합니다.
   [값(Value)]으로 [교착 상태(deadlock)]를 선택합니다.
8. [확인(OK)]을 선택합니다. 교착 상태가 있는 이벤트를 볼 수 있습니다.
9. 보려는 이벤트를 선택한 다음 [교착 상태(Deadlock)] 탭을 선택하여 그래프를 봅니다.

-또는-

T-SQL을 사용하여 교착 상태 정보를 검색하려면 다음을 수행합니다.

1.    교착 상태 목록을 보려면 다음과 유사한 쿼리를 실행합니다.

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM (
    SELECT XEvent.query('.') AS XEvent
    FROM (
        SELECT CAST(target_data AS XML) AS TargetData
        FROM sys.dm_xe_session_targets st
        INNER JOIN sys.dm_xe_sessions s 
        ON s.address = st.event_session_address
        WHERE s.NAME = 'system_health'
        AND st.target_name = 'ring_buffer'
        ) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
) AS source;

2.    교착 상태 XML 출력을 선택하여 XML 파일을 새 창에서 엽니다.

3.    .xdl 파일 확장자를 사용하여 XML을 저장합니다. 이렇게 하면 XML이 그래픽 형식으로 변환됩니다.

4.    파일 위치로 이동한 다음 SSMS에서 .xdl 파일을 열어 교착 상태 그래프를 봅니다.

T-SQL 쿼리를 실행하여 ring_buffer에서 교착 상태 정보를 검색할 수 있습니다. ring_buffer 대상은 이벤트 데이터를 메모리에 보관합니다. 이 정보는 인스턴스가 재부팅되지 않는 경우에만 사용할 수 있습니다. 재부팅하면 이 정보가 제거됩니다.

xml_deadlock_report 확장 이벤트 세션 사용

xml_deadlock_report 이벤트를 선택해 교착 상태를 캡처함으로써 확장 이벤트 세션을 생성할 수 있습니다. 이벤트 파일을 대상으로 선택하면 이벤트가 파일에 저장됩니다. 이 파일은 나중에 분석할 수 있습니다. 확장 이벤트 세션을 만들려면 SSMS 또는 T-SQL을 사용할 수 있습니다. 세션이 생성되면 DB 인스턴스의 교착 상태에 대한 정보를 검색할 수 있습니다. SSMS 또는 T-SQL을 사용하여 이 작업을 수행할 수 있습니다.

SSMS를 사용하여 확장 이벤트 세션을 생성하려면 다음을 수행합니다.

1. SSMS를 엽니다.
2. [객체 탐색기(Object Explorer)]에서 [관리(Management)]를 선택한 다음 [확장 이벤트(Extended Events)]를 선택합니다.
3. [세션(Sessions)]을 선택(마우스 오른쪽 버튼 클릭)한 다음 [새 세션 마법사(New Session Wizard)]를 선택합니다.
4. [세션 이름(Session name)]에 세션 이름을 입력한 후 [다음(Next)]을 선택합니다.
5. [템플릿 선택(Choose Template)] 페이지에서 [템플릿 미사용(Do not use a template)]을 선택합니다.
6. [다음(Next)]을 선택하여 [새 세션 마법사(New Session Wizard)] 페이지를 엽니다.
7. [이벤트 라이브러리(Event library)]에서 [ xml_deadlock_report]를 선택한 후 [다음(Next)]을 선택합니다.
8. [전역 필드 캡처(Capture Global Fields)] 페이지에서 모든 이벤트에 공통적인 값을 선택합니다.
   참고:[sql_text field]를 선택하여 교착 상태의 원인이 된 쿼리를 확인합니다.
9. [다음(Next)]을 선택합니다.
10. [세션 이벤트 필터 설정(Set Session Event Filters)] 페이지에서 캡처할 데이터를 제한하는 이벤트 필터를 생성합니다.
11. [다음(Next)]을 선택합니다.
12. [세션 데이터 스토리지 지정(Specify Session Data Storage)] 페이지에서 [파일에 데이터를 저장하여 나중에 분석(Save data to a file for later analysis)] 및 [ 최신 데이터만 사용하여 작업(Work with only the most recent data)]을 선택합니다.
13. [마침(Finish)]을 선택합니다.

이제 SSMS의 세션 폴더에서 새 세션을 볼 수 있습니다. 세션을 선택(마우스 오른쪽 버튼 클릭)한 다음 [세션 시작(Start session)]을 선택합니다.

-또는-

T-SQL을 사용하여 확장 이벤트 세션을 생성하려면 다음과 유사한 쿼리를 실행합니다.

CREATE EVENT SESSION [Deadlock_detection] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'D:\rdsdbdata\Log\Deadlock',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO     
-- Start the event session  
ALTER EVENT SESSION Deadlock_detection ON SERVER  
STATE = start;  
GO

SSMS를 사용하여 교착 상태 정보를 검색하려면 다음을 수행하세요.

1. SSMS를 엽니다.
2. [객체 탐색기(Object Explorer)]에서 [관리(Management)]를 선택한 다음 [확장 이벤트(Extended Events)]를 선택합니다.
3. [세션(Sessions)]을 선택합니다.
4. 이전에 생성한 확장 이벤트 세션을 찾아서 package0.event_file을 선택(두 번 클릭)하여 확장 이벤트 파일을 엽니다.
5. 파일 내용을 로드한 후 확인하려면 이벤트를 선택하고 [교착 상태(Deadlock)] 탭을 선택하여 그래프를 봅니다.

-또는-

T-SQL에서 다음과 유사한 쿼리를 실행하여 교착 상태 목록을 확인합니다.

SELECT * FROM sys.fn_xe_file_target_read_file('d:\rdsdbdata\log\deadlock*.xel', null, null, null)

---

관련 정보

교착 상태 정보 도구에 대한 Microsoft 설명서