optimize for ad hoc workloads(서버 구성 옵션)

적용 대상: SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

임시 워크로드 최적화 옵션은 여러 단일 사용 임시 일괄 처리가 포함된 워크로드에 대한 계획 캐시의 효율성을 향상시키는 데 사용됩니다. 이 옵션을 1로 설정하면 데이터베이스 엔진이 일괄 처리가 처음으로 컴파일되었을 때 전체 컴파일된 계획 대신 계획 캐시에 포함된 작은 컴파일된 계획 스텁을 저장합니다. 이 옵션은 계획 캐시가 재사용되지 않는 컴파일된 계획으로 채워지지 않게 되므로 메모리 압력을 해소하는 데 도움이 될 수 있습니다. 그러나 이 옵션을 사용하도록 설정하면 일회용 계획 문제를 해결하는 기능에 영향을 줄 수 있습니다.

컴파일된 계획 스텁을 사용하면 데이터베이스 엔진이 이 임시 일괄 처리가 이전에 컴파일되었음을 인식하고 컴파일된 계획 스텁만 저장합니다. 이 일괄 처리가 다시 호출(컴파일 또는 실행)되면 데이터베이스 엔진이 일괄 처리를 컴파일하고, 계획 캐시에서 컴파일된 계획 스텁을 제거하고, 전체 컴파일된 계획을 계획 캐시에 추가합니다.

sys.dm_exec_cached_plans 카탈로그 뷰를 쿼리하고 cacheobjtype 열에서 “컴파일된 계획”을 찾아 컴파일된 계획 스텁을 찾을 수 있습니다. 스텁에는 고유한 plan_handle이 있습니다. 컴파일된 계획 스텁에는 연결된 실행 계획이 없으며 계획 핸들을 쿼리해도 그래픽 또는 XML 실행 계획이 반환되지 않습니다.

Trace Flag 8032은 캐시가 더 커지도록 허용하는 SQL Server 2005(9.x) RTM 설정으로 캐시 제한 매개 변수를 복구합니다. 자주 재사용되는 캐시 항목이 캐시에 맞지 않고 optimize for ad hoc workloads 옵션으로 계획 캐시 관련 문제를 해결하지 못한 경우 이 설정을 사용합니다.

설명

optimize for ad hoc workloads 옵션을 1로 설정하면 새 계획만 영향을 받으며, 이미 계획 캐시에 있던 계획은 영향을 받지 않습니다.

이미 캐시된 쿼리 계획에 즉시 영향을 주려면 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE를 사용하여 계획 캐시를 지워야 하거나 SQL Server를 다시 시작해야 합니다.

권장 사항

계획 캐시에 많은 수의 일회용 계획이 있으며 안됩니다. 일반적인 원인은 다음과 같습니다.

• 일관되게 정의되지 않은 쿼리 매개 변수의 데이터 형식입니다. 이는 문자열 길이에 특히 적용되지만 최대값, 정밀도 또는 소수 자릿수가 있는 모든 데이터 형식에 적용할 수 있습니다. 예를 들어 @Greeting으로 명명된 매개 변수가 한 호출에서 nvarchar(10)로, 다음 호출에서 nvarchar(20)로 전달되는 경우 각 매개 변수 크기에 대해 별도의 계획이 만들어집니다.

• 매개 변수화되지 않은 쿼리입니다. 쿼리에 하드 코딩된 값이 데이터베이스 엔진에 제출되는 매개 변수가 하나 이상 있는 경우 각 쿼리에 대해 많은 수의 쿼리 계획이 있을 수 있습니다. 사용된 쿼리 매개 변수 데이터 형식과 길이의 각 조합에 대한 계획이 있을 수 있습니다.

일회용 계획의 수가 OLTP 서버의 SQL Server 데이터베이스 엔진 메모리의 상당 부분을 차지하고 이러한 계획이 임시 계획인 경우 이 서버 옵션을 사용하여 이들 개체의 메모리 사용을 줄입니다.

임시 워크로드 최적화 옵션을 사용하는 경우 계획 스텁만 캐시되므로 일회용 쿼리에 대한 실행 계획을 볼 수 없습니다. 환경 및 워크로드에 따라 다음 두 가지 기능을 활용할 수 있습니다.

• SQL Server 2016(13.x)에 도입된 쿼리 저장소 기능을 사용하면 쿼리 계획 변경으로 인한 성능 차이를 빠르게 찾을 수 있습니다. 쿼리 저장소 SQL Server 2022(16.x) 이상 버전의 새 데이터베이스에서 기본적으로 사용하도록 설정됩니다.

• 강제 매개 변수화는 쿼리 컴파일 및 재컴파일 빈도를 줄여 특정 데이터베이스의 성능을 향상시킬 수 있습니다. 강제 매개 변수화의 이점을 활용하는 데이터베이스는 일반적으로 판매 시점 애플리케이션과 같은 원본에서 대량의 동시 쿼리를 경험합니다.

  강제 매개 변수화는 매개 변수 민감도로 인해 성능 문제를 일으킬 수 있습니다. 자세한 내용은 매개 변수에 민감한 문제 조사 및 해결을 참조하세요. SQL Server 2022(16.x) 이상 버전의 경우 매개 변수에 민감한 계획 최적화를 사용하도록 설정할 수도 있습니다.

예제

단일 사용으로 캐시된 계획의 수를 찾으려면 다음 쿼리를 실행합니다.

SELECT objtype,
    cacheobjtype,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
    AND usecounts = 1
GROUP BY objtype, cacheobjtype;

관련 콘텐츠

• sys.dm_exec_cached_plans (Transact-SQL)
• 서버 구성 옵션(SQL Server)
• 쿼리 저장소를 사용하여 성능 모니터링
• 쿼리 처리 아키텍처 가이드
• 매개 변수 중요한 계획 최적화