매개 변수 중요한 계획 최적화

아티클
01/08/2024

적용 대상: SQL Server 2022(16.x) 이상 버전

PSP(매개 변수 중요 계획) 최적화는 지능형 쿼리 처리 기능 제품군의 일부입니다. 매개 변수가 있는 쿼리에 대해 캐시된 단일 계획이 들어오는 모든 매개 변수 값에 최적이 아닌 시나리오를 해결합니다. 균일하지 않은 데이터 배포의 경우입니다. 자세한 내용은 매개 변수 민감도와 매개 변수 및 실행 계획 재사용을 참조하세요.

이 문제 시나리오에 대한 기존 해결 방법에 대한 자세한 내용은 다음을 참조하세요.

PSP 최적화는 매개 변수가 있는 단일 문에 대해 활성 캐시된 여러 계획을 자동으로 사용하도록 설정합니다. 캐시된 실행 계획은 고객이 제공한 런타임 매개 변수 값에 따라 다양한 데이터 크기를 수용합니다.

매개 변수화 이해

SQL Server 데이터베이스 엔진 T-SQL(Transact-SQL) 문에서 매개 변수 또는 매개 변수 마커를 사용하면 관계형 엔진이 새 T-SQL 문을 이전에 컴파일된 기존 실행 계획과 일치시키고 계획 재사용을 승격할 수 있습니다. 자세한 내용은 단순 매개 변수화를 참조하세요.

데이터베이스의 모든 SELECT, INSERT및 UPDATEDELETE 문이 특정 제한 사항에 따라 매개 변수화되도록 지정하여 SQL Server의 기본 단순 매개 변수화 동작을 재정의할 수도 있습니다. 자세한 내용은 강제 매개 변수화를 참조하세요.

PSP 최적화 구현

초기 컴파일 중에 열 통계 히스토그램은 균일하지 않은 분포를 식별하고 사용 가능한 모든 조건자 중 최대 3개까지 위험에 처한 매개 변수가 있는 조건자를 평가합니다. 즉, 동일한 쿼리 내의 여러 조건자가 조건을 충족하는 경우 PSP 최적화는 상위 3개를 선택합니다. PSP 기능은 너무 많은 계획으로 계획 캐시 및 쿼리 저장소(쿼리 저장소 사용하도록 설정된 경우)를 블로트하는 것을 방지하기 위해 평가되는 조건자 수를 제한합니다.

적격 계획의 경우 초기 컴파일은 디스패처 식이라는 PSP 최적화 논리를 포함하는 디스패처 계획을 생성합니다. 디스패처 계획은 카디널리티 범위 경계 값 조건자를 기반으로 하는 쿼리 변형에 매핑됩니다.

용어

디스패처 식

런타임 매개 변수 값을 기반으로 조건자의 카드 비인식성을 평가하고 실행을 다른 쿼리 변형으로 라우팅합니다.

디스패처 계획

디스패처 식을 포함하는 계획은 원래 쿼리에 대해 캐시됩니다. 디스패처 계획은 기본적으로 기능에서 선택한 조건자의 컬렉션이며 몇 가지 추가 세부 정보가 있습니다. 선택한 각 조건자에 대해 디스패처 계획에 포함된 세부 정보 중 일부는 높고 낮은 경계 값입니다. 이러한 값은 매개 변수 값을 다른 버킷 또는 범위로 나누는 데 사용됩니다. 디스패처 계획에는 경계 값을 계산하는 데 사용된 통계도 포함됩니다.

쿼리 변형

디스패처 계획은 런타임 매개 변수 값을 기반으로 조건자의 카드 비합리성을 평가하므로 해당 값을 버킷화하고 별도의 자식 쿼리를 생성하여 컴파일하고 실행합니다. 이러한 자식 쿼리를 쿼리 변형이라고 합니다. 쿼리 변형에는 계획 캐시 및 쿼리 저장소에 자체 계획이 있습니다.

조건자 카드위치 범위

