통계 자동 갱신 조건과 별개로 옵티마이저 판단
SQL Server는 통계가 오래되거나 변경되면 자동으로 갱신하고 그때 계획을 다시 컴파일합니다.
하지만 통계 갱신이 없더라도, 데이터 분포 변화가 심해 기존 계획이 비효율적이라고 판단되면 옵티마이저가 강제 Recompile을 트리거할 수 있습니다.
 
DML로 인한 데이터 양 변화
예를 들어, 특정 테이블에 대량의 INSERT/DELETE가 발생해 행 수가 급격히 변하면, 기존 계획의 조인 전략(예: Nested Loop vs Hash Join)이 더 이상 적합하지 않을 수 있습니다.
이 경우 옵티마이저는 계획 캐시를 무효화하고 새 계획을 생성합니다.
ü   대량 DML로 인해 통계 임계치 초과 시 자동 갱신 → 기존 계획의 카디널리티 추정이 틀려서 재컴파일 발생

옵션 및 힌트 영향
OPTION(RECOMPILE) 힌트가 있거나, 저장 프로시저에 WITH RECOMPILE이 설정된 경우 매 실행마다 새 계획을 생성합니다.
파라미터 스니핑으로 인해 특정 값에서 기존 계획이 비효율적일 때도 Recompile이 발생합니다.
 
메모리 압박 또는 캐시 제거
DBCC FREEPROCCACHE 실행, 메모리 부족으로 인한 캐시 제거 등도 재컴파일을 유발합니다.
 
스키마 변경
o    ALTER TABLE, ALTER VIEW, 인덱스 생성/삭제
o    제약조건, 열 추가/삭제 → 계획이 참조하는 객체 구조가 바뀌면 무효화

 
SET 옵션 변경
ANSI_NULLS, QUOTED_IDENTIFIER 등 세션 옵션 변경 시
 
임시 테이블 변경
Temp Table 구조 변경, 인덱스 추가/삭제
 
기타
Query Store 강제 계획 적용 실패
데이터베이스 버전 변경
파티션 뷰 변경 등

 

또한 통계 갱신이 없더라도 옵티마이저는 내부 임계값을 기준으로 판단합니다.

예를 들어 특정 테이블에서 수백만 건의 INSERT가 발생하게 될 경우, 통계는 아직 갱신되지 않았지만, 옵티마이저는 기존 계획이 잘못될 가능성이 높다고 판단하여 Recompile을 수행할 수 있고, 파라미터 값 변화로 인해 기존 Plan이 비효율적으로 판단될 경우 Recompile을 수행할 수 있습니다.

 

Recompile이 발생하는 경우는 여러가지 이유가 있으며 테이블의 컬럼 및 인덱스 변경, Set옵션의 변경, 서버 재시작, 플랜캐시의 상황으로 캐시에서 밀려난 후 Compile, RECOMPILE 힌트, 명시적인 SP_Recompile등의 요소에 의해 발생할 수 있습니다.

: Recompile a Stored Procedure - SQL Server | Microsoft Learn

 

 

 

--XEVENT모니터링 적용

 

 
--1. 파일 타겟(100MB * 10개 순환) 세션 생성
CREATE EVENT SESSION Recompiles_File
ON SERVER
ADD EVENT sqlserver.sql_statement_recompile(
    ACTION(
        sqlserver.sql_text,
        sqlserver.database_id,
        sqlserver.session_id,
        sqlserver.plan_handle
    )
)
ADD TARGET package0.event_file(
    SET filename = N'Recompiles',      -- 실제 생성: Recompiles_0_*.xel 파일명만 적을경우 기본위치(xp_readerrorlog의 로파일 생성디렉토리)에 생성
        max_file_size = 100,                       -- MB
        max_rollover_files = 10        -- 파일 숫자를 너무 많이 할 경우 조회시 수행속도 이슈가 발생할 수 있음   
)
WITH (
    MAX_MEMORY = 20MB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    TRACK_CAUSALITY = ON,
    STARTUP_STATE = ON
);
GO
 
--2. 모니터링  Start
ALTER EVENT SESSION Recompiles_File ON SERVER STATE = START;
GO
 
 
--XEVENT모니터링  컴파일  원인확인
 
  1. XEvent저장 Default경로확인
 DECLARE @full nvarchar(4000)=CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(4000));
