통계 자동 갱신 조건과 별개로 옵티마이저 판단 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등의 요소에 의해 발생할 수 있습니다.
해당 시간 전환 전/후로 아래와 같이 쿼리하여 현재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%';
WRITELOG대기 원인 분석을 위해 성능 모니터와 세션 정보 수집을 권장 드립니다. 성능 카운터는 전반적인 시스템 상태를 확인하는 데 유용하며,세션 정보는 어떤 쿼리나 트랜잭션이WRITELOG대기를 발생시키는지 파악하고 세부 분석하는 데 도움이 됩니다. 수집 방법은 아래와 같습니다.
1. SQL성능 카운터 수집
Step1 cmd를 관리자권한으로 실행한 뒤 아래 명령을 실행하면 성능 수집기가 생성됩니다. 1초 주기로 최대 1024MB까지 수집되며,수집 경로는 환경에 맞게 수정해 주시기 바랍니다. WRITELOG대기 발생 시점을 알고 있는 경우,해당 짧은 기간 동안1초 간격으로 수집할 것을 권장 드립니다. 발생 시점을 알 수 없는 경우에는10~15초 간격으로 하루 정도 수집하는 것을 권장 드립니다.
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
