Wait Type이 WRITELOG일 때 로그 수집

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