Alwayson 동기화 지연 원인 파악

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