SELECT DefaultXEFolder =       LEFT(@full, LEN(@full) - CHARINDEX('\', REVERSE(@full)));  -- 이 폴더가 기본 .xel 저장 위치
 
 
--2 문제되는 쿼리 Plan 확인
DECLARE @Start datetime2 = '2025-10-23 01:57:13'; --시간필터
DECLARE @Query varchar(5000) = 'dbo'; --쿼리 필터
DECLARE @EventFileDir varchar(5000) = 'C:\DATA\MSSQL15.MSSQLSERVER\MSSQL\Log'; --XEvent 파일 경로 xp_readerror의 기본으로 생성시 로그파일 디렉토리와 동일함
DECLARE @EventFILE varchar(1000) = '\Recompiles*.xel'   --특별한 사유 없으면 그대로 사용할 것
SELECT session_id,  compile_time, recompile_cause_id, database_id, '0x'+ plan_handle as plan_handle, sql_text FROM
(
      SELECT
    --event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
   event_data.value('(event/@timestamp)[1]', 'datetime2(0)') AS compile_time,
    event_data.value('(event/data[@name="recompile_cause"]/text)[1]','VARCHAR(300)')   AS recompile_cause_id,
    event_data.value('(event/action[@name="session_id"]/value)[1]','VARCHAR(300)')     AS session_id,
    event_data.value('(event/action[@name="database_id"]/value)[1]','VARCHAR(300)')    AS database_id,
      event_data.value('(event/action[@name="plan_handle"]/value)[1]','VARCHAR(max)')     AS plan_handle,
    event_data.value('(event/action[@name="sql_text"]/value)[1]','nvarchar(max)') AS sql_text
FROM
      (
            SELECT CAST(event_data AS xml) AS event_data
            FROM sys.fn_xe_file_target_read_file(@EventFileDir + @EventFILE, NULL, NULL, NULL)
 
            --날짜필터조건
            WHERE  CAST(event_data AS xml).value('(event/@timestamp)[1]', 'datetime2(0)') >= @Start
            --SQL 필터조건
            AND CAST(event_data AS xml).value('(event/action[@name="sql_text"]/value)[1]','nvarchar(max)')  LIKE '%'+@Query+'%'
      ) AS x
)T1
ORDER BY compile_time DESC;
GO
 
 
-- 2-2. plan_handle + SQL 텍스트/DB 함께 조회
SELECT  cp.plan_handle,
            cp.usecounts,
        DB_NAME(t.dbid) AS DBName,
        t.text          AS SqlText,
        qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE cp.plan_handle = 문제되는Plan확인에서확인한 plan_handle; -- '' 추가하면 안됨
 

 

--SQL지연 발생시 긴급 즉조치 
-- 3-1문제된 특정플랜 삭제( plan_handle이 확인될 경우)
DBCC FREEPROCCACHE (2 문제되는 쿼리 Plan 확인에서 확인된  plan_handle);
 
 
-- 3-2 컴파일된 Plan캐시 전체삭제(모든 실행계획이 재컴파일 되므로 이도저도 안될때 Failover 하기전에 최종방법으로 수행)
DBCC FREEPROCCACHE;

'Database > SQL Server' 카테고리의 다른 글

DayLight Saving End(썸머타임 종료)에 따른 NTP 서버 시간 자동 변경  (0) 2025.10.21
Wait Type이 WRITELOG일 때 로그 수집  (0) 2025.09.24
파티션 테이블 압축  (0) 2024.07.04
SQL Server 2019 In-memory DTC Transaction이 Rollback  (0) 2024.04.18
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기  (0) 2023.02.14

SQL Server의 시간은 운영체제(Windows)의 시간이므로, 정상적인 DST 전환만으로 SQL Server나 서버 자체의 리부팅은 필요 없습니다.

SQL Server는 Windows API(GetSystemTimeAsFileTime)로 시스템 시간을 그대로 사용합니다.

SYSDATETIME (Transact-SQL) - SQL Server | Microsoft Learn

 

해당 시간 전환 전/후로 아래와 같이 쿼리하여 현재 SQL Server가 인지하는 시간값을 retrieve 하실 수 있으며,

혹시 해당 시간 전환이 잘 안 될 경우 서버에서 제대로 시간 변경이 안 되는 것이므로 이 때 NTP 서버와의 통신 상태 점검 및 Windows 레벨에서의 시간 설정을 확인해 보실 수 있습니다.

--1. 현재 시간 확인
SELECT SYSDATETIMEOFFSET() AS now_with_offset;
--2. 조회 시점 폴란드 Timezone DST 적용 여부 확인
SELECT * FROM sys.time_zone_info WHERE name LIKE 'Central European%';

 

sys.time_zone_info (Transact-SQL) - SQL Server | Microsoft Learn

'Database > SQL Server' 카테고리의 다른 글

MSSQL 실행 계획이 재생성 되는 경우  (0) 2025.10.29
Wait Type이 WRITELOG일 때 로그 수집  (0) 2025.09.24
파티션 테이블 압축  (0) 2024.07.04
SQL Server 2019 In-memory DTC Transaction이 Rollback  (0) 2024.04.18
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기  (0) 2023.02.14

WRITELOG는 SQL Server가 트랜잭션 로그에 변경 내용을 디스크에 기록할 때, 해당 작업이 완료될 때까지 대기하는 동안 발생하는 Wait Type입니다.
디스크 I/O 성능이 낮거나 순간적으로 트랜잭션 로그 기록 량이 많을 경우 이 대기가 증가할 수 있습니다.

로그 기록 지연은 해당 트랜잭션뿐 아니라 다른 트랜잭션에도 영향을 미쳐 전체 성능 저하를 유발할 수 있습니다.
WRITELOG 대기의 일반적인 원인은 다음 URL을 참고할 수 있습니다.

Troubleshoot slow SQL Server performance caused by I/O issues - SQL Server | Microsoft Learn

WRITELOG 대기 원인 분석을 위해 성능 모니터와 세션 정보 수집을 권장 드립니다.
성능 카운터는 전반적인 시스템 상태를 확인하는 데 유용하며, 세션 정보는 어떤 쿼리나 트랜잭션이 WRITELOG 대기를 발생시키는지 파악하고 세부 분석하는 데 도움이 됩니다.
수집 방법은 아래와 같습니다.

1. SQL 성능 카운터 수집 

Step1 cmd를 관리자권한으로 실행한 뒤 아래 명령을 실행하면 성능 수집기가 생성됩니다.
1초 주기로 최대 1024MB까지 수집되며, 수집 경로는 환경에 맞게 수정해 주시기 바랍니다.
 
WRITELOG 대기 발생 시점을 알고 있는 경우, 해당 짧은 기간 동안 1초 간격으로 수집할 것을 권장 드립니다.
발생 시점을 알 수 없는 경우에는 10~15초 간격으로 하루 정도 수집하는 것을 권장 드립니다.

Logman.exe create counter SQLPerfLog  -o "E:\sqlperflogs\PerfLog.csv" -f csv -v mmddhhmm -max 1024  -si 00:00:01 -c "\PhysicalDisk(*)\*" "\LogicalDisk(*)\*" "\Memory\*" "\Processor(_Total)\*" "\Process(*)\*" "\Network Interface(*)\*" "\Paging File(*)\*" "\System\*" "\SQLServer:Access Methods\*" "\SQLServer:Buffer Manager\*" "\SQLServer:Buffer Node(*)\*" "\SQLServer:Catalog Metadata(*)\*" "\SQLServer:Database Replica(*)\*" "\SQLServer:Databases(*)\*" "\SQLServer:Exec Statistics(*)\*" "\SQLServer:General Statistics\*" "\SQLServer:Latches\*" "\SQLServer:Locks(*)\*" "\SQLServer:Memory Manager\*" "\SQLServer:Plan Cache(*)\*" "\SQLServer:Resource Pool Stats(*)\*" "\SQLServer:SQL Errors(*)\*" "\SQLServer:SQL Statistics\*"  "\SQLServer:Transactions\*" "\SQLServer:Wait Statistics(*)\*" "\SQLServer:Workload Group Stats(*)\*
 
Step2 성능 수집을 시작합니다.
Logman.exe start SQLPerfLog
 
** 성능 수집을 중지하려면 아래 명령어를 입력합니다.
Logman.exe stop SQLPerfLog

2. 세션 정보

초단위로 DMV를 사용하여 세션 정보를 기록합니다.


Step1 로그 적재용 임시 데이터베이스 및 테이블을 생성합니다.

CREATE DATABASE tmpMonitoringDB
GO
 
use tmpMonitoringDB
GO
 
select top(1) getdate() as ctime, db_name(der.database_id) as database_name, der.*, dest.text, des.program_name
into dm_exec_requests_history
from sys.dm_exec_requests as der
inner join sys.dm_exec_sessions as des on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as dest
GO
 
truncate table dm_exec_requests_history
GO

 
Step2 1초 단위로 루프를 수행하면서 세션 정보를 수집합니다.
무한 루프이므로 상황이 종료된 이후 쿼리를 직접 취소하시면 됩니다.

declare @startTime datetime = getdate()
while 1=1
begin
    insert into dm_exec_requests_history
    select
        getdate() as ctime,
        db_name(der.database_id) as database_name,
        der.*,
        dest.text,
        des.program_name
    from sys.dm_exec_requests as der
    inner join sys.dm_exec_sessions as des on der.session_id = des.session_id
    cross apply sys.dm_exec_sql_text(der.sql_handle) as dest
    where der.session_id <> @@spid
     waitfor delay '00:00:01'
end

'Database > SQL Server' 카테고리의 다른 글

MSSQL 실행 계획이 재생성 되는 경우  (0) 2025.10.29
DayLight Saving End(썸머타임 종료)에 따른 NTP 서버 시간 자동 변경  (0) 2025.10.21
파티션 테이블 압축  (0) 2024.07.04
SQL Server 2019 In-memory DTC Transaction이 Rollback  (0) 2024.04.18
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기  (0) 2023.02.14
  1. 현상

DB_01 / DB_02 서버와 관련하여, (UTC+9) 03:29 ~ 04:42 구간 DB_02 서버로의 Failover 시도 5회 진행 하였으나,
RESOLVING_PENDING_FAILOVER 상태에서 DB_01으로 Fail back 및 DB_ 02 서버는 Resolving 상태에 빠진 이후 다시 Secondary Online 되었습니다.

03:29:32.770  spid142  The state of the local availability replica in availability group 'DB_AVG' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'.......
03:35:45.550  spid111  The state of the local availability replica in availability group 'DB_AVG' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'.......
03:44:16.190  spid70   The state of the local availability replica in availability group 'DB_AVG' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'.......
03:46:32.790  spid111  The state of the local availability replica in availability group 'DB_AVG' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'.......
04:37:00.190  spid135  The state of the local availability replica in availability group 'DB_AVG' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'.......

 

이와 관련하여 03:35:45 ~ 03:40:50 구간의 Failover 이력 확인 시, DB_ 01 > DB_ 02 방향의 Manual Failover 과정에서 DB_ 02 > DB_ 01 방향의 Connection Timeout 이슈로 Failover가 실패한 것으로 확인됩니다.

Timeline Primary ( DB_ 01 / 82A59128-5DE5-45B0-9D84-0A3B2E3DCBCD) Secondary ( DB_ 02 / C21209E8-81DD-4DD6-9EF7-70C48FE522E8)
03:35:45.550
: 관리자 요청에 의해 Failover 시작		   spid111   The state of the local availability replica in availability group 'DB_AVG' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'. 
The state changed because of a user initiated failover.  ...... ......
03:35:45.570 The state of the local availability replica in availability group 'DB_AVG' has changed from 'PRIMARY_NORMAL' to 'RESOLVING_NORMAL'.  The state changed because the availability group is going offline.  ............  ...... ......  
03:35:45.570 The availability group database "DB_NAME" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. ......  
03:36:00.580
: DB_ 02 > DB_01 방향의
 Connection Timeout 발생		   A connection timeout has occurred on a previously established connection to availability replica ' DB_ 01' with id [82A59128-5DE5-45B0-9D84-0A3B2E3DCBCD].  Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
03:36:00.580   Always On Availability Groups connection with primary database terminated for secondary database 'DB_NAME' on the availability replica ' DB_ 01' with Replica ID: {82a59128-5de5-45b0-9d84-0a3b2e3dcbcd}. ......
03:36:05
~ 03:36:25		 Remote harden of transaction 'GhostCleanupTask' (ID 0x0000004f44ca32ed 0001:ea796d64) started at Aug 17 2025  3:36AM in database 'DB_NAME' at LSN (521452:7796:2) failed.
……
Remote harden of transaction 'GhostCleanupTask' (ID 0x0000004f44ca4f41 0001:ea796d89) started at Aug 17 2025  3:36AM in database 'DB_NAME' at LSN (521452:8261:3) failed.		  
03:36:15.590
: DB_02 > DB_ 01 방향의
 Connection Timeout 계속 발생		   A connection timeout has occurred while attempting to establish a connection to availability replica ' DB_ 01' with id [82A59128-5DE5-45B0-9D84-0A3B2E3DCBCD]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.
03:36:27.410
: DB_ 01 Node가
 Primary_Normal 로 복귀		 The state of the local availability replica in availability group 'DB_AVG' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'.  The state changed because the local replica has completed processing Online command from Windows Server Failover Clustering (WSFC).  ...... ......  
03:36:39.650
: DB_ 01 Node와 재연결 확인		   Always On Availability Groups connection with primary database established for secondary database 'DB_NAME' on the availability replica ' DB_ 01' with Replica ID: {82a59128-5de5-45b0-9d84-0a3b2e3dcbcd}. This is an informational message only. No user action is required.
03:40:50.570
: DB_ 02 Node가
Resolving_Normal 로 복귀		   The state of the local availability replica in availability group 'DB_AVG' has changed from 'RESOLVING_PENDING_FAILOVER' to 'RESOLVING_NORMAL'. 
The state changed because of a user initiated failover.  ...... ......
03:40:50.600
: DB_ 02 Node가
Secondary_Normal 로 복귀		   The state of the local availability replica in availability group 'DB_AVG' has changed from 'RESOLVING_NORMAL' to 'SECONDARY_NORMAL'.  The state changed because the availability group state has changed in Windows Server Failover Clustering (WSFC).  ...... ......
  1. 원인

-  SQL Login [NT Authority\System]의 필수 권한 누락 이슈 확인

ü   [NT AUTHORITY\SYSTEM] 사용자에 대해 “ALTER ANY AVAILABILITY GROUP” 권한 및 “VIEW SERVER STATE” 권한이 누락된 경우,
고객환경과 동일하게 “Failover를 받는 기존 Secondary Replica”에서 “기존의 Primary Replica“ 방향의 Connection Timeout 오류가 재현 되었습니다.

 

ü   이와 관련하여 EXEC sp_server_diagnostics 실행 실패 및 EXEC sp_availability_group_command_internal 실행 실패가 Connection Timeout을 유발하는 원인으로 파악 되었습니다.

 
“VIEW SERVER STATE” 누락 시 – EXEC sp_server_diagnostics 실행 실패로 인한 Failover 실패 확인
“ALTER ANY AVAILABILITY GROUP” 누락 시 – EXEC sp_availability_group_command_internal 실행 실패로 인한 Failover 실패 확인

ü   고객환경의 Cluster Log를 통해, Failover 실패구간을 포함한 전체 구간에서 “VIEW SERVER STATE” 권한 누락으로 인해 'EXEC sp_server_diagnostics 10' 명령이 반복적으로 실패하는 것을 확인할 수 있었으므로,
[NT AUTHORITY\SYSTEM] 사용자에 대한 권한 누락은 Failover 실패 상황에 대한 원인으로 확인 되는 점 안내 드립니다.

……
[Verbose] 000024e4.00000b10::2025/08/17-03:35:54.663 INFO  [RES] SQL Server Availability Group: [hadrag] Connect to SQL Server ...
[Verbose] 000024e4.00000b10::2025/08/17-03:35:54.665 INFO  [RES] SQL Server Availability Group: [hadrag] The connection was established successfully
[Verbose] 000024e4.00000b10::2025/08/17-03:35:54.666 INFO  [RES] SQL Server Availability Group: [hadrag] Run 'EXEC sp_server_diagnostics 10' returns following information
[Verbose] 000024e4.00000b10::2025/08/17-03:35:54.666 ERR   [RES] SQL Server Availability Group: [hadrag] ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The user does not have permission to perform this action. (297)
[Verbose] 000024e4.00000b10::2025/08/17-03:35:54.666 ERR   [RES] SQL Server Availability Group: [hadrag] Failed to run diagnostics command. See previous log for error message
[Verbose] 000024e4.00000b10::2025/08/17-03:35:54.666 INFO  [RES] SQL Server Availability Group: [hadrag] Disconnect from SQL Server
……

 

 

[Resolution]

  1. [NT AUTHORITY\SYSTEM] 사용자의 누락된 권한 추가 (적용 완료)

- Failover 실패 상황을 예방하기 위해, SQL Server Always on Availability group 구성된 모든 서버에서 [NT AUTHORITY\SYSTEM] SQL Login에 대해 다음의 권한을 부여하여 주십시오.

다음 3개의 권한은 SQL Server의 Default 권한으로, 해당 권한이 누락될 경우 OS와의 Interaction이 필요한 상황에서 SQL Server의 정상 동작을 보장받지 못할 수 있습니다.

USE [master]
GO
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO

 

  1. NT SERVICE\MSSQLSERVER 사용자의 Sysadmin 권한 부여 (적용 완료)

-        엔지니어의 환경에서 테스트 시 서비스 가상계정의 sysadmin 권한이 Failover 실패에 직접적인 영향을 미치지는 않는 것으로 보이며,

해당 권한의 누락이 SQL Server의 일상적인 운영에 큰 영향을 주지는 않으나, 해당 권한은 SQL Server 설치시 부여되는 Default 권한으로 제거 시 예기치 못한 영향을 발생시킬 수 있어, 가급적 동 권한을 모든 노드에도 추가하실 것을 SQL Server 설정에 대한 Best Practice로서 권장 드립니다.

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT Service\MSSQLSERVER]
GO

 

  1. SQL Server Audit 구성을 통한 Server/Data Principal의 변경사항 기록

-        SQL Server의 Server Audit을 구성하여 다음과 같이 Server/Data Principal에 대한 변경이력을 수집/조회 할 수 있습니다.

: SQL Server Audit (Database Engine) - SQL Server | Microsoft Learn

SELECT  event_time, sequence_number, action_id, succeeded, session_id, database_name
    ,   statement, server_principal_name, client_ip, application_name
    ,   host_name
  FROM  sys.fn_get_audit_file('D:\YourLogPath\*.sqlaudit', DEFAULT, DEFAULT)
ORDER  BY
        event_time
    ,   sequence_number

 

-        다음은 Server/Database Principal에 대한 Role, Permission에 대한 Role member 및 Permission 변경에 대한 이력을 수집하는 Server Audit의 예제입니다.

다음의 스크립트는 Audit의 예제로, 고객환경에서 모니터링을 필요로 하는 Audit Specification을 추가로 정의하여 사용하실 수 있을 것으로 의견 드립니다.

USE [master]
GO
 
CREATE SERVER AUDIT [Audit_ChangePermissions]
TO FILE
(   FILEPATH = N'D:\YourLogPath\' -- 해당 폴더에 SQL Server 서비스계정의 Full Control 권한 필요
,   MAXSIZE = 0 MB -- 크기 무제한
,   MAX_ROLLOVER_FILES = 2147483647 -- Rollover 개수 최대
,   RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
 
ALTER SERVER AUDIT [Audit_ChangePermissions] WITH (STATE = OFF)
GO
 
CREATE SERVER AUDIT SPECIFICATION [AuditSpec_ChangePermissions]
FOR SERVER AUDIT [Audit_ChangePermissions]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = OFF)
GO
 
ALTER SERVER AUDIT SPECIFICATION [AuditSpec_ChangePermissions] WITH(STATE = ON);
GO
ALTER SERVER AUDIT [Audit_ChangePermissions] WITH(STATE = ON)
GO
 

 

  1. Server DDL Trigger를 활용하여 허가받지 않은 DDL 수행을 차단

-        SQL Server의 Server Trigger (Server DDL Trigger)를 통해, 허가받지 않는 DDL의 수행을 차단할 수 있습니다.

: DDL Events - SQL Server | Microsoft Learn

 

-        다음은 Server/Database Principal에 대한 Role, Permission에 대한 작업을 차단하는 DDL Trigger의 예제입니다.

해당 예제를 참고하고, 필요한 DDL 이벤트를 추가/제거하여 고객환경에서 허가받지 않은 작업을 차단하는 DDL Trigger를 활용하실 수 있을 것으로 의견 드립니다.

--Trigger 생성 예제
CREATE TRIGGER SRTI__PreventPermissionChange ON ALL SERVER
FOR REVOKE_DATABASE, GRANT_DATABASE, DENY_DATABASE
,   REVOKE_SERVER  , GRANT_SERVER  , DENY_SERVER
,   CREATE_ROLE, DROP_ROLE, ALTER_ROLE
,   CREATE_USER, DROP_USER, ALTER_USER
,   ADD_ROLE_MEMBER       , DROP_ROLE_MEMBER
,   ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER
AS
BEGIN
    PRINT '!!! Change permission is now allowed !!!';
    ROLLBACK;
END;
GO
 
--Trigger가 Enable된 상태에서 Grant 시도
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO


 
--Maintenance 수행 시 Trigger를 잠시 Disable 하고 권한 변경을 수행
DISABLE TRIGGER SRTI__PreventPermissionChange ON ALL SERVER
GO
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
ENABLE  TRIGGER SRTI__PreventPermissionChange ON ALL SERVER
GO

 

 

[추가 안내사항]

하기 내용은 본 문의 건을 지원 드리는 과정에서 문의주신 추가 안내사항을 취합/정리한 내용입니다.

 

  1. DB 서비스 계정에서 sysadmin 권한이 누락 되었을 때 Fail over와 관련이 있을까요 (lchdgqms01, lchdgqms02 노드 둘다 권한이 없었습니다. 현재는 sysadmin 권한 부여된 상태 입니다.)

-         엔지니어의 테스트 환경에서 테스트 시, 기 구성된 Always on Availability group 에 대해 SQL Server 서비스 계정 및 서비스 가상 계정에 대해 Sysadmin 권한이 누락되었다고 하여 Failover에 직접적인 영향을 주지는 않는 것으로 확인 됩니다.

다만 이는 엔지니어의 테스트 환경에 제한된 사항이며, SQL Server 서비스 가상계정에 대한 sysadmin 권한은 필수사항으로 누락 시 정상적인 Failover를 보장하지 않습니다.

 

-         서비스 계정에 대한 직접적인 Sysadmin의 부여가 필수 권한은 아닙니다만, 서비스계정의 운영상 편의를 위해 Sysadmin 권한을 부여하는 경우가 많습니다.
참고로 SQL Server Service 가상 계정에 대한 Sysadmin은 필수 권한입니다.

 

-         아래와 같이 SQL Server를 설치한 직후 기본 권한을 확인 시, 테스트환경의 서비스 가상 계정인 ”NT SERVICE\MSSQL$MSSQLSERVER2'”만 Sysadmin을 보유하고 있으며,
SQL Server 시작 계정인 “CONTOSO\SQLAdmin”는 아무런 권한도 보유하고 있지 않은 상태 (아직 로그인이 생성되지 않음) 임을 확인할 수 있습니다.

-- Service Account 확인
SELECT servicename, service_account FROM sys.dm_server_services;


 
-- 부여된 Permission 확인
SELECT  server_permission = SUSER_NAME(grantee_principal_id)
        ,   *
  FROM  sys.server_permissions
WHERE  SUSER_NAME(grantee_principal_id) = 'CONTOSO\SQLAdmin' -- 서비스계정 (Login 생성되지 않음)
    OR  SUSER_NAME(grantee_principal_id) = 'NT AUTHORITY\SYSTEM' -- 시스템 계정
    OR  SUSER_NAME(grantee_principal_id) = 'NT SERVICE\MSSQL$MSSQLSERVER2' --서비스 가상 계정
-- 부여된 Server Role 확인
SELECT  server_role           = SUSER_NAME(role_principal_id)
    ,   member_principal_name = SUSER_NAME(member_principal_id)
    ,   *
  FROM  sys.server_role_members
WHERE  SUSER_NAME(member_principal_id) = 'CONTOSO\SQLAdmin' -- 서비스계정 (Login 생성되지 않음)
    OR  SUSER_NAME(member_principal_id) = 'NT AUTHORITY\SYSTEM' -- 시스템 계정
    OR  SUSER_NAME(member_principal_id) = 'NT SERVICE\MSSQL$MSSQLSERVER2' --서비스 가상 계정
 
--sysadmin은 서비스 가상 계정에만 부여되어있음

 

  1. 로컬 Administrators 그룹에서 DB 서비스 계정이 누락 되었을 때 Fail over 실패와 관련이 있을까요  (lchdgqms01, lchdgqms02 두 서버 누락된 상태 였습니다. 현재는 서비스 계정이 추가되었습니다.)

-         SQL Server 서비스계정에 대한 Local Administrator 권한 또한 일반적으로 운영상 편의를 위해 부여하는 권한으로, SQL Server Always on Availability group의 Failover 실패와 직접적인 연관이 없어야 합니다.

 

  1. 다른 엔지니어분이 전달 주신 권한 관련 쿼리 입니다. 해당 쿼리가 Fail over 실패와 관련이 있을까요
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO

-         네, 해당 권한은 Windows Server Failover Cluster가 SQL Server Always on Availability group을 핸들링 하기 위한 최소 권한입니다.

각각 “ALTER ANY AVAILABILITY GROUP” 권한은 AG의 컨트롤, “CONNECT SQL”권한은 쿼리 실행, “VIEW SERVER STATE”은 서버 상태 확인을 위한 sp_server_diagnostics 실행을 위한 필수 권한입니다.

 

-         테스트환경에서 [NT AUTHORITY\SYSTEM]에 대한 ALTER ANY AVAILABILITY GROUP 권한 제거 후 테스트 수행 시, 고객 환경에서 발생하였던 동일 오류가 동일하게 재연되는 것으로 확인됩니다.

# 테스트환경의 Secondary에서 REVOKE ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM] 수행 후 Failover Test 시 고객 오류 재현
# W22SQL22-AG01 > W22SQL22-AG02로 Failover 시작
2025-08-18 09:34:55.500       spid75    The state of the local availability replica in availability group 'W22SQL22-AG' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'. 
The state changed because of a user initiated failover.  ……
# Failover 과정에서 W22SQL22-AG02 > W22SQL22-AG01 방향의 Timeout 발생
2025-08-18 09:36:14.200       spid51s   A connection timeout has occurred on a previously established connection to availability replica 'W22SQL22-AG01' with id [25700621-07A8-4D19-ACDB-4A43B5352199]. 
Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
2025-08-18 09:36:14.200   spid51s       Always On Availability Groups connection with primary database terminated for secondary database 'OneDataFile' on the availability replica 'W22SQL22-AG01'
           with Replica ID: {25700621-07a8-4d19-acdb-4a43b5352199}. This is an informational message only. No user action is required.
2025-08-18 09:36:14.200       spid60s   Always On Availability Groups connection with primary database terminated for secondary database 'AGDB01' on the availability replica 'W22SQL22-AG01'
                                             with Replica ID: {25700621-07a8-4d19-acdb-4a43b5352199}. This is an informational message only. No user action is required.
2025-08-18 09:36:24.200       spid51s   A connection timeout has occurred while attempting to establish a connection to availability replica 'W22SQL22-AG01' with id [25700621-07A8-4D19-ACDB-4A43B5352199].
 Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.
 
# Secondary(W22SQL22-AG02) <-> Primary(W22SQL22-AG01)간의 TCP 5022 통신 정상 확인

 

-         참고로, 해당 권한은 아래와 같이 SQL Server 설치 이후 Default로 할당되는 권한임을 확인하실 수 있습니다.

-- SQL Server 설치후 부여된 기본 Permission 확인
SELECT  server_permission = SUSER_NAME(grantee_principal_id)
        ,   *
  FROM  sys.server_permissions
WHERE  SUSER_NAME(grantee_principal_id) = 'NT AUTHORITY\SYSTEM' -- 시스템 계정

 

  1. 권한 누락 관련 원인분석 요청

-         안타깝게도 고객환경에서 Audit을 수집하지 않으셨다면, 현 시점에서 [NT Authority\System] 사용자에 대한 과거 변경이력을 확인할 수는 없습니다.

이에 안타깝게도 현 시점에서 SQL Server 측면에서의 [NT Authority\System] 사용자에 대한 SQL Server Side의 권한 누락 원인분석은 어려울 것으로 판단됩니다.

 

-         현재 데이터베이스 접근제어 솔루션을 통해 권한 누락 경위를 확인 중이신 상태로 공유해 주셨습니다.

안타깝게도 현재로서는 DAC에 의한 권한 누락 경위 확인이 가장 유효한 원인 분석 의견으로 판단됩니다.

 

  1. 설치 부터 잘못되었을 가능성

-         만일 처음부터 [NT Authority\System] 로그인에 대한 권한 설정이 잘못되었다면, 최초 Always on Availability group 구성 및 Failover Test를 수행하는 단계에서부터 문제가 발생했을 것이므로,

[NT Authority\System] 로그인에 대한 권한 변경은 Always on Availability group 구성 이후 시점이나, 가장 최근의 Failover Test 정상 완료 이후에 발생한 것으로 보는 것이 합당하다는 의견입니다.

 

  1. OS 그룹에서 DB 서비스계정 그룹 권한 변경 (그룹 제외시) -> OS의 administator 그룹에서 DB 서비스 계정 제거시 "NT AUTHORITY\SYSTEM" 계정 권한 회수 여부

-         테스트를 통해 SQL Server 서비스계정에 대한 OS의 Administrators 권한 제거와 "NT AUTHORITY\SYSTEM"의 계정 권한 회수는 관계가 없는 것으로 확인하였습니다.

 

  1. 서버 역할에서 제외 시 권한 회수 여부 (예: 원래 sysadmin 이었는데 보안 강화로 제거) => 해당 작업은 권한 회수되지 않는 것으로 내부 테스트 완료했습니다만, 검토 요청 드립니다.

-         ALTER ANY AVAILABILITY GROUP, CONNECT SQL 및 VIEW SERVER STATE 권한은 SQL Server 설치시 "NT AUTHORITY\SYSTEM"에 부여되는 Default 권한입니다.

단순 sysadmin 권한 회수로는 "NT AUTHORITY\SYSTEM"의 Default 권한 회수가 일어나지 않는 것으로 확인하였습니다.

 

  1. 엔드포인트 권한 초기화 스크립트 적용시 권한 회수 여부

-         단순 Endpoint에 대한 권한 초기화는 "NT AUTHORITY\SYSTEM"의 Default 권한 회수가 일어나지 않는 것으로 확인하였습니다.

 

  1. 보안 하드닝/감사 패키지가 GRANT/REVOKE 템플릿을 일괄 적용하면서 의도치 않게 회수. -> 해당 작업을 할 수 있는 기능이 혹시 DB 기본기능으로 내장되어 있다면, 점검 방법을 알려주시면 점검해보겠습니다.

-         3rd party tool에 대한 보안점검결과 반영 시 필수 권한이 회수되어 장애 발생으로 케이스가 생성되는 사례가 간혹 발생합니다.

이와 같은 경우, Server Audit을 구성하여 추적을 진행할 수 있습니다.

Server Audit과 관련하여서는 아래 본문에서 관련 안내 드리겠습니다.

 

  1. 혹시 추가로 다른 작업에 연계해서 변경이 될만한 부분이 있는지도 한번 체크 부탁드립니다. 

-         일반적으로 보안점검 지적사항 반영과 연계하여 권한 문제가 발생하는 것이 가장 자주 발생하는 이슈입니다.

또한 보안점검 지적사항으로 인해 "NT AUTHORITY\SYSTEM" 사용자를 삭제하였다가 다시 추가하면서 필수 권한을 추가하지 않아 문제가 발생하는 사례또한 함께 공유 드립니다.

 

  1. 저희가 2022버전도 일부 운영을 하고 있는데 관련 내용을 검색하다보니 아래와 같은 내용도 확인을 했습니다.
    SQL 2022     역시 SQL 2012 이후의 정책을 이어받아, SYSTEM 계정보다는 서비스 SID(NT SERVICE\<InstanceName>, NT SERVICE\SQLSERVERAGENT)를 권한 부여 대상으로 사용합니다.

2022버전에는 NT AUTHORITY\SYSTEM 계정이 아닌 별도의 계정을 사용하는지도 확인 요청 드립니다.

  • Windows Server Failover Cluster가 SQL Server에 접근할 때, SQL Server 2022에서도 기존 버전과 마찬가지로 “NT AUTHORITY\SYSTEM”를 사용하는 것으로 확인 하였습니다.

 

'Database > AlwaysOn' 카테고리의 다른 글

Always-on Availability group(AG) 구성의 SQL Server에서 Primary replica가 병렬 Index Rebuild 수행 시 Secondary도 동일한 수량의 병렬 Index Rebuild를 수행 하는가  (0) 2024.08.22
Log shrink 동기화 지연  (0) 2024.02.14
Alwayson 동기화 지연 원인 파악  (1) 2024.02.14
데이터 동기화 대기 시간에 대한 일반적인 원인 및 문제 해결  (0) 2024.02.14
SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰  (0) 2023.04.05

SAP Education - Certification.PDF
0.24MB

 

 

TADM55 SAP HANA Installing and administering

'ETC' 카테고리의 다른 글

CentOS 7에 PostgreSQL 13을 설치하는 방법  (1) 2020.11.18
클라우드 환경에서 데이터를 활용하면 어떤 장점이 있을까?  (0) 2020.11.11
SP_ERRORLOG  (0) 2020.11.02
클러스터 그룹 변경  (0) 2020.08.28
Net Framework install 오류 코드 : (0x00000057), "매개 변수가 잘못되었습니다."  (0) 2020.08.28

[진행사항]

Always-on Availability group(AG) 구성의 SQL Server에서 Primary replica가 병렬 Index Rebuild 수행 시 Secondary도 동일한 수량의 병렬 Index Rebuild를 수행하는지를 문의주신 건으로 이해됩니다.

 

AG의 데이터 동기화는 Primary Replica에서 발생한 Transaction log를 Secondary Replica에서 Redo하는 단계로 진행됩니다.

참고로 SQL Server 2016 이후 Parallel redo thread(Instance당 최대 100개, Database당 최대 16개)를 지원합니다.

다만, 이 Parallel redo thread의 개수는 Primary replica에서 Transaction log를 발생시킨 쿼리의 DoP와 관계 없이, 현재 Secondary replica에서 Transaction log를 Redo하는 부하 상황에 따라 달라지게 됩니다.

: SQL Server 2016/2017: Availability group secondary replica redo model and performance - Microsoft Community Hub

 

이 때문에, Primary replica에서 병렬 Index Rebuild 수행 시 Secondary replica에서 병렬로 Redo를 수행할 가능성은 높으나,
Index Rebuild 상황을 Redo하는 Secondary Replica의 Redo Thread 개수는, 일반적으로 Primary replica의 Index Rebuild Thread 개수보다 적게 됩니다.

 

예를 들어 다음과 같이 테스트환경에서 약 10GB 크기의 테이블에 대해, MAXDOP 4의 병렬 Index Rebuild를 수행하여 Secondary Replica에서 Redo thread를 모니터링 하면,
Primary Replica는 DoP 4로 Index Rebuild를 수행하고 있으나, 같은 시점의 Secondary Replica는 3개의 Redo thread로 Transaction을 Redo 하고 있음을 확인할 수 있습니다.

Case Result
Test Scenario
- 약 10GB 크기의 테이블에 Maxdop=4로 Index Rebuild를 수행		 EXEC sys.sp_spaceused 'dbo.tbl_Index_ParallelBuild'
GO        


 
ALTER INDEX UCL__tbl_Index_ParallelBuild__01 ON dbo.tbl_Index_ParallelBuild REBUILD WITH (MAXDOP = 4)
GO
 
Primary Replica
- 첨부드리는 Monitor_IndexRebuild_ParallelRedo.sql 쿼리를 통해
74번 세션에서 수행중인 Index Rebuild 수행 상황을 모니터링 하면,
2개의 Consumer thread를 제외하고, 4개의 Thread로 Parallel Rebuild 수행중임을 확인할 수 있습니다.
Secondary Replica
- 첨부드리는 Monitor_IndexRebuild_ParallelRedo.sql 쿼리를 통해
AG의 Redo 수행 상황을 모니터링 하면, AGDB01 데이터베이스에 대해 3개의 Parallel redo thread가 Index rebuild를 수행중임을 확인할 수 있습니다.
 

 

Monitor_IndexRebuild_ParallelRedo.sql
0.00MB

'Database > AlwaysOn' 카테고리의 다른 글

NT AUTHORITY\SYSTEM 권한 누락으로 인한 Failover 오류  (0) 2025.09.10
Log shrink 동기화 지연  (0) 2024.02.14
Alwayson 동기화 지연 원인 파악  (1) 2024.02.14
데이터 동기화 대기 시간에 대한 일반적인 원인 및 문제 해결  (0) 2024.02.14
SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰  (0) 2023.04.05

1.     파티션 테이블 압축시 서비스 영향도

-> 온라인으로 가능한지, IO 발생 및 압축진행중인 파티션 사용시 lock 발생 등의 위험요소가 궁금합니다.

-     파티션별 압축의 경우에도 전체 인덱싱과 마찬가지로 온라인 인덱싱을 지원합니다.

Online Indexing의 경우, 인덱싱 작업중 대상 테이블/파티션에 대한 CRUD가 가능하나, 아래와 같이 단계에 따라 작업 및 잠금을 획득하며, 이 과정에서 여러가지 형태의 성능 경합과 잠금이 발생합니다.

다만 해당 환경의 성능 여유가 충분한 상황이라면, 일반적으로 이와 같은 성능 경합이 심각한 성능 영향을 주지는 않습니다.

 

이와 관련하여서는 다음의 문서를 통해 조금 더 세부적인 내용을 확인하실 수 있습니다.

: How Online Index Operations Work - SQL Server | Microsoft Learn

Phase Source activity Source locks Architecture
Preparation
: Short phase		 -  System metadata preparation to create the new empty index structure.
 
-  A snapshot of the table is defined. That is, row versioning is used to provide transaction-level read consistency.
 
-  Concurrent user write operations on the source are blocked for a short period.
No concurrent DDL operations are allowed except creating multiple nonclustered indexes.		 -  S (Shared) on the table*
 
-  IS (Intent Shared)
 
-  INDEX_BUILD_INTERNAL_RESOURCE**
Build
: Main phase		 -  The data is scanned, sorted, merged, and inserted into the target in bulk load operations.
 
-  Concurrent user select, insert, update, and delete operations are applied to both the pre-existing indexes and any new indexes being built.		 -  IS
 
-  INDEX_BUILD_INTERNAL_RESOURCE**
Final
: Short phase		 -  All uncommitted update transactions must complete before this phase starts.
 
-  Depending on the acquired lock, all new user read or write transactions are blocked for a short period until this phase is completed.
 
-  System metadata is updated to replace the source with the target.
 
-  The source is dropped if it is required. For example, after rebuilding or dropping a clustered index.		 -  INDEX_BUILD_INTERNAL_RESOURCE**
 
-  S on the table if creating a nonclustered index.*
 
-  SCH-M (Schema Modification) if any source structure (index or table) is dropped.*

 

-     다음은 온라인 인덱싱 작업에 대한 일반적인 가이드라인입니다. Online Indexing 작업 중 발생할 수 있는 여러가지 이슈사항과 사전 고려사항을 정리하고 있으며,
Update 부하가 높은 환경에서 Online Indexing 중, 종종 발생하는 Deadlock(자동으로 해소)과 같은 증상도 해당 문서를 통해 확인할 수 있습니다.

: Guidelines for online index operations - SQL Server | Microsoft Learn

 

-> 온라인으로 절대 해서는 안되는 작업인지

-     온라인 수행에 문제는 없습니다. 다만 고객환경의 비즈니스로직에서의 성능 영향도를 파악하기 위해 고객사 내부적으로 충분한 테스트를 거쳐 검증하신 후 작업을 수행하실 것을 권장 드립니다.

 

-> 압축 진행중인 해당 파티션에만 영향을 주는 것인지

-     특정 파티션에 대해 압축을 수행하는 경우, 결과적으로는 해당 파티션에만 영향을 주는 것이 맞습니다.

하지만 압축을 진행하는 과정의 마지막 단계에서 Metadata를 업데이트 하며 Sch-M 잠금을 획득하므로, 동 시간대 대상 테이블에 대해 Sch-S/Sch-M을 요청하는 작업들과의 일시적인 성능 경합이 있을 수 있습니다.

 

2.     테이블/인덱스 압축시 내부 프로세스 및 모니터링 필수 요소

-> 압축대상 파티션을 바로 압축을 시도 하는지 혹은 별도의 데이터파일 여유 공간에 생성후 전환 (rebuild 처럼) 등등

-     인덱스의 파티션별 압축 작업 또한 마찬가지로 ALTER INDEX REBUILD 작업을 수행하며, 데이터파일 신규 공간을 할당 받아 Sort 및 인덱스를 생성한 후 기존의 개체를 대체하게 되므로 추가 공간을 사용하게 됩니다.

예를 들어 30GB 크기의 A인덱스의 1번 파티션을 압축하여 10GB로 압축이 수행된 경우, 작업 시작 시점에 Datafile 내 여유공간이 없었다면 데이터파일의 크기가 10GB 증가하게 됩니다.

 

-     Sort_in_tempdb 옵션을 사용하는 경우에도 새로운 공간에 신규 인덱스를 배치한 후 기존의 인덱스 개체를 제거하므로 추가 Disk 공간의 사용은 발생합니다.

 

-> 롤백이 발생하게 되나요?

-     Offline Indexing의 경우 작업을 중단하는 경우 해당 작업에 대한 Rollback이 발생하며, Online Indexing의 경우에도 작업 중단에 의해 메타데이터 업데이트를 위한 짧은 Rollback이 필요합니다.

-     작업 수행 중 다음의 내용을 모니터링 하실 수 있을 것으로 의견 드립니다.

①     Data / Log Disk 사용률 모니터링

②     Blocked Process 발생 여부 모니터링

-- sys.dm_exec_requests (Transact-SQL) - SQL Server | Microsoft Learn
SELECT session_id, status, blocking_session_id, wait_type, wait_time, wait_resource, transaction_id
FROM sys.dm_exec_requests WHERE status = N'suspended';
GO

 

③     Data File 사용률 모니터링

SELECT  Database_Name   = db_name(database_id)
    ,   file_id
    ,   Filesize_MB     = total_page_count/128
    ,   Allocated_MB    = allocated_extent_page_count/128
    ,   UnAllocated_MB  = unallocated_extent_page_count/128
    FROM  sys.dm_db_file_space_usage
GO

 

④     Log File 사용률 모니터링

SELECT  DB_Name            = db_name(database_id)
    ,   LogfileSize_MB     = total_log_size_in_bytes/1024/1024
    ,   LogfileUsed_MB     = used_log_space_in_bytes/1024/1024
    ,   LogfileUsed_PCT    = used_log_space_in_percent
  FROM  sys.dm_db_log_space_usage
GO

 

-> 파티션 압축 작업을 진행하게 되면 로그 사용량의 변화는 어떻게 되는지 궁금합니다.

-     전체 인덱스를 한 번에 압축하는 경우와 한 개 파티션 씩 압축하는 경우 모두 거의 유사한 크기의 Transaction Log가 발생하는 것으로 확인됩니다.

인덱스 전체를 한 번에 압축한 경우: 트랜잭션 로그 2,514 MB 증가 한 개 파티션 씩 12회 압축한 경우: 트랜잭션 로그 2,514 MB 증가
BACKUP LOG [OnlineCompressionWithPartitions] TO DISK = 'NUL';
GO
 
-- Log Used: 63 MB
SELECT  DB_Name            = db_name(database_id)
    ,   LogfileSize_MB     = total_log_size_in_bytes/1024/1024
    ,   LogfileUsed_MB     = used_log_space_in_bytes/1024/1024
    ,   LogfileUsed_PCT    = used_log_space_in_percent
  FROM  sys.dm_db_log_space_usage
GO


 
-- Duration: 78 Sec
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = ALL WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
GO
 
-- Log Used: 2,577 MB (2,514 MB increased)
SELECT  DB_Name            = db_name(database_id)
    ,   LogfileSize_MB     = total_log_size_in_bytes/1024/1024
    ,   LogfileUsed_MB     = used_log_space_in_bytes/1024/1024
    ,   LogfileUsed_PCT    = used_log_space_in_percent
  FROM  sys.dm_db_log_space_usage
GO

 BACKUP LOG [OnlineCompressionWithPartitions] TO DISK = 'NUL';
GO
 
-- Log Used: 15 MB
SELECT  DB_Name            = db_name(database_id)
    ,   LogfileSize_MB     = total_log_size_in_bytes/1024/1024
    ,   LogfileUsed_MB     = used_log_space_in_bytes/1024/1024
    ,   LogfileUsed_PCT    = used_log_space_in_percent
  FROM  sys.dm_db_log_space_usage
GO


 
-- Duration: 77 Sec
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 1  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 2  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 3  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 4  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 5  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 6  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 7  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 8  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 9  WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 10 WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 11 WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
ALTER INDEX CPK__Tbl_Partition     ON dbo.Tbl_Partition REBUILD PARTITION = 12 WITH(ONLINE = ON, DATA_COMPRESSION = PAGE, MAXDOP = 4)
 
-- Log Used: 2,529 MB (2,514 MB increased)
SELECT  DB_Name            = db_name(database_id)
    ,   LogfileSize_MB     = total_log_size_in_bytes/1024/1024
    ,   LogfileUsed_MB     = used_log_space_in_bytes/1024/1024
    ,   LogfileUsed_PCT    = used_log_space_in_percent
  FROM  sys.dm_db_log_space_usage
GO

 

'Database > SQL Server' 카테고리의 다른 글

DayLight Saving End(썸머타임 종료)에 따른 NTP 서버 시간 자동 변경  (0) 2025.10.21
Wait Type이 WRITELOG일 때 로그 수집  (0) 2025.09.24
SQL Server 2019 In-memory DTC Transaction이 Rollback  (0) 2024.04.18
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기  (0) 2023.02.14
임의의 쿼리를 HTML 테이블로 바꾸는 동적 프로시저  (0) 2022.10.13

##SQL Version :Microsoft SQL Server 2019 (RTM-CU17) (KB5016394) - 15.0.4249.2 (X64)

 

 

확인사항]

