[MSSQL] Azure MI 에서 deadlock XEvent 로깅하기

Azure MI 에서는 보안상의 이유로  차단되어 조회 불가능한 XEvent deadlock 관련 로그를 조회 하는 방법입니다.

보안상의 이유로  이벤트 세션의  package0.event_file에 접근할 수 없거나, 디스크에 파일을 생성할 수 없는 상황이라면, 

파일 대신 Ring Buffer라는 메모리 기반 타겟을 사용하여 정보를 수집할 수 있습니다.

 

# 1. 데드락 테스트

system_health 세션은 SQL Server 및 Azure SQL Managed Instance에 기본적으로 포함된 확장 이벤트 세션입니다.
이 세션은 데이터베이스 엔진이 시작될 때 자동으로 시작되며 눈에 띄는 성능 오버헤드 없이 실행됩니다.
세션은 데이터베이스 엔진의 성능 문제를 해결하는 데 사용할 수 있는 시스템 데이터를 수집합니다.

 

확장이벤트 의 system_health 을 우클릭해서 'View Target Data ... ' 를 실행합니다.

그 후, 아래 쿼리를 서로 다른 세션 탭에서 1, 2 를 동시 실행하여 데드락을 유도합니다.

SELECT *  FROM [Table_A];
SELECT *  FROM [Table_B];

-- ==========
-- Session 1
-- ==========
BEGIN TRAN  
    UPDATE [Table_A] 
SET b = 'update'  
where a = 3 
waitfor delay '00:05';

SELECT *  FROM [Table_B];

-- ==========
-- Session 2
-- ==========
BEGIN TRAN  
    UPDATE [Table_B] 
SET b = 'update'  
where a = 3
waitfor delay '00:05';

SELECT  *  FROM [Table_A];

데드락이 걸린 것을 확인 가능합니다.

 

# 2. 데드락 정보 저장

## 2.1. 데드락 로그 저장 테이블 

데드락 로그에서 보고싶은 컬럼을 선정하여 테이블을 생성합니다.

CREATE TABLE DeadlockReports (
    TranStartTime DATETIME,              -- 트랜잭션 시작 시간
    TranEndTime DATETIME,                -- 트랜잭션 종료 시간 (UTC에서 KST로 변환)
    DatabaseName NVARCHAR(50),           -- 데이터베이스 이름
    HostName NVARCHAR(50),               -- 호스트 이름
    LoginName NVARCHAR(50),               -- 로그인 이름
    SPID INT,                             -- 세션 프로세스 ID
    WaitTime NVARCHAR(20),               -- 대기 시간
    LockMode NVARCHAR(5),                -- 잠금 모드
    EventData XML,                       -- 이벤트 데이터 (XML 형식으로 저장)
    Username NVARCHAR(50),               -- 사용자 이름
    SessionID INT,                       -- 세션 ID
    ActionDatabaseName NVARCHAR(50),     -- 액션에서의 데이터베이스 이름
    ClientHostName NVARCHAR(50),         -- 클라이언트 호스트 이름
    ClientAppName NVARCHAR(50),          -- 클라이언트 애플리케이션 이름
    ClientApp NVARCHAR(100)              -- 클라이언트 애플리케이션 정보
);


CREATE NONCLUSTERED INDEX IX_DeadlockReports_Date_DatabaseName
ON DeadlockReports ([TranStartTime], [DatabaseName]);

 

## 2.2.데드락 Extended Events 설정

데드락을 추적하는  Extended Events  세션을 생성합니다.

 

* 각 인스턴스에 하나씩 세션을 생성하여 DW 에서 linked server로도 사용할 수 있습니다.

* xml_deadlock_report 는 전체 DB에서 수집합니다.

* database_xml_deadlock_report을 사용하면 특정 DB 필터링 하여 수집이 가능합니다.

