쿼리 저장소를 사용하여 최적화된 계획 강제 실행

적용 대상: SQL Server 2022 (16.x) Azure SQL Database

쿼리 최적화는 “충분한” 쿼리 실행 계획을 생성하는 다단계 프로세스입니다. 경우에 따라 쿼리 최적화의 일부인 쿼리 컴파일은 전체 쿼리 실행 시간의 큰 비율을 나타내고 상당한 시스템 리소스를 사용할 수 있습니다. 최적화된 계획 강제 실행은 지능형 쿼리 처리 기능 제품군의 일부입니다. 최적화된 계획 강제 적용은 강제 쿼리를 반복하기 위한 컴파일 오버헤드를 줄이고 쿼리 저장소 "읽기 쓰기" 모드에서 사용하도록 설정해야 합니다. 쿼리 실행 계획이 생성되면 최적화 재생 스크립트로 다시 사용할 수 있는 특정 컴파일 단계가 저장됩니다. 최적화 재생 스크립트는 쿼리 저장소의 압축된 실행 계획 XML의 일부로 숨겨진 OptimizationReplay 특성에 저장됩니다.

최적화된 계획 강제 실행 구현

쿼리가 컴파일 프로세스를 처음 통과하는 경우 최적화에 소요된 시간(쿼리 최적화 프로그램 입력 트리 기반)에 따른 임계값은 최적화 재생 스크립트가 생성되는지 여부를 결정합니다.

컴파일이 완료되면 이전 예측이 올바른지 여부를 평가하기 위해 여러 런타임 메트릭을 사용할 수 있게 됩니다. 데이터베이스 엔진 임계값을 초과했음을 확인하면 최적화 재생 스크립트가 지속성을 사용할 수 있습니다. 이러한 런타임 메트릭에는 액세스하는 개체 수, 조인 수, 최적화 중에 실행된 최적화 작업 수, 실제 최적화 시간이 포함됩니다.

최적화 재생 스크립트를 사용할 경우의 잠재적 이점은 최적화 재생 스크립트를 저장하는 오버헤드와도 비교됩니다. 최적화 재생 스크립트를 재생하기 위한 상대 시간의 추정은 일반 최적화 프로세스를 실행하는 데 소요된 시간과 비교됩니다. 이 예상치는 최적화 재생 스크립트에 저장된 최적화 작업의 수와 일반 컴파일 중에 실행된 최적화 작업의 수를 기반으로 합니다. 최적화 재생 스크립트를 재생하여 컴파일 시간을 줄이는 데 상당한 이점이 있는 경우 최적화 재생 스크립트가 유지됩니다.

고려 사항

최적화된 계획 강제 적용 기능을 사용하도록 설정하는 경우 최적화된 계획 강제 적용에 대한 자격 기준은 다음과 같습니다.

1. 전체 최적화를 거치는 쿼리 계획만 적격하며 StatementOptmLevel="FULL" 속성이 있으면 확인할 수 있습니다.

2. RECOMPILE 힌트 및 분산 쿼리가 있는 문은 적합하지 않습니다.

그러나 쿼리 저장소 최적화된 계획 강제 적용으로 범위가 지정된 쿼리 계획을 독립적으로 캡처하는 경우 기본 다시 컴파일 이벤트에 따라 동일한 쿼리의 두 번째 다시 컴파일을 위해 최적화 재생 스크립트가 만들어집니다. 실행 계획 다시 컴파일의 다시 컴파일에 대해 자세히 알아봅니다.

최적화 재생 스크립트가 생성된 경우에도 쿼리 저장소 구성된 캡처 정책 조건, 특히 해당 문의 실행 수와 누적 컴파일 및 실행 시간이 충족되지 않으면 쿼리 저장소 유지되지 않을 수 있습니다. 이 경우 잘못된 최적화 재생 스크립트가 메모리에서 비동기적으로 제거됩니다.

최적화된 계획 강제 적용 사용 및 사용 안 함

데이터베이스에 대해 최적화된 계획 강제 적용을 사용하거나 사용하지 않도록 설정할 수 있습니다. 데이터베이스에 대해 최적화된 계획 강제 적용을 사용하도록 설정한 경우 쿼리 힌트를 사용하여 DISABLE_OPTIMIZED_PLAN_FORCING 개별 쿼리에 대해 사용하지 않도록 설정할 수 있습니다. 쿼리 저장소 강제로 적용된 쿼리 계획에 대해 최적화된 계획 강제 적용을 사용하지 않도록 설정할 수도 있습니다.

데이터베이스에 최적화된 계획 강제 적용 사용 또는 사용 안 함

최적화된 계획 강제 적용은 기본적으로 SQL Server 2022(16.x) 이상에서 만든 새 데이터베이스에 대해 사용하도록 설정됩니다. 최적화된 계획 강제 적용이 사용되는 모든 데이터베이스에 대해 쿼리 저장소를 사용하도록 설정해야 합니다. 기존 데이터베이스가 있는 업그레이드된 인스턴스 또는 하위 버전의 SQL Server에서 복원된 데이터베이스에는 기본적으로 사용하도록 설정된 최적화된 계획이 있습니다.