먼저 DTC실패(Error: 8509)기록된후 Rollback하는 과정에서 Dump가 생성으며, 해당 이슈는 이미 알려진 이슈로 CU21에서 FIX가 되었습니다.

해당 Dump는 in-memory Table사용과정에서 발생할수있으며, 고객사같은 경우는 DTC Transaction이 Rollback 되는 과정에서 해당 known issue로 인한 Dump가 생성었고 이로인한 가용성그룹이 Resolving상태로 빠지게됩니다.

이후 Leasetime out이 발생하나 FailureConditionLevel(2)로 Failover가 자동적으로 Triggered되지 않았습니다.

 

#ErrorLog.2
2024-04-02 14:01:03.93 spid62      Error: 8509, Severity: 16, State: 1.
2024-04-02 14:01:03.93 spid62      Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION).
2024-04-02 14:01:35.11 spid156     Error: 8509, Severity: 16, State: 1.
2024-04-02 14:01:35.11 spid156     Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION).
2024-04-02 14:02:01.15 spid853     Error: 8509, Severity: 16, State: 1.
2024-04-02 14:02:01.15 spid853     Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION).
2024-04-02 14:02:35.74 spid35s     CImageHelper::Init () Version-specific dbghelp.dll is not used
2024-04-02 14:02:35.74 spid35s     Using 'dbghelp.dll' version '4.0.5'
2024-04-02 14:02:35.75 spid35s     **Dump thread - spid = 0, EC = 0x0000020BD57C1E10
..중략..
2024-04-02 14:02:35.75 spid35s     ***Stack Dump being sent to \SQLDump0017.txt
2024-04-02 14:02:35.75 spid35s     * *******************************************************************************
2024-04-02 14:02:35.75 spid35s     *
2024-04-02 14:02:35.75 spid35s     * BEGIN STACK DUMP:
2024-04-02 14:02:35.75 spid35s     *   04/02/24 14:02:35 spid 35
2024-04-02 14:02:35.75 spid35s     *
2024-04-02 14:02:35.75 spid35s     * Location:         "sql\\ntdbms\\hekaton\\engine\\core\\tx.cpp":6337
2024-04-02 14:02:35.75 spid35s     * Expression:         Dependencies.CommitDepCountOut >= 1
2024-04-02 14:02:35.75 spid35s     * SPID:                 35
2024-04-02 14:02:35.75 spid35s     * Process ID:         5652
2024-04-02 14:03:23.88 spid35s     Timeout waiting for external dump process 21796.
 