런타임 시 각 조건자의 카드은 런타임 매개 변수 값에 따라 평가됩니다. 디스패처는 컴파일 시간에 카드inality 값을 세 가지 조건자 카드inality 범위로 버킷트합니다. 예를 들어 PSP 최적화 기능은 다음 다이어그램과 같이 낮음, 중간 및 높은 카디널리티 범위를 나타내는 세 가지 범위를 만들 수 있습니다.

즉, 매개 변수가 있는 쿼리가 처음 컴파일되면 PSP 최적화 기능은 디스패처 계획이라는 셸 계획을 생성합니다. 디스패처 식에는 매개 변수의 런타임 값에 따라 쿼리를 쿼리 변형으로 버킷트하는 논리가 있습니다. 실제 실행이 시작되면 디스패처는 다음 두 단계를 수행합니다.

디스패처는 지정된 매개 변수 집합에 대한 디스패처 식을 평가하여 카드inality 범위를 계산합니다.
디스패처는 이러한 범위를 특정 쿼리 변형에 매핑하고 변형을 컴파일하고 실행합니다. 여러 쿼리 변형이 있으므로 PSP 최적화 기능은 단일 쿼리에 대해 여러 계획을 갖게 됩니다.

카드진수 범위 경계는 디스패치 계획의 ShowPlan XML 내에서 확인할 수 있습니다.

<Dispatcher>
  <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
    <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" />
    <Predicate>
      <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="@AgentId" />
            </Identifier>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Predicate>
  </ParameterSensitivePredicate>
</Dispatcher>

PSP 최적화 생성 힌트는 쿼리 변형의 ShowPlan XML에 SQL 문에 추가됩니다. 힌트는 직접 사용할 수 없으며 수동으로 추가된 경우 구문 분석되지 않습니다 . 힌트에는 다음 요소가 포함됩니다.

option ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[ schemaName]. [tableName]. [columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) ) )

ObjectID 는 현재 문이 속한 모듈(즉, 저장 프로시저, 함수, 트리거)에서 가져온 것이며, 모듈에서 문이 생성되었다고 가정합니다. 문이 동적 또는 임시 SQL(즉, )의 결과인 sp_executesql경우 ObjectID 요소는 같습니다 0.
QueryVariantID 는 PSP 최적화가 선택한 모든 조건자의 범위 조합과 거의 동일합니다. 예를 들어 쿼리에 PSP에 적합한 두 개의 조건자가 있고 각 조건자의 범위가 3개인 경우 1-9로 번호가 매겨진 9개의 쿼리 변형 범위가 있습니다.
조건자 범위는 디스패처 식에서 생성된 조건자 카드inality 범위 정보입니다.

또한 쿼리 변형의 ShowPlan XML 내에서(Dispatcher 요소 내부)

<Batch>
  <Statements>
    <StmtSimple StatementText="SELECT PropertyId,&#xD;&#xA;       AgentId,&#xD;&#xA;       MLSLinkId,&#xD;&#xA;       ListingPrice,&#xD;&#xA;       ZipCode,&#xD;&#xA;       Bedrooms,&#xD;&#xA;       Bathrooms&#xD;&#xA;FROM dbo.Property&#xD;&#xA;WHERE AgentId = @AgentId&#xD;&#xA;ORDER BY ListingPrice DESC option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1, predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090085E4372DFC69BB9E7EBA421561DE8E1E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.021738" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x476167A000F589CD" QueryPlanHash="0xDE982107B7C28AAE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2019-09-07T15:32:16.89" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_Property_AgentId]" Table="[Property]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>

    </StmtSimple>
  </Statements>
</Batch>

설명

