보미네

RECOMPILE의 비용은 실행 빈도에 비례하고, 이득은 파라미터 값의
편차에 비례합니다. OLTP는 앞항이 크고 뒷항이 작아 금기이지만,
관리자 전용 조회는 정확히 그 반대이므로 정석에 가깝습니다.


────────────────────────────────────────────
1. 세 가지 추정 모드
────────────────────────────────────────────

옵티마이저가 WHERE col = @p 의 행 수를 추정하는 경로는 셋뿐입니다.

 (1) 스니핑 ON (기본값)
     - 추정 근거 : 최초 컴파일 시 전달된 값 → 히스토그램
     - 플랜 확정 : 최초 1회
     - 캐시      : 함

 (2) 스니핑 OFF
     - 추정 근거 : 평균 밀도 → density vector
                   (부등호는 고정 추정치: >, < 는 30%, BETWEEN 은 9%)
     - 플랜 확정 : 최초 1회
     - 캐시      : 함

 (3) OPTION (RECOMPILE)
     - 추정 근거 : 매 실행 시점의 실제 값 → 히스토그램
     - 플랜 확정 : 매 실행
     - 캐시      : 안 함

앞의 둘은 "언제 정한 값을 계속 쓰느냐"의 차이일 뿐,
하나의 플랜을 모든 값에 재사용한다는 점에서는 동일합니다.
세 번째만 이 전제를 깹니다.


────────────────────────────────────────────
2. RECOMPILE 은 스니핑을 끄는 것이 아닙니다
────────────────────────────────────────────

흔한 오해와 달리 RECOMPILE 은 스니핑을 무시하지 않습니다.
캐시에 박제된 과거의 스니핑 값을 버리고, 지금 값으로 다시
스니핑하는 것입니다. 옵티마이저 관점에서는 가장 강한 형태의
스니핑입니다.

이 동작의 정식 명칭은 PEO (Parameter Embedding Optimization)
이며, 런타임 값을 파라미터가 아닌 리터럴 상수로 쿼리 트리에
삽입한 뒤 최적화합니다. 그래서 RECOMPILE 에서만 가능한
최적화가 생깁니다.

 - 로컬 변수도 스니핑 대상이 됨 (평소에는 불가능, 5번 참조)
 - 모순 감지 : CHECK 제약이나 파티션 범위와 충돌하는 조건의
   서브트리를 통째로 제거
 - 조건 폴딩 : @p IS NULL OR col = @p 같은 catch-all 패턴에서
   사용되지 않는 가지를 제거


────────────────────────────────────────────
3. 실제 손실은 CPU 가 아니라 메모리 그랜트
────────────────────────────────────────────

Sort / Hash 연산자의 메모리 그랜트는 컴파일 시점의
"추정 행 수 x 추정 행 크기" 로 결정되며, 실행 중에는
증가하지 않습니다. 추정이 어긋나면 양방향 모두 손해입니다.

 - 과소 추정 → tempdb 스필.
   페이징에서 TOP N 뒤에 Sort 가 붙으면 직격탄입니다.

 - 과대 추정 → 사용하지 않을 메모리를 예약.
   RESOURCE_SEMAPHORE 대기로 무관한 세션까지 굶깁니다.

두 번째가 더 위험합니다. 잘못된 플랜은 해당 쿼리만 느리게
하지만, 과대 그랜트는 인스턴스 전체에 영향을 줍니다.


────────────────────────────────────────────
4. OLTP 금기 사유와 그 무력화
────────────────────────────────────────────

 (1) 컴파일 CPU
     - 내용 : 단순 쿼리 1~5ms, 복잡한 조인은 수십 ms
     - 관리자 모드 : 호출 QPS 가 사실상 한 자릿수이므로
       총비용 무시 가능

 (2) 관측 가능성 상실
     - 내용 : sys.dm_exec_query_stats 에 누적되지 않아
       슬로우 쿼리 집계에서 누락
     - 관리자 모드 : 호출 건수가 적어 개별 추적이 오히려 용이.
       Query Store 로도 보완 가능

 (3) 동시 컴파일 경합
     - 내용 : 동일 쿼리 동시 유입 시 컴파일이 직렬화 지점
     - 관리자 모드 : 동시성 없음

반면 얻는 것은 큽니다. 관리자 조회는 조건 조합에 따라 대상
건수가 수십 건에서 수백만 건까지 벌어집니다. 이 편차 폭 자체가
단일 플랜으로는 커버 불가능하다는 근거입니다.


────────────────────────────────────────────
5. SSMS 로는 스니핑을 테스트할 수 없습니다
────────────────────────────────────────────

가장 실무적인 함정입니다.

SSMS 에서 DECLARE 로 변수를 선언해 실행하면 파라미터 스니핑은
발생하지 않습니다. 로컬 변수는 컴파일 시점에 값이 없는 것으로
취급되어 density vector 기반 추정을 받습니다.
즉 OPTIMIZE FOR UNKNOWN 과 동등합니다.