2024-04-02 14:03:23.88 spid35s     Error: 17066, Severity: 16, State: 1.
2024-04-02 14:03:23.88 spid35s     SQL Server Assertion: File: <"sql\\ntdbms\\hekaton\\engine\\core\\tx.cpp">, line=6337 Failed Assertion = 'Dependencies.CommitDepCountOut >= 1'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
..중략..
2024-04-02 14:03:23.88 Server      The state of the local availability replica in availability group 'L_AVG' has changed from 'PRIMARY_NORMAL' to 'RESOLVING_NORMAL'.  The state changed because the lease between the local availability replica and Windows Server Failover Clustering (WSFC) has expired.  For more information, see the SQL Server error log or cluster log.  If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2024-04-02 14:03:23.88 spid189s    The availability group database "DATABASE_P03" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
2024-04-02 14:03:23.88 spid325s    The availability group database " DATABASE_03 _IF" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
2024-04-02 14:03:23.88 spid851s    The availability group database " DATABASE_03_HMI_2" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
2024-04-02 14:03:23.88 spid849s    The availability group database " DATABASE_03_P03" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
 
#ClusterLog
00002c40.00003588::2024/04/02-14:02:40.450 INFO  [RES] SQL Server Availability Group: [hadrag] SQL Server component 'system' health state has been changed from 'clean' to 'warning' at 2024-04-02 14:02:40.447
00002c40.00003588::2024/04/02-14:02:40.453 INFO  [RES] SQL Server Availability Group: [hadrag] SQL Server component 'query_processing' health state has been changed from 'clean' to 'warning' at 2024-04-02 14:02:40.447
00002c40.00000844::2024/04/02-14:02:57.490 ERR   [RES] SQL Server Availability Group <L_AVG>: [hadrag] Lease renewal failed with timeout error
00002c40.00000844::2024/04/02-14:02:57.490 ERR   [RES] SQL Server Availability Group <L_AVG>: [hadrag] The lease is expired. The lease should have been renewed by 2024/04/02-12:02:47.490
00002c40.00003858::2024/04/02-14:02:58.046 ERR   [RES] SQL Server Availability Group <L_AVG>: [hadrag] Availability Group lease is no longer valid
00002c40.00003858::2024/04/02-14:02:58.046 ERR   [RES] SQL Server Availability Group <L_AVG>: [hadrag] Resource Alive result 0.
00002c40.00003858::2024/04/02-14:02:58.046 ERR   [RES] SQL Server Availability Group <L_AVG>: [hadrag] Availability Group lease is no longer valid
00002c40.00003858::2024/04/02-14:02:58.046 ERR   [RES] SQL Server Availability Group <L_AVG>: [hadrag] Resource Alive result 0.
00002c40.00003858::2024/04/02-14:02:58.046 WARN  [RHS] Resource L_AVG IsAlive has indicated failure.
00001cec.00002838::2024/04/02-14:02:58.046 INFO  [RCM] HandleMonitorReply: FAILURENOTIFICATION for 'L_AVG', gen(0) result 1/0.
00002c40.00003858::2024/04/02-14:02:58.046 INFO  [RHS-WER] Scheduling WER ERROR report in 10.000. ReportId 2f1efff4-5677-4f46-8c8c-9162e4c155db;
00001cec.00002838::2024/04/02-14:02:58.046 INFO  [RCM] Res L_AVG: Online -> ProcessingFailure( StateUnknown )
00001cec.00002838::2024/04/02-14:02:58.046 INFO  [RCM] TransitionToState(L_AVG) Online-->ProcessingFailure.

 