PSP 최적화 기능은 현재 같음 조건자에서만 작동합니다.
중요한 데이터 배포 변경 내용이 있는 경우 디스패처 계획은 자동으로 다시 작성됩니다. 쿼리 변형 계획은 다른 쿼리 계획 유형과 마찬가지로 필요에 따라 독립적으로 다시 컴파일되며 기본 다시 컴파일 이벤트가 적용됩니다. 다시 컴파일에 대한 자세한 내용은 실행 계획 다시 컴파일을 참조하세요.
일반 컴파일된 계획, 디스패처 계획 및 쿼리 변형 계획을 구분하기 위해 sys.query_store_plan(Transact-SQL) 쿼리 저장소 시스템 카탈로그 뷰가 변경되었습니다. 새 쿼리 저장소 시스템 카탈로그 뷰인 sys.query_store_query_variant(Transact-SQL)에는 원래 매개 변수가 있는 쿼리(부모 쿼리라고도 함), 디스패처 계획 및 자식 쿼리 변형 간의 부모-자식 관계에 대한 정보가 포함되어 있습니다.
동일한 테이블의 일부인 여러 조건자가 있는 경우 PSP 최적화는 기본 통계 히스토그램에 따라 데이터 오차가 가장 많은 조건자를 선택합니다. 예를 들어 SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2, 둘 다 column1 = @predicate1 동일한 column2 = @predicate2 테이블에서 table1온 것이기 때문에 가장 왜곡된 조건자만 기능에 의해 평가됩니다. 그러나 예제 쿼리에 a와 같은 UNION연산자가 포함된 경우 PSP는 둘 이상의 조건자를 평가합니다. 예를 들어 쿼리와 유사한 SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate특성이 있는 경우 시스템에서 이 시나리오를 서로 다른 두 테이블인 것처럼 취급하기 때문에 PSP는 이 경우 최대 두 개의 조건자를 선택합니다. 테이블 별칭을 통해 자체 조인하는 쿼리에서 동일한 동작을 관찰할 수 있습니다.

쿼리 변형에 대한 ShowPlan XML은 선택한 두 조건자 모두 관련 힌트에 해당 정보가 추가 PLAN PER VALUE PSP된 다음 예제와 유사합니다.

<Batch>
  <Statements>
    <StmtSimple StatementText="SELECT  b.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty a join  PropertyDetails b on a.PropertyId = b.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id&#xD;&#xA;UNION&#xD;&#xA;          SELECT  c.PropertyId, &#xD;&#xA;       AgentId, &#xD;&#xA;       MLSLinkId, &#xD;&#xA;       ListingPrice, &#xD;&#xA;       ZipCode, &#xD;&#xA;       Bedrooms, &#xD;&#xA;       Bathrooms &#xD;&#xA;FROM dbo.AgentProperty c join  PropertyDetails d on c.PropertyId = d.PropertyId&#xD;&#xA;WHERE AgentId = @AgentId and  Property_id=@Property_id option (PLAN PER VALUE(ObjectID = 981578535, QueryVariantID = 9, predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0),predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090051FBCD918F8DFD60D324887356B422D90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="29.2419" StatementEstRows="211837" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6D2A4E407085C01E" QueryPlanHash="0x72101C0A0DD861AB" CardinalityEstimationModelVersion="160" BatchModeOnRowStoreUsed="true">
      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <Dispatcher>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [a].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[a]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
        <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
          <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" />
          <Predicate>
            <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [c].[AgentId]=[@AgentId]">
              <Compare CompareOp="EQ">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[c]" Column="AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </ParameterSensitivePredicate>
      </Dispatcher>
      <QueryPlan CachedPlanSize="160" CompileTime="17" CompileCPU="17" CompileMemory="1856" QueryVariantID="9">