여기서 비직관적인 조합이 나옵니다.

  DECLARE @id INT = 12345;

  -- 스니핑 없음 (density)
  SELECT ... WHERE member_id = @id;

  -- 리터럴과 동일 (히스토그램)
  SELECT ... WHERE member_id = @id OPTION (RECOMPILE);

  -- 위와 동일한 플랜
  SELECT ... WHERE member_id = 12345;

같은 변수인데 RECOMPILE 유무로 플랜이 갈립니다.
PEO 가 로컬 변수까지 상수로 치환하기 때문입니다.

애플리케이션의 실제 실행 경로를 재현하려면 다음 중 하나를
사용해야 합니다.

  -- (1) sp_executesql : ADO.NET / JDBC 파라미터 바인딩과 동일 경로
  EXEC sp_executesql
       N'SELECT ... WHERE member_id = @id',
       N'@id INT',
       @id = 12345;

  -- (2) sp_prepexec : RPC 레벨 prepared statement 재현
  -- (3) 저장 프로시저로 감싸서 호출


────────────────────────────────────────────
6. 검증 : 실행 플랜 XML 의 <ParameterList>
────────────────────────────────────────────

 - ParameterCompiledValue 가 존재
   → 스니핑 발생. 이 값 기준으로 플랜이 생성됨

 - ParameterCompiledValue 가 없음
   → 로컬 변수. 스니핑 안 됨. 테스트 무효

 - ParameterCompiledValue != ParameterRuntimeValue
   → 파라미터 스니핑 문제 발생 중

세 번째가 원인 규명의 결정타입니다. 컴파일 값과 런타임 값이
다르다는 것은, 지금 이 실행이 다른 값에 맞춰진 플랜을 빌려
쓰고 있다는 직접 증거입니다.


────────────────────────────────────────────
7. 적용 판단 체크리스트
────────────────────────────────────────────

RECOMPILE 적용 전 확인 사항입니다.

  1) 파라미터 값에 따라 대상 건수가 자릿수 단위로 달라지는가
  2) 해당 쿼리의 실행 빈도가 낮은가 (분당 수십 회 이하 수준)
  3) 플랜에 메모리 그랜트를 요구하는 연산자
     (Sort, Hash Match) 가 있는가
  4) ParameterCompiledValue != ParameterRuntimeValue 가
     실측으로 확인되었는가

1, 2 번이 함께 참이면 적용 근거가 성립하고,
3, 4 번이 그 근거를 실측으로 뒷받침합니다.

반대로 2 번이 거짓이면 다른 대안 - 플랜 가이드,
OPTIMIZE FOR 대표값 고정, 쿼리 분기 - 를 먼저 검토해야 합니다.

한 줄 요약: SSMS 20부터 암호화 기본값이 Mandatory(필수) 로 바뀌면서, SSMS가 서버 인증서를 검증하기 시작했습니다. 신뢰된 인증서가 없으면 접속이 막히고, 이때 "Trust Server Certificate"를 체크하면 검증만 건너뛰고 접속됩니다.

배경

• SSMS 19 이전에는 대부분 비암호화로 연결됐지만, SSMS 20부터 기본값이 Mandatory로 변경.
• SSMS 19부터 드라이버가 교체되어 인증서 검증을 본격 수행. (Azure Data Studio, ODBC 18 등 Microsoft 전반의 보안 강화 흐름)
• 서버는 그대로인데 클라이언트가 엄격해진 것.

왜 오류가 나나

서버에 신뢰된 CA 서명 인증서가 없으면(자체 서명 등):

The certificate chain was issued by an authority that is not trusted.
(Microsoft SQL Server, Error: -2146893019)

연결은 됐지만 로그인 단계의 인증서 검증에서 실패한 것.

Trust Server Certificate의 진짜 의미

• 암호화(TLS) 사용 여부가 아님.
• "상대 서버가 신뢰할 수 있는지 검증할지" 여부.
• 체크 = 검증만 건너뛰고 암호화는 그대로 유지.

대응

1. 임시 우회: Trust Server Certificate 체크 → 즉시 접속되나 MITM 검증을 포기(권장 X).
2. Optional로 낮추기: 인증서 안 믿어도 실패 안 함(보안↓).
3. 근본 해결(권장): 서버에 신뢰된 CA 인증서 설치 + 클라이언트 루트 CA 저장소에 등록 → 체크 없이 Mandatory로 정상 접속.

참고

• Strict(SQL 2022 / Azure SQL): Trust Server Certificate 비활성화, 인증서 검증 필수.
• SSMS 19/18 설정을 가져오면 기본값 변경 때문에 기존 연결이 안 될 수 있음.

USE MASTER ;
GO 

IF OBJECT_ID('dbo.usp_query_check') IS NOT NULL
    DROP PROCEDURE dbo.usp_query_check;