해당 이슈가 Failover를 Trigger하는 부차적인 이슈가 있으며, 이로인한 failover를 반복적으로 수행되나 해당 역할을 Online시키지 못함에 따라 반복적으로 dump로 인한 failover가 수행되었습니다.

이과정에서 large transaction rollback및 반복적인 failover시도로인한 DB연결이슈(983)가 발생한 것으로 확인하였습니다.

 

2024/04/02-14:03:23.916 INFO  [RCM] rcm::RcmApi::MoveGroup: (Group:L_AVG Dest:1 Flags:0 MoveType:MoveType::Manual Cur.State:Pending, ContextSize:0)
2024/04/02-14:03:23.917 ERR   [RCM] rcm::RcmApi::OnlineResource: (5023)' because of 'The API call is not valid while resource is in the [Terminating to DelayRestartingResource] state.'


2024-04-02 14:03:23.93 spid725s    Error: 3624, Severity: 20, State: 1.
2024-04-02 14:03:23.93 spid725s    A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.
2024-04-02 14:03:23.93 spid725s    Error: 3314, Severity: 21, State: 3.
2024-04-02 14:03:23.93 spid725s    During undoing of a logged operation in database 'tempdb' (page (1:139) if any), an error occurred at log record ID (17626:408077:603). Typically, the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup, or repair the database.
..중략..
2024-04-02 14:04:22.71 Logon       Error: 983, Severity: 14, State: 1.
2024-04-02 14:04:22.71 Logon       Unable to access availability database 'DATABASE_P03' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.
2024-04-02 14:04:30.07 spid189s    Nonqualified transactions are being rolled back in database DATABASE_P03 for an Always On Availability Groups state change. Estimated rollback completion: 0%. This is an informational message only. No user action is required.
2024-04-02 14:04:32.07 spid189s    Nonqualified transactions are being rolled back in database DATABASE_P03 for an Always On Availability Groups state change. Estimated rollback completion: 0%. This is an informational message only. No user action is required.
2024-04-02 14:04:32.99 spid653s    Remote harden of transaction 'GhostCleanupTask' (ID 0x00000072638fd8cd 0001:3a728b14) started at Apr  2 2024  2:04PM in database 'DATABASE_P03' at LSN (170966:295662:202) failed.
2024-04-02 14:04:33.67 spid325s    Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2024-04-02 14:04:33.67 spid37s     Always On: DebugTraceVarArgs AR '[HADR] [Primary] operation on replicas [5777243E-1C2F-4399-8B8F-8F006F9FD4EE]->[CFF305E1-8C94-4DB3-85D7-0EDD5502E88E], database [DATABASE_P03_IF], remote endpoint [TCP://LES.es.net:5022], source operation [8909B701-63D0-4C65-BE06-CE7BC04DD049]: Transitioning from [CATCHUP] to [COMPLETED].'
2024-04-02 14:04:33.67 spid46s     Always On: DebugTraceVarArgs AR '[HADR] [Primary] operation on replicas [5777243E-1C2F-4399-8B8F-8F006F9FD4EE]->[7ED01BC6-97DF-41C4-84E9-737FE70CB40B], database [DATABASE_P03_IF], remote endpoint [TCP://LES.es.net:5022], source operation [79597AD0-EB94-4E28-ACC6-0E791C5BB0E0]: Transitioning from [CATCHUP] to [COMPLETED].'
2024-04-02 14:04:33.67 spid974s    [DbMgrPartnerCommitPolicy::SetReplicaInfoInAg] DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: CFF305E1-8C94-4DB3-85D7-0EDD5502E88E:10:4
2024-04-02 14:04:34.07 spid189s    Nonqualified transactions are being rolled back in database DATABASE_P03 for an Always On Availability Groups state change. Estimated rollback completion: 0%. This is an informational message only. No user action is required.

 