다음 방법 중 하나 이상을 사용하여 PSP 최적화 기능에서 사용하는 현재 기울이기 임계값에 영향을 줄 수 있습니다.
- CE(카디널리티 추정기) 추적 플래그(예: 추적 플래그 9481 (전역, 세션 또는 쿼리 수준)
- 사용 중인 CE 모델을 낮추거나 CE 모델이 여러 조건자의 독립성과 관련하여 가정하는 데 영향을 주려는 데이터베이스 범위 구성 옵션입니다. 이는 다중 열 통계가 존재하지 않는 경우 특히 유용하며, 이는 PSP 최적화에서 해당 조건자의 후보를 평가하는 능력에 영향을 줍니다.
- 자세한 내용은 SQL Server 2014 카디널리티 추정기 백서로 쿼리 계획 최적화의 여러 조건자에 대한 상관 관계 가정 증가 섹션을 참조하세요. 최신 CE 모델은 조건자의 결합 및 분리에 대한 몇 가지 상관 관계와 덜 독립성이라고 가정하려고 시도합니다. 레거시 CE 모델을 사용하면 다중 열 조인 시나리오에서 조건자의 선택성을 계산하는 방법에 영향을 줄 수 있습니다. 이 작업은 특정 시나리오에 대해서만 고려해야 하며 대부분의 워크로드에 레거시 CE 모델을 사용하지 않는 것이 좋습니다.
PSP 최적화는 현재 각 쿼리 변형을 새 준비된 문으로 컴파일하고 실행합니다. 이는 디스패처 계획이 모듈(즉, 저장 프로시저, 트리거, 함수, 뷰 등)을 기반으로 하는 경우 쿼리 변형이 부모 모듈 object_id 과의 연결을 잃는 이유 중 하나입니다. 준비된 문으로, object_id 개체에 sys.objects 직접 매핑할 수 있는 것은 아니지만 기본적으로 일괄 처리 텍스트의 내부 해시를 기반으로 계산된 값입니다. 자세한 내용은 DMV 설명서의 테이블 반환 섹션을 sys.dm_exec_plan_attributes 참조하세요.

쿼리 변형 계획은 계획 캐시 개체 저장소(CACHESTORE_OBJCP)에 배치되고 디스패처 계획은 SQL Plans 캐시 저장소(CACHESTORE_SQLCP)에 배치됩니다. 그러나 PSP 기능은 부모 쿼리가 동적 또는 임시 T-SQL이 아닌 모듈의 일부인 경우 PSP가 ShowPlan XML에 추가하는 PLAN PER VALUE 힌트의 일부인 ObjectID 특성 내에 쿼리 변형의 부모를 저장 object_id 합니다. 캐시된 프로시저, 함수 및 트리거에 대한 집계 성능 통계는 해당 용도로 계속 사용될 수 있습니다. DMV와 유사한 sys.dm_exec_query_stats 뷰에 있는 것과 같은 더 세부적인 실행 관련 통계에는 여전히 쿼리 변형에 대한 데이터가 포함되어 있지만, 테이블 내 sys.objects 의 쿼리 변형과 개체 간의 object_id 연결은 더 세분화된 런타임 통계가 필요한 각 쿼리 변형에 대한 ShowPlan XML의 추가 처리 없이는 현재 정렬되지 않습니다. 쿼리 저장소 사용하도록 설정된 경우 추가 ShowPlan XML 구문 분석 기술 없이도 쿼리 변형에 대한 런타임 및 대기 통계 정보를 쿼리 저장소 가져올 수 있습니다.
PSP 쿼리 변형은 새 준비된 문으로 실행되므로 ShowPlan XML을 파쇄하고 텍스트 패턴 일치 기술(즉, object_id 추가 XQuery 처리)을 적용하지 않고 다양한 계획 캐시 관련 sys.dm_exec_* DMV에 자동으로 노출되지 않습니다. PSP 최적화 디스패처 계획만 현재 적절한 부모 개체 ID를 내보낸다. object_id 쿼리 저장소 계획 캐시 계층 구조에서 제공하는 것보다 더 많은 관계형 모델을 허용하므로 쿼리 저장소 내에 노출됩니다. 자세한 내용은 쿼리 저장소 시스템 카탈로그 뷰 sys.query_store_query_variant(Transact-SQL)를 참조하세요.

고려 사항

PSP 최적화를 사용하도록 설정하려면 쿼리를 실행할 때 연결된 데이터베이스의 데이터베이스 호환성 수준 160을 사용하도록 설정합니다.
PSP 최적화 기능에 대한 추가 인사이트를 위해 쿼리 저장소 켜서 쿼리 저장소 통합을 사용하도록 설정하는 것이 좋습니다. 다음 예제에서는 다음과 같은 MyNewDatabase기존 데이터베이스에 대한 쿼리 저장소 켭니다.

ALTER DATABASE [MyNewDatabase]
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO
);

참고 항목

이제 SQL Server 2022(16.x)부터 새로 만든 모든 데이터베이스에 대해 쿼리 저장소 기본적으로 사용하도록 설정됩니다.

