-------------------------------------------------------------------------------------------------------------------------------
가. SQL이 장시간 수행 되는 경우 Logging 하기
-------------------------------------------------------------------------------------------------------------------------------
-- 생성
-- ACTION - 수집 시 저장되는 정보
-- WHERE - 조건에 맞아야 수집됨 AdventureWorks2017, AdventureWorksDW2017 Database 이며, duration(수행시간)이 1초 이상인 경우 수집됨
-- max_dispatch_latency : 수집된 Memory 정보가 FILE에 Write Latency
-- STARTUP_STATE=ON : SQL Server 기동 시, 자동 ON
CREATE EVENT SESSION [Long_Running_Query] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
( ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ( ([sqlserver].[database_name]=N'AdventureWorks2017') OR ([sqlserver].[database_name]=N'AdventureWorksDW2017') )
AND duration > 1000000 /*total us(1/1,000,0000 ) of CPU time*/
)
ADD TARGET package0.asynchronous_file_target
(SET FILENAME = N'C:\XE\Long_Running_Query..xel', METADATAFILE = N'C:\XE\Long_Running_Query.xem')
WITH (max_dispatch_latency = 1 seconds, STARTUP_STATE=ON );
-- 보통 Extended Event 생성은 되나, 시작 되지 않는 경우는 FILE_NAME PATH 가 잘못된 경우이다.
-- 시작
ALTER EVENT SESSION [Long_Running_Query] ON SERVER STATE = START
GO
-- 중지
ALTER EVENT SESSION [Long_Running_Query] ON SERVER STATE = STOP
GO
-- EVENT SESSION 을 STOP 및 DROP 하여도 실제 파일이 남아 있으면, EVENT SESSION 을 새로 생성시, 과거 정보가 보여짐
-- START SESSION 시 실제 파일이 생성됨
<< CREATE EVENT SESSION Syntax >>
MAX_MEMORY = size [KB | MB]
-> 이벤트 버퍼링을 위해 세션에 할당할 최대 메모리 양을 지정 합니다. 기본값은 4MB 입니다.
크기는 정수이며 KB 또는 MB 값일 수 있습니다. 최대 2GB 를 초과할 수 없습니다.
그러나 GB 범위의 메모리 값은 사용하지 않는 것이 좋습니다.
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ( DEFAULT ), ALLOW_MULTIPLE_EVENT_LOSS, NO_EVENT_LOSS
-> 이벤트 손실을 처리하는 데 사용할 이벤트 보존 모드 지정
MAX_DISPATCH_LATENCY = ( seconds SECONDS | INFINITE ( DEFAULT )
-> 이벤트 세션 대상에 보내기 전에 메모리에 버퍼링 될 시간을 저장합니다. 기본적으로 이 값은 30 초로 설정됩니다.
-> INFINITE 버퍼가 가득 차거나 이벤트 세션이 종료된 경우에만 버퍼를 대상에 플러시 합니다.
-> MAX_DISPLATCH_LATENCY = 0 SECONDS = MAX_DISPATCH_LATENCY = INFINITE
TRACK_CAUSALITY = ON | OFF ( Default)
-> 인관 관계를 추적할지 여부를 지정합니다.
-> ON 으로 설정하면 인과 관계에 따른 다른 서버 연결에 있는 관련 이벤트의 상호 연결을 허용합니다.
STARTUP_STATE = [ ON | OFF ( Default ) ]
-> SQL Server 가 시작될 때 해당 이벤트 세션을 자동으로 시작할지 여부를 지정합니다.
-- Event 확인
select * from sys.dm_xe_objects
-- where name like '%sql%'
where name like '%dead%' ;
-- 해당 EVENT 선택 후, LIVE 데이타 감시 메뉴를 통해서 실시간 확인 가능합니다.
-- 관리 -> 확장 이벤트 -> 세션 -> Long_Running_Query 선택 후, "라이브 데이타 감시" 수행 후
-- , 아래와 같이 수행한다.
-------------------------------------------------------------------------------------------------------------------------------
-- 테스트 Case1 ) 장시간 수행 쿼리
-------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT_BIG(*) AS LONG_SQL
FROM SYS.ALL_OBJECTS A1, SYS.ALL_OBJECTS A2, sys.all_views A3 , sys.all_views A4
-- 13 초 --> 수집 된다
SELECT COUNT(*) AS SHOT_SQL
FROM SYS.ALL_OBJECTS A1, SYS.ALL_OBJECTS A2
-- 1초 미만 완료 --> 수집 되지 않는다.
--> 라이브 데이타 감시를 통해서 수집 결과를 확인 한다.
-------------------------------------------------------------------------------------------------------------------------------
-- 테스트 Case2 ) LOCK 대기 쿼리
-------------------------------------------------------------------------------------------------------------------------------
-- SESSION 1 )
USE AdventureWorks2017
GO
CREATE TABLE CHECK_LOCK ( A1 INT, A2 VARCHAR(10));
INSERT INTO CHECK_LOCK VALUES (1, 'A');
BEGIN TRAN
UPDATE CHECK_LOCK SET A2 ='AAA' WHERE A1 = 1 ;
-- SESSION 2 )
USE AdventureWorks2017
GO
UPDATE CHECK_LOCK SET A2 ='BBBBBB' WHERE A1 = 1 ;
-- 16 초 걸림
--> 라이브 데이타 감시를 통해서 수집 결과를 확인 한다.
-------------------------------------------------------------------------------------------------------------------------------
-- 실행계획 보기 // SQL 문장을 자동으로 나옴 ( Input = Plan_handle )
USE MASTER
GO
SELECT *
FROM sys.dm_exec_query_plan (0x060005005662C230E03A69CE1701000001000000000000000000000000000000000000000000000000000000);
GO
SELECT *
FROM sys.dm_exec_query_plan (0x0600050014D8D910C0B636D21701000001000000000000000000000000000000000000000000000000000000);
GO
-------------------------------------------------------------------------------------------------------------------------------
나. LOCK(Deadlock) Logging 하기
참조 링크 : https://www.brentozar.com/archive/2014/03/extended-events-doesnt-hard/
https://www.mssqltips.com/sqlservertip/6430/monitor-deadlocks-in-sql-server-with-systemhealth-extended-events/
-- 해당 EVENT 선택 후, LIVE 데이타 감시 메뉴를 통해서 실시간 확인 가능합니다.
-- 장시간 수행은 SQL 수행이 완료 시점에 기록되고, LOCK 정보도 장시간 대기 후, LOCK 획득시 기록됩니다.
-- 장시간 수행 SQL 을 중지(Cancel), Lock 대기 중 중지(Cancel) 하는 경우 기록되지 않습니다.
-->>>> Microsoft 고객 서비스 지원 센터의 도움 없이 장기적으로 사용하거나 프로덕션 환경에서 사용하면 안 됩니다. <<<<<
-- 사전 시스템 설정
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
/* Enabled the blocked process report */
-- 현재 설정 확인
EXEC sp_configure 'blocked process threshold'
GO
-- 설정 수정
EXEC sp_configure 'blocked process threshold', '5'; -- sgl 에서는 10초로 함
RECONFIGURE
GO
-- 수정 설정 확인
EXEC sp_configure 'blocked process threshold'
GO
-- blocked process threshold 옵션을 사용하여 차단된 프로세스 보고서가 생성되는 임계값을 초 단위로 지정할 수 있습니다.
-- 0에서 86,400 사이의 임계값을 설정할 수 있습니다. 기본적으로 차단된 프로세스 보고서는 생성되지 않습니다.
-- 시스템 태스크 또는 검색할 수 있는 교착 상태를 생성하지 않는 리소스를 기다리는 태스크의 경우 이 이벤트가 생성되지 않습니다.
-- 삭제
DROP EVENT SESSION [blocked_process] ON SERVER ;
-- 생성
CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report( ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report ( ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name))
ADD TARGET package0.asynchronous_file_target
(SET filename = N'C::\XE\blocked_process..xel', metadatafile = N'C:\XE\blocked_process.xem',
max_file_size=(65536), max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO
-- 시작
-- 보통 Extended Event 생성은 되나, 시작 되지 않는 경우는 FILE_NAME PATH 가 잘못된 경우이다.
ALTER EVENT SESSION [blocked_process] ON SERVER STATE = START
GO
-- 중지
ALTER EVENT SESSION [blocked_process] ON SERVER STATE = STOP
GO
-- 해당 EVENT 선택 후, LIVE 데이타 감시 메뉴를 통해서 실시간 확인 가능합니다.
-- 관리 -> 확장 이벤트 -> 세션 -> [blocked_process] 선택 후, "라이브 데이타 감시" 수행 후
-- , 아래와 같이 수행한다.
-------------------------------------------------------------------------------------------------------------------------------
-- 테스트 Case1 ) LOCK 대기 쿼리
-------------------------------------------------------------------------------------------------------------------------------
-- SESSION 1 )
USE AdventureWorks2017
GO
CREATE TABLE CHECK_LOCK ( A1 INT, A2 VARCHAR(10));
INSERT INTO CHECK_LOCK VALUES (1, 'A');
BEGIN TRAN
UPDATE CHECK_LOCK SET A2 ='AAA' WHERE A1 = 1 ;
-- SESSION 2 )
USE AdventureWorks2017
GO
UPDATE CHECK_LOCK SET A2 ='BBBBBB' WHERE A1 = 1 ;
-- 16 초 걸림
--> 라이브 데이타 감시를 통해서 수집 결과를 확인 한다.
-------------------------------------------------------------------------------------------------------------------------------
-- 테스트 Case2 ) DEADLOCK LOCK 대기 쿼리
-------------------------------------------------------------------------------------------------------------------------------
-- SESSION 1 )
USE AdventureWorks2017
GO
--Two global temp tables with sample data
CREATE TABLE ##TableA (
ID INT IDENTITY,
Val CHAR(1)
)
GO
INSERT INTO ##TableA (Val)
VALUES ('A'), ('B')
GO
CREATE TABLE ##TableB(
ID INT IDENTITY,
Val CHAR(1)
)
GO
INSERT INTO ##TableB (Val)
VALUES ('C'), ('D')
GO
-- 테스트 시작
-- Session1 )
-- code for query window 1
BEGIN TRANSACTION
SELECT @@SPID AS ProcessID
--1
UPDATE ##TableA
SET Val = 'E'
WHERE ID = 1
------------------------------------
WAITFOR DELAY '00:00:07'
--3
UPDATE ##TableB
SET Val= N'G'
WHERE ID = 1
-------------------------------------------------------------
COMMIT
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1
-- SESSION 2 )
USE AdventureWorks2017
GO
-- code for query window 2
BEGIN TRANSACTION
SELECT @@SPID AS ProcessID
--2
UPDATE ##TableB
SET Val = N'F'
WHERE ID = 1
--------------------------------------
WAITFOR DELAY '00:00:07'
--4
UPDATE ##TableA
SET Val = N'H'
WHERE ID = 1
COMMIT
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1