이후 고객사에서 LES Server재시작(2024-04-02 14:49)이후 정상적으로 'DATABASE_P03' Online되며 AG가 정상적으로 Online되었습니다.

2024-04-02 14:49:51.76 Server      SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
 
2024-04-02 15:00:39.69 spid46s     Always On Availability Groups connection with secondary database established for primary database 'DATABASE_P03' on the availability replica 'LES2' with Replica ID: {cff305e1-8c94-4db3-85d7-0edd5502e88e}. This is an informational message only. No user action is required.
2024-04-02 15:00:39.81 spid139s    Always On Availability Groups connection with secondary database established for primary database 'DATABASE_P03' on the availability replica 'LES2' with Replica ID: {cff305e1-8c94-4db3-85d7-0edd5502e88e}. This is an informational message only. No user action is required.
2024-04-02 15:00:52.81 spid79s     Always On: DebugTraceVarArgs AR '[HADR] [Primary] operation on replicas [5777243E-1C2F-4399-8B8F-8F006F9FD4EE]->[CFF305E1-8C94-4DB3-85D7-0EDD5502E88E], database [DATABASE_P03], remote endpoint [TCP://LES2.es.net:5022], source operation [719D2611-5566-404F-A02F-B08684AE5A07]: Transitioning from [CATCHUP] to [COMPLETED].'
2024-04-02 15:00:57.02 spid46s     [DbMgrPartnerCommitPolicy::SetReplicaInfoInAg] DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: CFF305E1-8C94-4DB3-85D7-0EDD5502E88E:11:4
2024-04-02 15:00:57.02 spid46s     DbMgrPartnerCommitPolicy::SetSyncState: CFF305E1-8C94-4DB3-85D7-0EDD5502E88E:11:4

 

