MSSQL 실행 계획이 재생성 되는 경우

통계 자동 갱신 조건과 별개로 옵티마이저 판단
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;