OPPO(선택적 매개 변수 계획 최적화)

적용 대상:적용 대상:Microsoft Fabric의 SQL Server 2025(17.x) 미리 보기 Azure SQL Database SQL 데이터베이스

선택적 매개 변수라는 용어는 쿼리 실행 중에 존재하는 중요한 매개 변수 값이 테이블을 검색하거나 검사해야 하는지 여부를 제어하는 PSP(매개 변수 구분 계획) 문제의 특정 변형을 나타냅니다. 간단한 예제는 다음과 같습니다.

SELECT column1,
       column2
FROM Table1
WHERE column1 = @p
      OR @p IS NULL;

이 예제에서 SQL Server는 인덱스 Table1가 있는 경우에도 항상 테이블 Table1(col1)을 검사하는 계획을 선택합니다. NULL을 사용하면 검색 계획이 불가능할 수 있습니다. 쿼리 힌트 기술(예: OPTIMIZE FOR)은 실행 중에 인덱스 탐색을 스캔으로 동적으로 변경하는 연산자가 없기 때문에 이 유형의 PSP 문제에는 유용하지 않을 수 있습니다. 런타임 시 이러한 종류의 검색->검색 조합은 해당 연산자 위에 있는 카디널리티 추정치가 정확하지 않을 수 있으므로 효과적이지 않을 수도 있습니다. 그 결과 비효율적인 계획 선택과 유사한 쿼리 패턴을 사용하는 더 복잡한 쿼리에 대한 과도한 메모리 부여가 발생합니다.

선택적 매개변수 계획 최적화(OPPO) 기능은 매개변수 민감한 계획 최적화 개선 사항을 통해 도입되어 단일 문장에서 여러 계획을 생성하는 적응 계획 최적화(Multiplan) 인프라를 사용합니다. 이렇게 하면 기능이 쿼리에 사용되는 매개 변수 값에 따라 다른 가정을 할 수 있습니다. 쿼리 실행 시간 동안 OPPO는 적절한 계획을 선택합니다.

• 여기서 매개 변수 값 IS NOT NULL은 검색 계획 또는 전체 검사 계획보다 더 최적의 항목을 사용합니다.
• 매개 변수 값이 NULL일 때 검사 계획을 사용합니다.

OPPO는 매개 변수 중요한 계획 최적화를 포함하는 적응형 계획 최적화 기능 제품군의 일부로 동적 검색 기능을 포함하는 다중 계획 기능 집합의 두 번째 구성 요소에 대한 솔루션을 제공합니다.

• 동등성 판별식

  WHERE column1 = @p
  

• 동적 검색

  WHERE column1 = @p1 OR @p1 IS NULL
    AND column2 = @p2 OR @p2 IS NOT NULL
  

용어 및 작동 방식

예를 들어 특정 목록의 침실 수에 대한 선택적 필터링을 허용하는 부동산 회사의 애플리케이션 웹 양식을 고려합니다. 일반적인 안티패턴은 선택적 필터를 다음과 같이 표현하는 것입니다.

SELECT * FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

매개 변수 @bedrooms = 10가 매개 변수 마커를 통해 탐지되고 침실 수에 대한 카디널리티가 매우 낮을 가능성이 높다는 것을 알고 있더라도, 최적화 프로세스는 @bedroomsNULL 경우에 적합한 실행 계획이 아니기 때문에 침실 열에 존재하는 인덱스를 조회하는 계획을 생성하지 않습니다. 생성된 계획에는 인덱스 검색이 포함되지 않습니다.

두 개의 별도 문으로 다시 작성할 수 있다고 상상해 보십시오. 매개 변수의 런타임 값에 따라 다음과 같이 평가할 수 있습니다.

IF @bedrooms IS NULL
    SELECT * FROM Properties;
ELSE
    SELECT * FROM Properties
    WHERE bedrooms = @bedrooms;

적응형 계획 최적화 인프라를 사용하여 이를 달성할 수 있으며, 이를 통해 두 가지 쿼리 변형을 디스패치하는 디스패처 계획을 만들 수 있습니다.

PSP 최적화에서 사용하는 조건자 카디널리티 범위 와 마찬가지로 OPPO는 계획의 쿼리 텍스트와 함께 시스템 사용 가능한 쿼리 힌트를 포함합니다. 이 힌트는 애플리케이션에서 사용하거나 직접 사용하려는 경우 유효하지 않습니다.

이전 예제를 계속합니다.

SELECT * FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

OPPO는 Showplan XML 내에 다음 특성이 추가될 수 있는 두 개의 쿼리 변형을 생성할 수 있습니다.

• @bedrooms은 NULL입니다. 쿼리 변형은 스캔 계획을 달성하기 위해 원래 쿼리를 접었습니다.

  SELECT * FROM Properties PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 1, optional_predicate(@침실 수는 NULL))

• @bedrooms IS NOT NULL

  속성에서 bedrooms = @bedrooms인 항목을 모두 선택 PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 2, optional_predicate(@bedrooms가 NULL인 경우))

선택적 매개 변수 계획 최적화 사용

데이터베이스에 OPPO를 사용하도록 설정하려면 다음 필수 구성 요소가 필요합니다.