[제안사항]

해당 이슈는 CU21에서 FIX되었으며, 최소 CU21로 patch하시는 것을 제안드립니다.

Cumulative update 21 for SQL Server 2019 (KB5025808) - SQL Server | Microsoft Learn

 

 

다만, 기존 known issue들에 의 한 잠재적인 위험요소가 있기에 가장 마지막 버젼인 CU25로 업그레이드 하시는 것을 권장드립니다.

Download SQL Server® 2019 for Microsoft® Windows Latest Cumulative Update from Official Microsoft Download Center

'Database > SQL Server' 카테고리의 다른 글

Wait Type이 WRITELOG일 때 로그 수집  (0) 2025.09.24
파티션 테이블 압축  (0) 2024.07.04
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기  (0) 2023.02.14
임의의 쿼리를 HTML 테이블로 바꾸는 동적 프로시저  (0) 2022.10.13
FREE: Real-time SQL Server Performance Monitor  (0) 2022.09.14

일반적으로 백업은 DML을 blocking 하지 않으나 백업이 진행중일 때는 database/file을 shrink 할 수 없으며,

Primary에서 Log shrink 를 진행해도 Secondary에도 적용이 되기 때문에 위와 같은 Lock wait이 발생하게 됩니다.

Limitations and restrictions

Ÿ   The database can't be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE. For example, if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.
Ÿ   You can't shrink a database while the database is being backed up. Conversely, you can't back up a database while a shrink operation on the database is in process.
Ÿ   You cannot shrink files while database Backups are happening.
Ÿ   You might also notice that these commands encounter a wait_type = LCK_M_U and a wait_resource = DATABASE: <id> [BULKOP_BACKUP_DB] when the status of these commands is viewed from the various dynamic management views (DMVs), such as from sys.dm_exec_requests or sys.dm_os_waiting_tasks.


Shrink a database - SQL Server | Microsoft Learn

MSSQLSERVER_3023 - SQL Server | Microsoft Learn

'Database > AlwaysOn' 카테고리의 다른 글

NT AUTHORITY\SYSTEM 권한 누락으로 인한 Failover 오류  (0) 2025.09.10
Always-on Availability group(AG) 구성의 SQL Server에서 Primary replica가 병렬 Index Rebuild 수행 시 Secondary도 동일한 수량의 병렬 Index Rebuild를 수행 하는가  (0) 2024.08.22
Alwayson 동기화 지연 원인 파악  (1) 2024.02.14
데이터 동기화 대기 시간에 대한 일반적인 원인 및 문제 해결  (0) 2024.02.14
SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰  (0) 2023.04.05

1. AG 설정 xml 파일 생성

 

아래 쿼리를 Primary, Secondary(Async relica)에서 각각 실행

select
AGNode.group_name
,AGNode.replica_server_name
,AGNode.node_name,ReplicaState.role,ReplicaState.role_desc
,ReplicaState.is_local
,DatabaseState.database_id
,db_name(DatabaseState.database_id) as database_name
,DatabaseState.group_database_id
,DatabaseState.is_commit_participant
,DatabaseState.is_primary_replica
,DatabaseState.synchronization_state_desc
,DatabaseState.synchronization_health_desc
,ClusterState.group_id
,ReplicaState.replica_id
from sys.dm_hadr_availability_replica_cluster_nodes AGNode
join sys.dm_hadr_availability_replica_cluster_states ClusterState
on AGNode.replica_server_name = ClusterState.replica_server_name
join sys.dm_hadr_availability_replica_states ReplicaState
on ReplicaState.replica_id = ClusterState.replica_id
join sys.dm_hadr_database_replica_states DatabaseState
on ReplicaState.replica_id=DatabaseState.replica_id
for XML RAW, ROOT('AGInfoRoot')

 

생성되는 링크 클릭

 

각각의 xml output을 primary.xml , secondary.xml 로 저장하여 전달

 

추가 Extended event 수집

문제가 발생하는 시간대를 특정지을 수는 없다고 하셨지만, 지연이벤트가 집중되는 시간대가 확인되시는 경우 해당 시간대에 수집하여 주시기 바랍니다.

아래 수집 경로 및 파일명도 원하시는 대로 변경하실 수 있으며, 서버 사용량에 맞게 file size 및 rollover file 개수를 조정하시기 바랍니다.

보내드리는 스크립트는 500MB 파일이 4개 생성되는 구조입니다.

 

Secondary의 경우 지연 현상에 대해 확인이 필요한 Async 노드에서만 수집하시기 바랍니다.

-- 1.1. On Primary Replica
CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
ADD EVENT sqlserver.hadr_apply_log_block,
ADD EVENT sqlserver.hadr_capture_filestream_wait,
ADD EVENT sqlserver.hadr_capture_log_block,
ADD EVENT sqlserver.hadr_capture_vlfheader,
ADD EVENT sqlserver.hadr_db_commit_mgr_harden,
ADD EVENT sqlserver.hadr_log_block_compression,
ADD EVENT sqlserver.hadr_log_block_decompression,
ADD EVENT sqlserver.hadr_log_block_group_commit ,
ADD EVENT sqlserver.hadr_log_block_send_complete,
ADD EVENT sqlserver.hadr_lsn_send_complete,
ADD EVENT sqlserver.hadr_receive_harden_lsn_message,
ADD EVENT sqlserver.hadr_send_harden_lsn_message,
ADD EVENT sqlserver.hadr_worker_pool_task,
 
ADD EVENT sqlserver.hadr_database_flow_control_action,
ADD EVENT sqlserver.hadr_transport_flow_control_action, 
ADD EVENT ucs.ucs_connection_flow_control,
 
ADD EVENT sqlserver.hadr_transport_receive_log_block_message,
ADD EVENT sqlserver.log_block_pushed_to_logpool,
ADD EVENT sqlserver.log_flush_complete ,
ADD EVENT sqlserver.recovery_unit_harden_log_timestamps
ADD TARGET package0.event_file(SET filename=N'c:\temp\AlwaysOn_Data_Movement_Tracing.xel',max_file_size=(500),max_rollover_files=(4))
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=OFF)
GO
 
-- 1.2 On Secondary Replica
CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
ADD EVENT sqlserver.hadr_apply_log_block,
ADD EVENT sqlserver.hadr_capture_filestream_wait,
ADD EVENT sqlserver.hadr_capture_log_block,
ADD EVENT sqlserver.hadr_capture_vlfheader,
ADD EVENT sqlserver.hadr_db_commit_mgr_harden,
ADD EVENT sqlserver.hadr_log_block_compression,
ADD EVENT sqlserver.hadr_log_block_decompression,
ADD EVENT sqlserver.hadr_log_block_group_commit ,
ADD EVENT sqlserver.hadr_log_block_send_complete,
ADD EVENT sqlserver.hadr_lsn_send_complete,
ADD EVENT sqlserver.hadr_receive_harden_lsn_message,
ADD EVENT sqlserver.hadr_send_harden_lsn_message,
ADD EVENT sqlserver.hadr_undo_of_redo_log_scan,
 
ADD EVENT sqlserver.hadr_database_flow_control_action,
ADD EVENT sqlserver.hadr_transport_flow_control_action, 
ADD EVENT ucs.ucs_connection_flow_control,
 
ADD EVENT sqlserver.hadr_transport_receive_log_block_message,
ADD EVENT sqlserver.log_block_pushed_to_logpool,
ADD EVENT sqlserver.log_flush_complete ,
ADD EVENT sqlserver.recovery_unit_harden_log_timestamps
ADD TARGET package0.event_file(SET filename=N'c:\temp\AlwaysOn_Data_Movement_Tracing.xel',max_file_size=(500),max_rollover_files=(4))
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=OFF)
GO
 
 
--2. Start Session
ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER STATE=START;
 
 
--3. Stop Session
ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER STATE=STOP;

 

분석에 필요한 event가 SQL Server 2014 SP2 / 2016 SP1 이후 추가되었기 때문에 이전 버전에서는 해당 이벤트 수집이 어려운 점, 참고 부탁드립니다.

KB3173156 - Update adds AlwaysOn extended events and performance counters in SQL Server 2014 or 2016 - Microsoft Support

 

Perfmon 수집

1) cmd를 관리자권한으로 실행한 뒤 아래 명령을 실행하면 성능 수집기가 생성됩니다.

  • C:\sqlperflogs 경로에 파일이 생성되고 5초 간격으로 카운터를 수집하도록 설정되어 있습니다. 옵션을 고객사 환경에 맞게 적절하게 변경하셔도 관계 없습니다.
  • M/d/yyyy 를 다음날로 설정하시면 다음날 00시부터 수집이 되며, 24시간(23시간 59분 59초) 간격으로 새로운 파일 생성이 됩니다.
  • 만약 날짜 상관 없이 수집을 바로 시작하고 24시간 간격으로 수집을 원하실 경우 아래, 명령어를 변경하신 후 start 해 주시기 바랍니다.
  • 로그를 수집한 후 파일 사이즈를 확인하고 문제가 재현되지 않을 경우 디스크 공간을 많이 차지하지 않도록 주기적으로 로그 파일을 수동 제거할 필요가 있습니다.

 

DEFAULT INSTANCE

Logman.exe create counter SQLPerfLog -o "c:\sqlperflogs\PerfLog.csv" -f csv -v mmddhhmm -b M/d/yyyy 12:00:00AM -r -rf 23:59:59 -si 00:00:05   -c "\PhysicalDisk(*)\*" "\LogicalDisk(*)\*" "\Memory\*" "\Processor(_Total)\*" "\Process(*)\*" "\Network Interface(*)\*" "\Paging File(*)\*" "\System\*" "\SQLServer:Access Methods\*" "\SQLServer:Backup Device\*" "\SQLServer:Broker Activation\*" "\SQLServer:Broker Statistics\*" "\SQLServer:Broker TO Statistics\*" "\SQLServer:Broker/DBM Transport\*" "\SQLServer:Buffer Manager\*" "\SQLServer:Buffer Node(*)\*" "\SQLServer:Catalog Metadata(*)\*" "\SQLServer:CLR\*" "\SQLServer:Cursor Manager by Type(*)\*" "\SQLServer:Cursor Manager Total\*" "\SQLServer:Database Mirroring\*" "\SQLServer:Database Replica(*)\*" "\SQLServer:Databases(*)\*" "\SQLServer:Deprecated Features(*)\*" "\SQLServer:Exec Statistics(*)\*" "\SQLServer:General Statistics\*" "\SQLServer:Latches\*" "\SQLServer:Locks(*)\*" "\SQLServer:Memory Manager\*" "\SQLServer:Plan Cache(*)\*" "\SQLServer:Replication Agents\*" "\SQLServer:Replication Dist.\*" "\SQLServer:Replication Logreader\*" "\SQLServer:Replication Merge\*" "\SQLServer:Replication Snapshot\*" "\SQLServer:Resource Pool Stats(*)\*" "\SQLServer:SQL Errors(*)\*" "\SQLServer:SQL Statistics\*"  "\SQLServer:Transactions\*" "\SQLServer:User Settable(*)\*" "\SQLServer:Wait Statistics(*)\*" "\SQLServer:Workload Group Stats(*)\*"

 