데이터베이스 수준에서 PSP 최적화를 사용하지 않도록 설정하려면 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF 데이터베이스 범위 구성을 사용합니다.
쿼리 수준에서 PSP 최적화를 사용하지 않도록 설정하려면 DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION 쿼리 힌트를 사용합니다.
추적 플래그 4136, PARAMETER_SNIFFING 데이터베이스 범위 구성 또는 쿼리 힌트로 매개 변수 검색을 USE HINT('DISABLE_PARAMETER_SNIFFING') 사용하지 않도록 설정하면 연결된 워크로드 및 실행 컨텍스트에 대해 PSP 최적화를 사용할 수 없습니다. 자세한 내용은 힌트(Transact-SQL) - 쿼리 및 ALTER DATABASE SCOPED CONFIGURATION(Transact-SQL)을 참조하세요.
계획 캐시에 저장된 디스패처 당 고유한 계획 변형 수는 캐시 블로팅을 방지하기 위해 제한됩니다. 내부 임계값은 문서화되지 않습니다. 각 SQL 일괄 처리는 여러 계획을 만들 가능성이 있으며 각 쿼리 변형 계획에는 계획 캐시에 독립적인 항목이 있으므로 허용되는 계획 항목의 기본 최대 수에 도달할 수 있습니다. 계획 캐시 제거 속도가 눈에 띄게 높거나 캐시 저장소 및 캐시 저장소의 CACHESTORE_OBJCPCACHESTORE_SQLCP크기가 과도한 경우 추적 플래그 174를 적용하는 것이 좋습니다.
쿼리 저장소에서 쿼리에 대해 저장된 고유한 계획 변형의 수는 max_plans_per_query 구성 옵션으로 제한됩니다. 쿼리 변형에 둘 이상의 계획이 있을 수 있으므로 쿼리 저장소 내의 쿼리당 총 200개의 계획이 있을 수 있습니다. 이 숫자에는 부모 쿼리에 속하는 모든 디스패처에 대한 모든 쿼리 변형 계획이 포함됩니다. 쿼리 저장소 구성 옵션을 늘리는 max_plans_per_query 것이 좋습니다.
- 고유한 계획 수가 기본 쿼리 저장소 max_plans_per_query 제한을 초과할 수 있는 방법의 예는 다음과 같은 동작이 있는 시나리오입니다. 쿼리 ID가 10인 쿼리가 있는데 디스패처 계획이 2개이고 각 디스패처 계획에는 각각 20개의 쿼리 변형(총 40개의 쿼리 변형)이 있다고 가정해 보겠습니다. 쿼리 ID 10의 총 계획 수는 쿼리 변형에 대한 40개의 계획과 두 개의 디스패처 계획입니다. 부모 쿼리 자체(쿼리 ID 10)에는 5개 일반(비디스패처) 계획이 있을 수도 있습니다. 이렇게 하면 47개의 계획(쿼리 변형에서 40개, 디스패처 2개 및 PSP가 아닌 5개 관련 계획)이 만들어집니다. 또한 각 쿼리 변형에 평균 5개의 계획도 있는 경우 이 시나리오에서는 부모 쿼리에 대한 쿼리 저장소 200개 이상의 계획을 가질 수 있습니다. 또한 이 예제 부모 쿼리가 참조할 수 있는 데이터 세트의 데이터 오차가 많을 때도 달라집니다.
지정된 디스패처에 대한 각 쿼리 변형 매핑의 경우:
- query_plan_hash는 고유합니다. 이 열은 기타 동적 관리 뷰 및 카탈로그 테이블에서 사용할 수 있습니다 sys.dm_exec_query_stats.
- plan_handle는 고유합니다. 이 열은 sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans, 기타 동적 관리 뷰와 함수 및 카탈로그 테이블에서 사용할 수 있습니다.
- query_hash는 동일한 디스패처에 매핑되는 다른 변형에 공통적이므로 입력 매개 변수 값에 따라 다른 쿼리에 대한 집계 리소스 사용량을 확인할 수 있습니다. 이 열은 sys.dm_exec_query_stats, sys.query_store_query, 기타 동적 관리 뷰 및 카탈로그 테이블에서 사용할 수 있습니다.
- sql_handle은 컴파일하는 동안 특수 PSP 최적화 식별자가 쿼리 텍스트에 추가되기 때문에 고유합니다. 이 열은 sys.dm_exec_query_stats, sys.dm_exec_sql_text, sys.dm_exec_cached_plans, 기타 동적 관리 뷰와 함수 및 카탈로그 테이블에서 사용할 수 있습니다. sys.query_store_query 카탈로그 테이블의 last_compile_batch_sql_handle 열과 동일한 핸들 정보를 쿼리 저장소에서 사용할 수 있습니다.
- 쿼리 저장소에서 query_id는 고유합니다. 이 열은 sys.query_store_query 및 다른 쿼리 저장소 카탈로그 테이블에서 사용할 수 있습니다.