데이터베이스 수준에서 최적화된 계획 강제 적용을 사용하도록 설정하려면 ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON 데이터베이스 범위 구성을 사용합니다. 쿼리 저장소가 아직 사용하도록 설정되어 있지 않은 경우 사용하도록 설정해야 합니다. 예제 A에서 예제 코드를 찾거나 쿼리 저장소를 사용하여 성능 모니터링에서 쿼리 저장소에 대해 자세히 알아보세요.

데이터베이스 수준에서 최적화된 계획 강제 적용을 사용하지 않도록 설정하려면 ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF 데이터베이스 범위 구성을 사용합니다.

쿼리 힌트를 사용하여 최적화된 계획 강제 적용 사용 안 함

데이터베이스에서 최적화된 계획 강제 적용 기능을 사용하도록 설정한 경우 DISABLE_OPTIMIZED_PLAN_FORCING 쿼리 힌트를 사용하여 개별 쿼리에 대해 최적화된 계획 강제 적용을 사용하지 않도록 설정할 수 있습니다.

예제 E에서 이 쿼리 힌트를 적용하는 예제를 찾습니다.

쿼리 저장소 사용하여 계획을 강제 적용하지만 최적화된 계획 강제 적용을 사용하지 않도록 설정

sp_query_store_force_plan 프로시저에는 disable_optimized_plan_forcing 매개 변수가 포함됩니다. 이 매개 변수를 사용하려면 저장 프로시저에 추가 매개 변수가 sp_query_store_force_plan 필요합니다. 추가 매개 변수를 호출 @replica_group_id합니다. 기본적으로 주 복제본 @replica_group_id 의 값1은 구성된 보조 복제본이 없는 경우에도 1()입니다.

C 예제의 저장 프로시저에 적절한 매개 변수를 sp_query_store_force_plan 적용하는 예제를 찾습니다.

sys.query_store_plan 카탈로그 뷰에는 계획에 연결된 최적화 재생 스크립트가 있는지 여부를 나타내는 열이 포함되어 있으며, 연결된 최적화 재생 스크립트와 관련된 기존 실패 이유 열에 새 상태를 추가합니다. sys.query_store_plan 자세히 알아보세요.

예제

이 문서의 Transact-SQL 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.

A. 데이터베이스에 대한 쿼리 저장소 및 최적화된 계획 강제 적용 사용

다음 코드는 데이터베이스에서 쿼리 저장소를 사용하도록 설정한 다음, 데이터베이스에서 최적화된 계획 강제 적용을 사용하도록 설정합니다. ALTER DATABASE SET 옵션에서 쿼리 저장소 사용하도록 설정하는 옵션에 대해 자세히 알아봅니다.

코드를 실행하기 전에 적절한 사용자 데이터베이스에 연결합니다.

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. 최적화 재생 스크립트가 있는 모든 쿼리 선택

다음 예제 코드는 쿼리 저장소에서 최적화 재생 스크립트가 있는 모든 query_ids를 선택합니다. 예제 코드를 실행하기 전에 적절한 사용자 데이터베이스에 연결합니다.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C. 쿼리 저장소에서 계획 강제 적용 및 최적화된 계획 강제 적용 사용 안 함

다음 코드는 쿼리 저장소에서 계획을 강제 적용하지만 최적화된 계획 강제 적용을 사용하지 않도록 설정합니다. 다음 코드를 실행하기 전에 @query_id 및 @plan_id를 인스턴스에 적합한 조합으로 바꿉니다. 저장 프로시저는 sp_query_store_force_plan 쿼리 저장소 최적화된 계획을 강제로 사용하지 않도록 설정하려고 할 때 매개 변수가 세 번째 매개 변수 값으로 전달될 것으로 예상 @replica_group_id 합니다. 이는 특정 복제본에서 특정 강제 계획에 대한 최적화된 계획 강제 적용을 사용하지 않도록 설정하는 데 사용할 수 있습니다. 값 @replica_group_id = 1 은 주 복제본에서 기능을 사용하지 않도록 설정하는 데 사용됩니다.

EXECUTE sp_query_store_force_plan
    @query_id = 148,
    @plan_id = 4,
    @replica_group_id = 1,
    @disable_optimized_plan_forcing = 1;
GO

sp_query_store_force_plan 자세히 알아보세요.

D. 쿼리 저장소에서 최적화된 계획 강제 적용이 사용하지 않도록 설정된 모든 쿼리 선택

다음 예제에서는 쿼리 저장소 강제로 is_optimized_plan_forcing_disabled 설정된 모든 계획을 쿼리합니다1. 코드를 실행하기 전에 적절한 사용자 데이터베이스에 연결합니다.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. 쿼리에 최적화된 계획 강제 적용 사용 안 함

다음 예제에서는 DISABLE_OPTIMIZED_PLAN_FORCING 쿼리 힌트를 사용하여 쿼리에 최적화된 계획 강제 적용을 사용하지 않도록 설정합니다.

SELECT ProductID,
       OrderQty,
       SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

관련 콘텐츠

• sys.query_store_plan(Transact-SQL)
• ALTER DATABASE SCOPED CONFIGURATION(Transact-SQL)
• sp_query_store_force_plan(Transact-SQL)
• SQL 데이터베이스의 인텔리전트 쿼리 처리