NAMED INSTANCE
“MSSQL$NAMEDINS”를 고객사 NAMED INSTANCE이름으로 변경이 필요합니다.
Ex) MSSQL$NAMEDINS à MSSQL$DB2

Logman.exe create counter SQLPerfLog -o "c:\sqlperflogs\PerfLog.csv" -f csv -v mmddhhmm -b M/d/yyyy 12:00:00AM  -r -rf 23:59:59 -si 00:00:05  -c  "\PhysicalDisk(*)\*" "\LogicalDisk(*)\*" "\Memory\*" "\Processor(_Total)\*" "\Process(*)\*" "\Network Interface(*)\*" "\Paging File(*)\*" "\System\*" "\MSSQL$NAMEDINS:Backup Device\*" "\MSSQL$NAMEDINS:Broker Activation\*" "\MSSQL$NAMEDINS:Broker Statistics\*" "\MSSQL$NAMEDINS:Broker TO Statistics\*" "\MSSQL$NAMEDINS:Broker/DBM Transport\*" "\MSSQL$NAMEDINS:Buffer Manager\*" "\MSSQL$NAMEDINS:Buffer Node(*)\*" "\MSSQL$NAMEDINS:Catalog Metadata(*)\*" "\MSSQL$NAMEDINS:CLR\*" "\MSSQL$NAMEDINS:Cursor Manager by Type(*)\*" "\MSSQL$NAMEDINS:Cursor Manager Total\*" "\MSSQL$NAMEDINS:Database Mirroring\*" "\MSSQL$NAMEDINS:Database Replica(*)\*" "\MSSQL$NAMEDINS:Databases(*)\*" "\MSSQL$NAMEDINS:Deprecated Features(*)\*" "\MSSQL$NAMEDINS:Exec Statistics(*)\*" "\MSSQL$NAMEDINS:General Statistics\*" "\MSSQL$NAMEDINS:Latches\*" "\MSSQL$NAMEDINS:Locks(*)\*" "\MSSQL$NAMEDINS:Memory Manager\*" "\MSSQL$NAMEDINS:Plan Cache(*)\*" "\MSSQL$NAMEDINS:Replication Agents\*" "\MSSQL$NAMEDINS:Replication Dist.\*" "\MSSQL$NAMEDINS:Replication Logreader\*" "\MSSQL$NAMEDINS:Replication Merge\*" "\MSSQL$NAMEDINS:Replication Snapshot\*" "\MSSQL$NAMEDINS:Resource Pool Stats(*)\*" "\MSSQL$NAMEDINS:SQL Errors(*)\*" "\MSSQL$NAMEDINS:SQL Statistics\*"  "\MSSQL$NAMEDINS:Transactions\*" "\MSSQL$NAMEDINS:User Settable(*)\*" "\MSSQL$NAMEDINS:Wait Statistics(*)\*" "\MSSQL$NAMEDINS:Workload Group Stats(*)\*" "\SQLAgent$NAMEDINS:Alerts\*" "\SQLAgent$NAMEDINS:Jobs(*)\*" "\SQLAgent$NAMEDINS:JobSteps(*)\*"

 

 

-- Database Mirroring 항목 추가

 

DEFAULT INSTANCE

Logman.exe update counter [수집중인Perflog명]  -c "\SQLServer:Availability Replica\*" "\PhysicalDisk(*)\*" "\LogicalDisk(*)\*" "\Memory\*" "\Processor(_Total)\*" "\Process(*)\*" "\Network Interface(*)\*" "\Paging File(*)\*" "\System\*" "\SQLServer:Access Methods\*" "\SQLServer:Backup Device\*" "\SQLServer:Broker Activation\*" "\SQLServer:Broker Statistics\*" "\SQLServer:Broker TO Statistics\*" "\SQLServer:Broker/DBM Transport\*" "\SQLServer:Buffer Manager\*" "\SQLServer:Buffer Node(*)\*" "\SQLServer:Catalog Metadata(*)\*" "\SQLServer:CLR\*" "\SQLServer:Cursor Manager by Type(*)\*" "\SQLServer:Cursor Manager Total\*" "\SQLServer:Database Mirroring\*" "\SQLServer:Database Replica(*)\*" "\SQLServer:Databases(*)\*" "\SQLServer:Deprecated Features(*)\*" "\SQLServer:Exec Statistics(*)\*" "\SQLServer:General Statistics\*" "\SQLServer:Latches\*" "\SQLServer:Locks(*)\*" "\SQLServer:Memory Manager\*" "\SQLServer:Plan Cache(*)\*" "\SQLServer:Replication Agents\*" "\SQLServer:Replication Dist.\*" "\SQLServer:Replication Logreader\*" "\SQLServer:Replication Merge\*" "\SQLServer:Replication Snapshot\*" "\SQLServer:Resource Pool Stats(*)\*" "\SQLServer:SQL Errors(*)\*" "\SQLServer:SQL Statistics\*"  "\SQLServer:Transactions\*" "\SQLServer:User Settable(*)\*" "\SQLServer:Wait Statistics(*)\*" "\SQLServer:Workload Group Stats(*)\*"

 

NAMED INSTANCE

“MSSQL$NAMEDINS”를 고객사 NAMED INSTANCE이름으로 변경이 필요합니다.

Ex) MSSQL$NAMEDINS à MSSQL$DB2

Logman.exe update counter [수집중인Perflog명]  -c "\MSSQL$NAMEDINS:Availability Replica\*"   "\PhysicalDisk(*)\*" "\LogicalDisk(*)\*" "\Memory\*" "\Processor(_Total)\*" "\Process(*)\*" "\Network Interface(*)\*" "\Paging File(*)\*" "\System\*" "\MSSQL$NAMEDINS:Backup Device\*" "\MSSQL$NAMEDINS:Broker Activation\*" "\MSSQL$NAMEDINS:Broker Statistics\*" "\MSSQL$NAMEDINS:Broker TO Statistics\*" "\MSSQL$NAMEDINS:Broker/DBM Transport\*" "\MSSQL$NAMEDINS:Buffer Manager\*" "\MSSQL$NAMEDINS:Buffer Node(*)\*" "\MSSQL$NAMEDINS:Catalog Metadata(*)\*" "\MSSQL$NAMEDINS:CLR\*" "\MSSQL$NAMEDINS:Cursor Manager by Type(*)\*" "\MSSQL$NAMEDINS:Cursor Manager Total\*" "\MSSQL$NAMEDINS:Database Mirroring\*" "\MSSQL$NAMEDINS:Database Replica(*)\*" "\MSSQL$NAMEDINS:Databases(*)\*" "\MSSQL$NAMEDINS:Deprecated Features(*)\*" "\MSSQL$NAMEDINS:Exec Statistics(*)\*" "\MSSQL$NAMEDINS:General Statistics\*" "\MSSQL$NAMEDINS:Latches\*" "\MSSQL$NAMEDINS:Locks(*)\*" "\MSSQL$NAMEDINS:Memory Manager\*" "\MSSQL$NAMEDINS:Plan Cache(*)\*" "\MSSQL$NAMEDINS:Replication Agents\*" "\MSSQL$NAMEDINS:Replication Dist.\*" "\MSSQL$NAMEDINS:Replication Logreader\*" "\MSSQL$NAMEDINS:Replication Merge\*" "\MSSQL$NAMEDINS:Replication Snapshot\*" "\MSSQL$NAMEDINS:Resource Pool Stats(*)\*" "\MSSQL$NAMEDINS:SQL Errors(*)\*" "\MSSQL$NAMEDINS:SQL Statistics\*"  "\MSSQL$NAMEDINS:Transactions\*" "\MSSQL$NAMEDINS:User Settable(*)\*" "\MSSQL$NAMEDINS:Wait Statistics(*)\*" "\MSSQL$NAMEDINS:Workload Group Stats(*)\*" "\SQLAgent$NAMEDINS:Alerts\*" "\SQLAgent$NAMEDINS:Jobs(*)\*" "\SQLAgent$NAMEDINS:JobSteps(*)\*"

 

2) 성능 수집을 중지하려면 아래 명령어를 입력합니다.

Logman.exe stop SQLPerfLog

 

파라미터 변경이 필요하신 경우 아래 링크 참고 부탁 드립니다. 

logman create counter | Microsoft Learn

 

위 logman 명령은 perfmon (성능 모니터)에서 로그 수집기의 중지 조건이나 스케줄을 GUI로도 편집하실 수 있습니다.

 

 

 

(optional) lsn 정보 수집

아래 내용을 수집 기간 동안 분 단위로 수집하여 주시면 분석에 도움이 될 수 있습니다.

다만 서버에 부하가 갈 수 있을 경우 수집하지 않으셔도 됩니다.

select GETDATE(), database_id, recovery_lsn, truncation_lsn, last_hardened_lsn, last_sent_lsn, last_received_lsn,  
   last_redone_lsn, last_sent_time, last_received_time, last_redone_time 
from sys.dm_hadr_database_replica_states

 

'Database > AlwaysOn' 카테고리의 다른 글

Always-on Availability group(AG) 구성의 SQL Server에서 Primary replica가 병렬 Index Rebuild 수행 시 Secondary도 동일한 수량의 병렬 Index Rebuild를 수행 하는가  (0) 2024.08.22
Log shrink 동기화 지연  (0) 2024.02.14
데이터 동기화 대기 시간에 대한 일반적인 원인 및 문제 해결  (0) 2024.02.14
SQL Server Always On 가용성 그룹에 대한 가용성 복제본 및 데이터베이스를 모니터링하기 위한 동적 관리뷰  (0) 2023.04.05
SQL Server AlwaysOn synchronous-commit 환경에서 동기화 레이턴시 트러블슈팅  (0) 2020.08.27

+ Recent posts

티스토리툴바