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

간략한 설명

각각의 리소스가 잠긴 두 개 이상의 세션에서 다른 세션의 잠긴 리소스에 액세스하려고 하면 데이터베이스 인스턴스가 교착 상태에 빠집니다. 교착 상태가 발생하면 세션 중 하나에서 잠금을 해제할 때까지 어떤 세션도 계속 실행될 수 없습니다.

SQL Server 교착 상태 탐지기는 교착 상태를 해결하기 위해 리소스 기반 또는 비용 기반 메커니즘을 사용하여 세션 중 하나를 종료합니다. 그런 다음 탐지기는 이 세션과 관련된 모든 수정 사항을 롤백합니다. 기본적으로 SQL Server 데이터베이스 엔진은 비용이 가장 적게 드는 트랜잭션을 실행하는 세션을 교착 상태 피해자로 선택하여 롤백합니다. 세션 중 하나가 종료되면 해당 세션에서 보유하고 있던 잠금이 해제되고 다른 세션은 계속할 수 있습니다. 자세한 내용은 Microsoft 웹 사이트의 교착 상태를 참조하십시오.

해결 방법

DB 인스턴스의 교착 상태 이벤트에 대한 정보를 캡처하려면 추적 플래그, system_health 세션 또는 xml_deadlock_report 확장 이벤트 세션을 사용할 수 있습니다.

추적 플래그 활성화

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

참고: 고성능 워크로드 인스턴스에는 교착 상태가 발생해도 이러한 추적 플래그를 사용하지 않는 것이 모범 사례입니다. 이렇게 하면 성능 문제가 발생할 수 있기 때문입니다. 대신 확장된 이벤트 세션을 생성하여 교착 상태 이벤트 정보를 캡처하십시오.

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

자세한 내용은 Microsoft 웹 사이트에서 추적 플래그 1204 및 추적 플래그 1222를 참조하십시오.

RDS for SQL Server의 사용자 지정 파라미터 그룹을 통해 각 교착 상태 이벤트에 대해 서로 다른 두 가지 표현을 가져오려면 두 추적 플래그를 모두 활성화하십시오. 이를 설정하려면 Amazon RDS for SQL Server DB 인스턴스에서 교착 상태 이벤트가 발생할 때 알림을 받으려면 어떻게 해야 합니까?를 참조하십시오. 추적 플래그를 활성화한 후 SQL Server 오류 로그에서 교착 상태 이벤트에 대한 자세한 내용을 검토할 수 있습니다.

system_health 세션 사용

Amazon RDS for SQL Server에서 확장된 이벤트를 사용하여 데이터를 수집하여 SQL Server 문제를 모니터링하고 문제를 해결할 수 있습니다. system_health 확장된 이벤트 세션은 SQL Server에 포함되어 있으며 기본적으로 활성화됩니다. 세션은 SQL Server 데이터베이스 엔진이 시작되고 기본 서버 상태 정보를 수집할 때 자동으로 시작됩니다. 이 정보를 사용하여 데이터베이스 엔진 내에서 성능 문제를 해결하고 교착 상태를 모니터링할 수 있습니다. 자세한 내용은 Microsoft 웹 사이트에서 system_health 세션 사용을 참조하십시오.

system_health 확장된 이벤트 세션은 이벤트 파일과 링 버퍼라는 두 개의 대상을 사용하여 데이터를 저장합니다. 링 버퍼는 선입선출(FIFO) 방식으로 데이터를 저장합니다. Amazon RDS for SQL Server에서 링 버퍼의 대상 메모리는 4MB를 초과할 수 없습니다. 따라서 사용량이 많은 인스턴스에서는 system_health 세션이 이벤트를 순환시킬 수 있습니다.

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

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

SSMS를 사용하여 교착 상태 정보를 검색하려면 다음 단계를 완료하십시오.