• 데이터베이스는 호환성 수준 170을 사용해야 합니다.
• OPTIONAL_PARAMETER_OPTIMIZATION 데이터베이스 범위 구성을 사용하도록 설정해야 합니다.

OPTIONAL_PARAMETER_OPTIMIZATION 데이터베이스 범위 구성은 기본적으로 사용하도록 설정됩니다. 즉, 호환성 수준 170(SQL Server 2025의 기본값)을 사용하는 데이터베이스는 기본적으로 OPPO를 사용합니다.

다음 문을 실행하여 데이터베이스가 SQL Server 2025에서 OPPO를 사용하는지 확인할 수 있습니다.

ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

데이터베이스에 대한 선택적 매개 변수 계획 최적화를 사용하지 않도록 설정하려면 데이터베이스 범위 구성을 OPTIONAL_PARAMETER_OPTIMIZATION 사용하지 않도록 설정합니다.

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

쿼리 힌트를 통해 선택적 매개 변수 계획 최적화 사용

쿼리 힌트를 DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION 사용하여 지정된 쿼리에 대한 선택적 매개 변수 계획 최적화를 사용하지 않도록 설정할 수 있습니다. USE HINT 문구를 통해 힌트를 지정해야 합니다. 자세한 내용은 쿼리 힌트를 참조하세요.

힌트는 어떤 호환성 수준에서도 작동하며 데이터베이스 범위 구성을 재정의합니다 OPTIONAL_PARAMETER_OPTIMIZATION.

쿼리 힌트는 DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION 쿼리에서 직접 또는 쿼리 저장소 힌트를 통해 지정할 수 있습니다.

확장 이벤트

• optional_parameter_optimization_skipped_reason: OPPO가 쿼리를 최적화할 수 없다고 결정할 때 발생합니다. 이 확장 이벤트는 PSP 최적화에서 사용되는 parameter_sensitive_plan_optimization_skipped_reason 이벤트와 동일한 패턴을 따릅니다. 쿼리는 PSP 최적화 및 OPPO 쿼리 변형을 모두 생성할 수 있으므로 두 이벤트를 모두 확인하여 하나 또는 둘 다 기능이 참여하는 이유를 이해해야 합니다. 다음 쿼리는 PSP를 건너뙤는 이유를 모두 보여 줍니다.

SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'opo_skipped_reason_enum'
ORDER BY map_key;

• query_with_optional_parameter_predicate: 확장 이벤트는 PSP 최적화에서 사용되는 query_with_parameter_sensitivity 이벤트와 동일한 패턴을 따릅니다. PSP 최적화 개선 사항에서 사용할 수 있는 추가 필드에는 흥미로운 조건자의 수를 표시하고, 흥미로운 조건자에 대한 json 형식의 자세한 정보가 포함되어 있으며, 조건자 또는 조건자들에 대해 OPPO가 지원되는지도 확인할 수 있습니다.

비고

• 쿼리 변형의 ShowPlan XML은 선택한 조건자에 대한 정보가 PLAN PER VALUE에 추가되고, optional_predicate 힌트가 사용된 다음의 예시와 유사합니다.

<Batch>
  <Statements>
    <StmtSimple StatementCompId="4" StatementEstRows="1989" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="170" StatementSubTreeCost="0.0563916" StatementText="SELECT PropertyId, AgentId, ListingPrice, ZipCode, SquareFootage, &#xD;&#xA;           Bedrooms, Bathrooms, ListingDescription&#xD;&#xA;    FROM dbo.Property &#xD;&#xA;    WHERE (@AgentId IS NULL OR AgentId = @AgentId)&#xD;&#xA;      AND (@ZipCode IS NULL OR ZipCode = @ZipCode)&#xD;&#xA;      AND (@MinPrice IS NULL OR ListingPrice &gt;= @MinPrice)&#xD;&#xA;      AND (@HasDescription IS NULL OR &#xD;&#xA;           (@HasDescription = 1 AND ListingDescription IS NOT NULL) OR&#xD;&#xA;           (@HasDescription = 0 AND ListingDescription IS NULL)) option (PLAN PER VALUE(ObjectID = 1269579561, QueryVariantID = 7, optional_predicate(@MinPrice IS NULL),optional_predicate(@ZipCode IS NULL),optional_predicate(@AgentId IS NULL)))" StatementType="SELECT" QueryHash="0x2F701925D1202A9F" QueryPlanHash="0xBA0B2B1A18AF1033" RetrievedFromCache="true" StatementSqlHandle="0x09000033F4BE101B2EE46B1615A038D422710000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="1269579561" StatementParameterizationType="1" SecurityPolicyApplied="false">
      <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
      <Dispatcher>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@MinPrice] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@MinPrice" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@ZipCode] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@ZipCode" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@AgentId] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
      </Dispatcher>
      <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="376" QueryVariantID="7">

• query_with_optional_parameter_predicate 확장 이벤트의 출력 예제

관련 콘텐츠

• 쿼리 처리 아키텍처 가이드
• 실행 계획 다시 컴파일
• 매개 변수 및 실행 계획 재사용
• 단순 매개 변수화
• 강제 매개 변수화
• 쿼리 힌트(Transact SQL)
• SQL 데이터베이스의 인텔리전트 쿼리 처리
• 매개 변수 민감도
• ALTER DATABASE SCOPED CONFIGURATION(Transact-SQL)