GO
CREATE PROCEDURE dbo.usp_query_check
    @keywords   NVARCHAR(MAX) = NULL,          -- 콤마로 구분된 검색 문자 (예: N'birthMonthDate,CFT_MARKET_ITEM_OTN')
    @action     NVARCHAR(10)  = N'start',       -- 'start' = 삭제 후 재생성 + 시작, 'stop' = 정지
    @filepath   NVARCHAR(260) = N'D:\DBA\XERESULT\querycheck.xel'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql            NVARCHAR(MAX);
    DECLARE @sessionName    SYSNAME = N'querycheck';

    ---------------------------------------------------------------
    -- STOP 처리
    ---------------------------------------------------------------
    IF (LOWER(@action) = N'stop')
    BEGIN
        IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name = @sessionName)
        BEGIN
            SET @sql = N'ALTER EVENT SESSION ' + QUOTENAME(@sessionName)
                     + N' ON SERVER STATE = STOP;';
            EXEC sp_executesql @sql;
            PRINT N'Event session [' + @sessionName + N'] stopped.';
        END
        ELSE
            PRINT N'Event session [' + @sessionName + N'] is not running.';
        RETURN;
    END

    ---------------------------------------------------------------
    -- START 처리 : 검색 문자 필수
    ---------------------------------------------------------------
    IF (@keywords IS NULL OR LTRIM(RTRIM(@keywords)) = N'')
    BEGIN
        RAISERROR(N'@keywords is required for start action (comma separated).', 16, 1);
        RETURN;
    END

    ---------------------------------------------------------------
    -- 콤마 분리 후 컬럼별 WHERE 절 동적 생성
    -- (statement 대상용 / batch_text 대상용 각각)
    ---------------------------------------------------------------
    DECLARE @whereStmt  NVARCHAR(MAX) = N'';
    DECLARE @whereBatch NVARCHAR(MAX) = N'';

    ;WITH parts AS (
        SELECT LTRIM(RTRIM(value)) AS kw
        FROM STRING_SPLIT(@keywords, N',')
        WHERE LTRIM(RTRIM(value)) <> N''
    )
    SELECT
        @whereStmt = STRING_AGG(
            N'[sqlserver].[like_i_sql_unicode_string]([statement],N''%'
            + REPLACE(kw, N'''', N'''''') + N'%'')', N' OR '),
        @whereBatch = STRING_AGG(
            N'[sqlserver].[like_i_sql_unicode_string]([batch_text],N''%'
            + REPLACE(kw, N'''', N'''''') + N'%'')', N' OR ')
    FROM parts;

    IF (@whereStmt IS NULL OR @whereStmt = N'')
    BEGIN
        RAISERROR(N'No valid keywords parsed from @keywords.', 16, 1);
        RETURN;
    END

    ---------------------------------------------------------------
    -- 기존 세션 삭제
    ---------------------------------------------------------------
    IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = @sessionName)
    BEGIN
        SET @sql = N'DROP EVENT SESSION ' + QUOTENAME(@sessionName) + N' ON SERVER;';
        EXEC sp_executesql @sql;
    END

    ---------------------------------------------------------------
    -- 세션 생성
    ---------------------------------------------------------------
    SET @sql =
        N'CREATE EVENT SESSION ' + QUOTENAME(@sessionName) + N' ON SERVER ' + NCHAR(13) +
        N'ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)' + NCHAR(13) +
        N'    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.username)' + NCHAR(13) +
        N'    WHERE (' + @whereStmt + N')),' + NCHAR(13) +
        N'ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)' + NCHAR(13) +
        N'    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_nt_username,sqlserver.username)' + NCHAR(13) +
        N'    WHERE (' + @whereStmt + N')),' + NCHAR(13) +
        N'ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)' + NCHAR(13) +
        N'    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.username)' + NCHAR(13) +
        N'    WHERE (' + @whereBatch + N'))' + NCHAR(13) +
        N'ADD TARGET package0.event_file(SET filename=N''' + REPLACE(@filepath, N'''', N'''''') + N''',max_file_size=(100))' + NCHAR(13) +
        N'WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);';

    EXEC sp_executesql @sql;

    ---------------------------------------------------------------
    -- 시작
    ---------------------------------------------------------------
    SET @sql = N'ALTER EVENT SESSION ' + QUOTENAME(@sessionName)
             + N' ON SERVER STATE = START;';
    EXEC sp_executesql @sql;

    PRINT N'Event session [' + @sessionName + N'] (re)created and started.';
    PRINT N'Keywords: ' + @keywords;
END
GO

EXEC dbo.usp_query_check
     @keywords = N'birthMonthDate, CFT_MARKET_ITEM_OTN',
     @action   = N'start',
     @filepath = N'D:\DBA\XERESULT\querycheck.xel';


EXEC dbo.usp_query_check @action = N'stop';

CREATE EVENT SESSION [querycheck] ON SERVER 
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_nt_username,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'%MonthDates%') OR [sqlserver].[like_i_sql_unicode_string]([statement],N'%ITEM_OTN%'))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'%MonthDates%') OR [sqlserver].[like_i_sql_unicode_string]([statement],N'%ITEM_OTN%'))),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([batch_text],N'%MonthDates%') OR [sqlserver].[like_i_sql_unicode_string]([batch_text],N'%ITEM_OTN%')))
ADD TARGET package0.event_file(SET filename=N'D:\DBA\XERESULT\querycheck.xel',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO