쿼리 저장소를 사용하여 최적화된 계획 강제 실행
적용 대상:
SQL Server 2022(16.x)
쿼리 최적화는 “충분한” 쿼리 실행 계획을 생성하는 다단계 프로세스입니다. 경우에 따라 쿼리 최적화의 일부인 쿼리 컴파일은 전체 쿼리 실행 시간의 큰 비율을 나타내고 상당한 시스템 리소스를 사용할 수 있습니다. 최적화된 계획 강제 적용은 지능형 쿼리 처리 기능 제품군의 일부입니다. 최적화된 계획 강제 적용은 강제 쿼리를 반복하기 위한 컴파일 오버헤드를 줄이고 쿼리 저장소를 사용하도록 설정하고 "읽기 쓰기" 모드로 설정해야 합니다. 쿼리 실행 계획이 생성되면 최적화 재생 스크립트로 다시 사용할 수 있는 특정 컴파일 단계가 저장됩니다. 최적화 재생 스크립트는 쿼리 저장소의 압축된 실행 계획 XML의 일부로 숨겨진 OptimizationReplay 특성에 저장됩니다.
최적화된 계획 강제 실행 구현
쿼리가 컴파일 프로세스를 처음 통과하는 경우 최적화에 소요된 시간(쿼리 최적화 프로그램 입력 트리 기반)에 따른 임계값은 최적화 재생 스크립트가 생성되는지 여부를 결정합니다.
컴파일이 완료되면 이전 예측이 올바른지 여부를 평가하기 위해 여러 런타임 메트릭을 사용할 수 있게 됩니다. 임계값을 초과한 것으로 확인되면 최적화 재생 스크립트가 지속성을 사용할 수 있습니다. 이러한 런타임 메트릭에는 액세스하는 개체 수, 조인 수, 최적화 중에 실행된 최적화 작업 수, 실제 최적화 시간이 포함됩니다.
최적화 재생 스크립트를 사용할 경우의 잠재적 이점은 최적화 재생 스크립트를 저장하는 오버헤드와도 비교됩니다. 최적화 재생 스크립트를 재생하기 위한 상대 시간의 예측은 최적화 재생 스크립트에 저장된 최적화 작업의 수와 일반 컴파일 중에 실행된 최적화 작업 수를 기준으로 일반 최적화 프로세스를 실행하는 데 소요된 시간과 비교됩니다. 최적화 재생 스크립트를 재생하여 컴파일 시간을 줄이는 데 상당한 이점이 있는 경우 최적화 재생 스크립트가 유지됩니다.
고려 사항
최적화된 계획 강제 적용 기능을 사용하도록 설정하는 경우 최적화된 계획 강제 적용에 대한 자격 기준은 다음과 같습니다.
- 전체 최적화를 거치는 쿼리 계획만 적격하며
StatementOptmLevel="FULL"속성이 있으면 확인할 수 있습니다. - 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의 값을 갖습니다.
예제 C의 sp_query_store_force_plan 저장 프로시저에 적절한 매개 변수를 적용하는 예제를 찾습니다.
sys.query_store_plan 카탈로그 뷰에는 계획에 연결된 최적화 재생 스크립트가 있는지 여부를 나타내는 열이 포함되어 있으며, 연결된 최적화 재생 스크립트와 관련된 기존 실패 이유 열에 새 상태를 추가합니다. sys.query_store_plan(Transact-SQL)에서 자세히 알아보세요.
예
A. 데이터베이스에 대한 쿼리 저장소 및 최적화된 계획 강제 적용 사용
다음 코드는 데이터베이스에서 쿼리 저장소를 사용하도록 설정한 다음, 데이터베이스에서 최적화된 계획 강제 적용을 사용하도록 설정합니다. ALTER DATABASE SET 옵션(Transact-SQL)에서 쿼리 저장소를 사용하도록 설정하는 옵션에 대해 자세히 알아봅니다.
코드를 실행하기 전에 적절한 사용자 데이터베이스에 연결합니다.
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 합니다. 이는 특정 복제본에서 특정 강제 계획에 대한 최적화된 계획 강제 적용을 사용하지 않도록 설정하는 데 사용할 수 있습니다. 값 1 - @replica_group_id=1 주 복제본에서 기능을 사용하지 않도록 설정하는 데 사용됩니다.
EXEC 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(Transact-SQL)에서 자세히 알아보세요.
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쿼리 힌트를 사용하여 쿼리에 최적화된 계획 강제 적용을 사용하지 않도록 설정합니다. 이 예에서는 AdventureWorks 샘플 데이터베이스를 사용합니다.
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
다음 단계
다음 문서에서 쿼리 저장소 및 최적화된 계획 강제 적용에 대해 자세히 알아봅니다.
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기