쿼리 저장소의 계획 강제 적용

동일한 sp_query_store_force_plan 및 sp_query_store_unforce_plan 저장 프로시저를 사용하여 디스패처 또는 변형 계획에서 작동합니다.

변형이 강제로 적용되는 경우 부모 디스패처는 강제되지 않습니다. 디스패처가 강제 적용된 경우 해당 디스패처의 변형만 사용할 수 있는 것으로 간주됩니다.

이전에는 다른 디스패처의 강제 변형이 비활성 상태가 되었지만 디스패처가 다시 강제될 때까지 강제 상태 유지합니다.
이전에 비활성 상태가 된 동일한 디스패처의 강제 변형이 다시 강제 적용됩니다.

쿼리 힌트 동작 쿼리 저장소

쿼리 저장소 힌트가 쿼리 변형(자식 쿼리)에 추가되면 힌트는 PSP가 아닌 쿼리와 동일한 방식으로 적용됩니다. 쿼리 저장소 부모 쿼리에도 힌트가 적용된 경우 쿼리 변형 힌트의 우선 순위가 더 높습니다.
쿼리 저장소 힌트가 부모 쿼리에 추가되고 자식 쿼리(쿼리 변형)에 기존 쿼리 저장소 힌트가 없는 경우 자식 쿼리(쿼리 변형)는 부모 쿼리에서 힌트를 상속합니다.
부모 쿼리에서 쿼리 저장소 쿼리 힌트가 제거되면 자식 쿼리(쿼리 변형)도 힌트가 제거됩니다.
힌트가 RECOMPILE 부모 쿼리에 추가되면 PSP 기능이 힌트가 있는 쿼리에서 작동하지 않으므로 기존 쿼리 변형 계획이 계획 캐시에서 제거된 후 시스템에서 PSP가 RECOMPILE 아닌 계획을 생성합니다.
쿼리 저장소 힌트 결과는 확장 이벤트 query_store_hints_application_success 및 query_store_hints_application_failed 이벤트를 사용하여 관찰할 수 있습니다. sys.query_store_query_hints 테이블의 경우 적용된 쿼리 힌트에 대한 정보가 포함됩니다. 부모 쿼리에만 힌트가 적용된 경우 시스템 카탈로그에는 부모 쿼리에 대한 힌트 정보가 포함되지만 자식 쿼리에 대한 힌트 정보는 포함되지 않지만 자식 쿼리는 부모 쿼리의 힌트를 상속합니다.

쿼리 힌트 및 계획 강제 동작이 있는 PSP는 다음 표에 요약되어 있습니다.

쿼리 변형 힌트 또는 계획	부모에 사용자 적용 힌트가 있습니다.	부모에 피드백 적용 힌트가 있습니다.	부모가 수동으로 강제 계획을 가지고 있습니다.	부모에는 APC ¹ 강제 계획이 있습니다.
사용자를 통한 힌트	쿼리 변형 힌트	쿼리 변형 힌트	쿼리 변형 힌트	해당 없음
피드백을 통한 힌트	쿼리 변형 힌트	쿼리 변형 힌트	쿼리 변형 힌트	해당 없음
사용자가 강제 적용한 계획	쿼리 변형 강제 계획	쿼리 변형 강제 계획	쿼리 변형 강제 계획	쿼리 변형 강제 계획
APC에 의해 강제된 계획	쿼리 변형 강제 계획	쿼리 변형 강제 계획	쿼리 변형 강제 계획	쿼리 변형 강제 계획
힌트 또는 강제 계획 없음	부모 사용자의 힌트	힌트 없음	작업 없음	작업 없음