1. SSMS를 엽니다.
2. 개체 탐색기에서 관리를 선택한 다음 확장된 이벤트를 선택합니다.
3. 세션을 선택합니다.
4. system_health 세션을 찾은 다음 package0.event_file을 선택(두 번 클릭)하여 확장된 이벤트 파일을 엽니다.
5. 파일 내용을 로드한 후 SSMS 메뉴에서 확장된 이벤트를 선택합니다.
6. 필터를 선택합니다.
7. 필터 창에서 다음 작업을 수행합니다.
   필드에서 이름을 선택합니다.
   연산자에서 포함을 선택합니다.
   값에서 교착 상태를 선택합니다.
8. 확인을 선택합니다. 교착 상태가 있는 이벤트를 볼 수 있습니다.
9. 보려는 이벤트를 선택한 다음 교착 상태 탭을 선택하여 그래프를 확인합니다.

T-SQL을 사용하여 교착 상태 정보를 검색하려면 다음 단계를 완료하십시오.

1. 다음과 비슷한 쿼리를 실행하여 교착 상태 목록을 확인합니다.

   SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraphFROM (
       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 파일을 열어 교착 상태 그래프를 확인합니다.

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

xml_deadlock_report 확장된 이벤트 세션 사용

확장된 이벤트 세션을 만들려면 xml_deadlock_report 이벤트를 선택하여 교착 상태를 캡처합니다. 분석을 위해 이벤트를 파일에 저장하려면 이벤트 파일을 대상으로 선택합니다. 확장된 이벤트 세션을 생성하려면 SSMS 또는 T-SQL을 사용할 수 있습니다.

SSMS를 사용하여 확장된 이벤트 세션을 생성하려면 다음 단계를 완료하십시오.

1. SSMS를 엽니다.
2. 개체 탐색기에서 관리를 선택한 다음 확장된 이벤트를 선택합니다.
3. 세션을 선택(마우스 오른쪽 버튼으로 클릭)한 다음 새 세션 마법사를 선택합니다.
4. 세션 이름에 세션 이름을 입력한 후 다음을 선택합니다.
5. 템플릿 선택 페이지에서 템플릿 사용 안 함을 선택합니다.
6. 다음을 선택하여 새 세션 마법사 페이지를 엽니다.
7. 이벤트 라이브러리에서 xml_deadlock_report를 선택하고 다음을 선택합니다.
8. 글로벌 필드 캡처 페이지에서 모든 이벤트에 공통적인 값을 선택합니다.
   참고: sql_text 필드를 선택하여 교착 상태를 일으킨 쿼리를 확인합니다.
9. 다음을 선택합니다.
10. 세션 이벤트 필터 설정 페이지에서 캡처하려는 데이터를 제한하는 이벤트 필터를 생성합니다.
11. 다음을 선택합니다.
12. 세션 데이터 스토리지 지정 페이지에서 나중에 분석할 수 있도록 데이터를 파일에 저장 및 가장 최근 데이터만 사용하여 작업을 선택합니다.
13. 완료를 선택합니다.

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

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. 개체 탐색기에서 관리를 선택한 다음 확장된 이벤트를 선택합니다.
3. 세션을 선택합니다.
4. 이전에 만든 확장 이벤트 세션을 찾은 다음 package0.event_file을 선택(두 번 클릭)하여 확장된 이벤트 파일을 엽니다.
5. 파일 내용이 로드된 후 보려는 이벤트를 선택한 다음 교착 상태 탭을 선택하여 그래프를 확인합니다.

T-SQL을 사용하여 교착 상태 목록을 보려면 다음과 비슷한 쿼리를 실행합니다.

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

관련 정보

교착 상태 가이드(Microsoft 웹 사이트)

KB4541132 - 개선 사항: SQL Server 2016, 2017 및 2019의 기본 XEvent 추적 system_health에서 크기 및 보존 정책 증가(Microsoft 웹 사이트)

Amazon CloudWatch를 사용하여 Amazon RDS for SQL Server의 교착 상태를 모니터링하고 알림을 설정