-- Ring Buffer 타겟을 사용하여 데드락 추적
CREATE EVENT SESSION [xe_deadlock_report] ON SERVER
ADD EVENT sqlserver.database_xml_deadlock_report (
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.username)
    WHERE ([sqlserver].[database_name]=N'YourDatabase' OR [sqlserver].[database_name]=N'YourDatabase2')
)
ADD TARGET ring_buffer -- 타겟을 ring_buffer로 설정
WITH (STARTUP_STATE = ON); -- SQL Server 시작 시 자동으로 세션 시작

-- 세션 시작
ALTER EVENT SESSION [xe_deadlock_report] ON SERVER STATE = START;

 

## 2.3. 로그 저장

MS 데드락 가이드 링크

DECLARE @ENDTIME NVARCHAR(100)

SELECT TOP 1 @ENDTIME = CONVERT(VARCHAR(23),[TranStartTime] ,121)    
FROM dbo.DeadlockReports
ORDER BY [TranStartTime] DESC;

-- 각 xml 데이터를 각각 열로 분리하여 테이블에 저장
INSERT INTO DeadlockReports
SELECT 
xdr.value('(./data/value/deadlock/process-list/process/@lasttranstarted)[1]', 'datetime')  AS [TranStartTime] , 
DATEADD(HOUR, 9, xdr.value('(./@timestamp)[1]', 'datetime')) AS [TranEndTime],  -- UTC to KST  
xdr.value('(./data/value/deadlock/process-list/process/@currentdbname)[1]', 'NVARCHAR(50)') AS [DatabaseName],
xdr.value('(./data/value/deadlock/process-list/process/@hostname)[1]', 'NVARCHAR(50)') AS [HostName],
xdr.value('(./data/value/deadlock/process-list/process/@loginname)[1]', 'NVARCHAR(50)') AS [LoginName],
xdr.value('(./data/value/deadlock/process-list/process/@spid)[1]', 'INT') AS [SPID],
xdr.value('(./data/value/deadlock/process-list/process/@waittime)[1]', 'NVARCHAR(20)') AS [WaitTime],
xdr.value('(./data/value/deadlock/process-list/process/@lockMode)[1]', 'NVARCHAR(5)') AS [LockMode],
xdr.query('.') AS [EventData], 
xdr.value('(./action[@name="username" ]/value)[1]','NVARCHAR(50)') as username ,
xdr.value('(./action[@name="session_id" ]/value)[1]','INT') as session_id ,
xdr.value('(./action[@name="database_name" ]/value)[1]','NVARCHAR(50)') as [database_name],
xdr.value('(./action[@name="client_hostname" ]/value)[1]','NVARCHAR(50)') as client_hostname ,
xdr.value('(./action[@name="client_app_name" ]/value)[1]','NVARCHAR(50)') as client_app_name,
xdr.value('(./data/value/deadlock/process-list/process/@clientapp)[1]', 'NVARCHAR(100)') AS clientapp
FROM (
    SELECT CAST([target_data] AS XML) AS Target_Data
    FROM  sys.dm_xe_session_targets AS xt
    INNER JOIN  sys.dm_xe_sessions AS xs ON xs.address = xt.event_session_address
    WHERE 
        xs.name = N'xe_deadlock_report'  -- 이벤트 이름 입력
        AND xt.target_name = N'ring_buffer'
) AS XML_Data
CROSS APPLY   Target_Data.nodes('RingBufferTarget/event[@name="database_xml_deadlock_report"]') AS XEventData(xdr)
WHERE xdr.value('(./data/value/deadlock/process-list/process/@lasttranstarted)[1]', 'datetime')  > @ENDTIME
ORDER BY [TranStartTime]  DESC;

 

## 2.4. 세션 종료

만약 추적이 불필요한 경우 추적을 종료합니다.

-- 세션 중지
ALTER EVENT SESSION [xe_deadlock_report] ON SERVER STATE = STOP;

-- 세션 삭제
DROP EVENT SESSION [xe_deadlock_report] ON SERVER;

 

전역 필드(동작)을 더 선택하여 액션 값을 추가 수집 가능합니다.

 

# 결과

위에 정리된 쿼리를 바탕으로 SP 를 생성하고  Job에 설정하여 일정 간격으로 실행하면 됩니다.

마침.