¹ 자동 조정 기능의 자동 계획 수정 구성 요소

확장 이벤트

parameter_sensitive_plan_optimization_skipped_reason: 매개 변수 중요한 계획 기능을 건너뛸 때 발생합니다. 이 이벤트를 사용하여 PSP 최적화를 건너뛰는 이유를 모니터링합니다.

다음 쿼리는 PSP를 건너뛴 모든 가능한 이유를 보여 줍니다.
```
SELECT name, map_value FROM sys.dm_xe_map_values WHERE name ='psp_skipped_reason_enum' ORDER BY map_key;
```
parameter_sensitive_plan_optimization: 쿼리에서 PSP 최적화 기능을 사용할 때 발생합니다. 디버그 채널만 해당합니다. 관심 있는 일부 필드는 다음과 같습니다.
- is_query_variant: 디스패처 계획(부모) 또는 쿼리 변형 계획(자식)인지 설명합니다.
- predicate_count: PSP에서 선택한 조건자 수
- query_variant_id: 쿼리 변형 ID를 표시합니다. 값이 0이면 개체가 디스패처 계획(부모)입니다.

SQL Server 감사 동작

PSP 최적화는 디스패처 계획 문 및 디스패처와 연결된 모든 쿼리 변형에 대한 감사 데이터를 제공합니다. SQL Server 감사 내의 열은 additional_information 쿼리 변형에 대한 적절한 T-SQL 스택 정보도 제공합니다. 데이터베이스를 MyNewDatabase 예로 사용하여 이 데이터베이스에 이름이 있는 T2usp_test테이블과 저장 프로시저가 있는 경우 usp_test 저장 프로시저를 실행한 후 감사 로그에 다음 항목이 포함될 수 있습니다.

action_id	object_name	statement	additional_information
AUSC			`<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit_Testing_Psp$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info>`
EX	usp_test	exec usp_test 300
SL	T2	select * from dbo.t2 where ID=@id	`<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`
SL	T2	select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 1, predicate_range([MyNewDatabase].[ dbo]. [T2]. [ID] = @id, 100.0, 100000.0)))	`tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`
EX	usp_test	exec usp_test 60000
SL	T2	select * from dbo.t2 where ID=@id	`<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`
SL	T2	select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 3, predicate_range([TestDB_FOR_PSP].[ dbo]. [T2]. [ID] = @id, 100.0, 100000.0)))	`<tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack>`

알려진 문제

문제	발견된 날짜	상태	해결된 날짜
액세스 위반 예외는 특정 조건에서 SQL Server 2022(16.x)의 쿼리 저장소 발생합니다. PSP 최적화 쿼리 저장소 통합을 사용하는 경우 액세스 위반 예외가 발생할 수 있습니다. 자세한 내용은 매개 변수 중요한 계획 최적화의 업데이트를 참조하세요.	2023년 3월	해결됨	2023년 8월(CU 7)

해결됨

SQL Server 2022의 쿼리 저장소 특정 조건에서 액세스 위반 예외가 발생합니다.

참고 항목

SQL Server 2022(16.x) 누적 업데이트 7부터 액세스 위반으로 이어질 수 있는 경합 상태에 대한 몇 가지 수정 사항이 릴리스되었습니다. SQL Server 2022(16.x)용 누적 업데이트 7을 적용한 후 쿼리 저장소 통합을 사용한 PSP 최적화와 관련된 액세스 위반이 발생하는 경우 다음 해결 방법을 고려하세요.

이 문제는 실행된 쿼리에 대한 런타임 통계가 메모리 클럭에 있는 쿼리 저장소 메모리 내 표현에서 쿼리 저장소 디스크 버전으로 유지될 때 발생할 수 있는 MEMORYCLERK_QUERYDISKSTORE_HASHMAP 경합 상태 때문에 발생합니다. 런타임 통계로 표시된 런타임 통계는 문 옵션SET QUERY_STORE(기본값은 15분)으로 DATA_FLUSH_INTERVAL_SECONDS 정의된 일정 기간 동안 메모리에 유지됩니다. Management Studio 쿼리 저장소 대화 상자를 사용하여 내부적으로 초로 변환되는 데이터 플러시 간격(분)의 값을 입력할 수 있습니다. 시스템이 메모리 압력을 받고 있는 경우 런타임 통계를 옵션으로 정의된 것보다 일찍 디스크로 DATA_FLUSH_INTERVAL_SECONDS 플러시할 수 있습니다. 쿼리 저장소 쿼리 계획 클린up(즉, STALE_QUERY_THRESHOLD_DAYS 쿼리 저장소 옵션)과 MAX_STORAGE_SIZE_MB 관련된 추가 쿼리 저장소 백그라운드 스레드가 쿼리 저장소 쿼리의 경우 쿼리 변형 및/또는 관련 디스패처 문이 조기에 역참조될 수 있는 시나리오가 있습니다. 이로 인해 쿼리 변형을 쿼리 저장소 삽입하거나 삭제하는 동안 액세스 위반이 발생할 수 있습니다.

쿼리 저장소 작업에 대한 자세한 내용은 쿼리 저장소 데이터 수집 방법 문서의 설명 섹션을 참조하세요.

해결 방법: SQL Server 2022(16.x)에 대해 누적 업데이트 7을 적용한 후 PSP 통합이 설정된 쿼리 저장소 시스템에서 액세스 위반이 계속 발생하는 경우 추가 수정 사항을 사용할 수 있을 때까지 쿼리 저장소 있는 쿼리 변형을 제거하거나 쿼리 또는 데이터베이스 수준에서 PSP 기능을 일시적으로 사용하지 않도록 설정할 수 있습니다.

데이터베이스 수준에서 PSP 최적화를 사용하지 않도록 설정하려면 ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF 데이터베이스 범위 구성을 사용합니다.
쿼리 수준에서 PSP 최적화를 사용하지 않도록 설정하려면 DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION 쿼리 힌트를 사용합니다.

sys.query_store_query_variant(Transact-SQL) 카탈로그 뷰에 표시되는 쿼리 변형뿐만 아니라 쿼리 저장소 모든 쿼리 변형을 제거하려면 다음과 유사한 쿼리를 사용할 수 있습니다. 문제가 발생한 적절한 데이터베이스로 바꿉 [<database>] 다.

USE master;
GO

--Temporarily turn Query Store off in order to remove query variant plans as well as to
--clear the Query Store in-memory representation of Query Store (HashMap) for a particular database
ALTER DATABASE [<database>] SET QUERY_STORE = OFF;
GO

USE [<database>];
GO

DECLARE @QueryIDsCursor CURSOR;
DECLARE @QueryID BIGINT;
BEGIN
 -- Getting the cursor for query IDs for query variant plans
    SET @QueryIDsCursor = CURSOR FAST_FORWARD FOR
    SELECT query_id
        FROM sys.query_store_plan
    WHERE plan_type = 2 --query variant plans
    ORDER BY query_id;
 
 -- Using a non-set based method for this example query
    OPEN @QueryIDsCursor
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
        WHILE @@FETCH_STATUS = 0
    BEGIN

 -- Deleting query variant(s) from the query store
        EXEC sp_query_store_remove_query @query_id = @QueryID;
        FETCH NEXT FROM @QueryIDsCursor
        INTO @QueryID
    END;
    CLOSE @QueryIDsCursor ;
    DEALLOCATE @QueryIDsCursor;
END;

--Turn Query Store back on
ALTER DATABASE [<database>] SET QUERY_STORE = ON;
GO

쿼리 저장소 크거나 시스템에 쿼리 저장소 캡처할 수 있는 상당한 워크로드 및/또는 높은 수의 임시 매개 변수가 없는 쿼리가 있는 경우 쿼리 저장소 해제하는 데 다소 시간이 걸릴 수 있습니다. 이러한 시나리오에서 쿼리 저장소 강제로 해제하려면 이전 샘플 T-SQL에서 대신 명령을 사용합니다ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED). 매개 변수가 없는 쿼리를 찾으려면 쿼리 저장소 매개 변수가 없는 쿼리 찾기를 참조하세요.