매개 변수 중요한 계획 최적화
적용 대상: 
SQL Server 2022(16.x) 이상 버전, Azure SQL 데이터베이스, Azure SQL Managed Instance
PSP(매개 변수 민감도 계획) 최적화는 지능형 쿼리 처리 기능 제품군의 일부입니다. 매개 변수가 있는 쿼리에 대해 캐시된 단일 계획이 가능한 모든 들어오는 매개 변수 값에 최적이 아닌 시나리오를 해결합니다. 균일하지 않은 데이터 배포의 경우입니다. 자세한 내용은 매개 변수 민감도와 매개 변수 및 실행 계획 재사용을 참조하세요.
이 문제 시나리오의 기존 해결 방법에 대한 자세한 내용은 다음을 참조하세요.
PSP 최적화는 매개 변수가 있는 단일 문에 대해 활성 캐시된 여러 계획을 자동으로 사용하도록 설정합니다. 캐시된 실행 계획은 고객이 제공한 런타임 매개 변수 값에 따라 다른 데이터 크기를 수용합니다.
매개 변수화 이해
SQL Server 데이터베이스 엔진에서는 Transact-SQL(T-SQL) 문에 매개 변수 또는 매개 변수 표식을 사용하여 새 T-SQL 문을 이전에 컴파일된 기존의 실행 계획과 일치시키는 관계형 엔진의 성능을 향상시키고 계획 재활용을 승격합니다. 자세한 내용은 단순 매개 변수화를 참조하세요.
데이터베이스의 모든 SELECT, INSERT, UPDATE, DELETE 문이 특정 제한에 따라 매개 변수화되도록 지정하여 SQL Server의 기본 단순 매개 변수화 동작을 무시할 수도 있습니다. 자세한 내용은 강제 매개 변수화를 참조하세요.
PSP 최적화 구현
초기 컴파일 중에 열 통계 히스토그램은 균일하지 않은 분포를 식별하고 사용 가능한 모든 조건자 중 최대 3개까지 가장 위험에 처한 매개 변수가 있는 조건자를 평가합니다. 즉, 동일한 쿼리 내의 여러 조건자가 조건을 충족하는 경우 PSP 최적화는 상위 3개를 선택합니다. PSP 기능은 너무 많은 계획으로 계획 캐시 및 쿼리 저장소(쿼리 저장소 사용하도록 설정된 경우)를 블로트하는 것을 방지하기 위해 평가되는 조건자 수를 제한합니다.
적격 계획의 경우 초기 컴파일은 디스패처 식이라는 PSP 최적화 논리를 포함하는 디스패처 계획을 생성합니다. 디스패처 계획은 카디널리티 범위 경계 값 조건자를 기반으로 하는 쿼리 변형에 매핑됩니다.
용어
디스패처 식
런타임 매개 변수 값을 기반으로 조건자의 카디널리티를 평가하고 다른 쿼리 변형으로 실행을 라우팅합니다.
디스패처 계획
디스패처 식을 포함하는 계획이 원래 쿼리에 대해 캐시됩니다. 디스패처 계획은 기본적으로 몇 가지 추가 세부 정보와 함께 기능에서 선택한 조건자의 컬렉션입니다. 선택한 각 조건자에 대해 디스패처 계획에 포함된 세부 정보 중 일부는 높은 경계 값과 낮은 경계 값입니다. 이러한 값은 매개 변수 값을 다른 버킷 또는 범위로 나누는 데 사용됩니다. 디스패처 계획에는 경계 값을 계산하는 데 사용된 통계도 포함됩니다.
쿼리 변형
디스패처 계획은 런타임 매개 변수 값을 기반으로 조건자의 카디널리티를 평가하므로 해당 값을 버킷화하고 컴파일하고 실행할 별도의 자식 쿼리를 생성합니다. 이러한 자식 쿼리는 쿼리 변형이라고 합니다. 쿼리 변형에는 계획 캐시 및 쿼리 저장소에 자체 계획이 있습니다.
조건자 카디널리티 범위
런타임 시 각 조건자의 카디널리티는 런타임 매개 변수 값에 따라 평가됩니다. 디스패처는 카디널리티 값을 컴파일 시간에 세 가지 조건자 카디널리티 범위로 버킷화합니다. 예를 들어 PSP 최적화 기능은 다음 다이어그램과 같이 낮음, 중간 및 높은 카디널리티 범위를 나타내는 세 가지 범위를 만들 수 있습니다.
즉, 매개 변수가 있는 쿼리가 처음 컴파일되면 PSP 최적화 기능은 디스패처 계획이라는 셸 계획을 생성합니다. 디스패처 식에는 매개 변수의 런타임 값에 따라 쿼리를 쿼리 변형으로 버킷화하는 논리가 있습니다. 실제 실행이 시작되면 디스패처는 다음 두 단계를 수행합니다.
디스패처는 지정된 매개 변수 집합에 대한 디스패처 식을 평가하여 카디널리티 범위를 계산합니다.
디스패처는 이러한 범위를 특정 쿼리 변형에 매핑하고 변형을 컴파일하고 실행합니다. 여러 쿼리 변형이 있으므로 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개의 쿼리 변형 범위가 있습니다.
- 조건자 범위는 디스패처 식에서 생성된 조건자 카디널리티 범위 정보입니다.
또한 쿼리 변형의 ShowPlan XML 내에서(Dispatcher 요소 내부):
<Batch>
<Statements>
<StmtSimple StatementText="SELECT PropertyId,
 AgentId,
 MLSLinkId,
 ListingPrice,
 ZipCode,
 Bedrooms,
 Bathrooms
FROM dbo.Property
WHERE AgentId = @AgentId
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테이블에서 온 것이기 때문에 가장 왜곡된 조건자만 기능에 의해 평가됩니다. 그러나 예제 쿼리에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, 
 AgentId, 
 MLSLinkId, 
 ListingPrice, 
 ZipCode, 
 Bedrooms, 
 Bathrooms 
FROM dbo.AgentProperty a join PropertyDetails b on a.PropertyId = b.PropertyId
WHERE AgentId = @AgentId and Property_id=@Property_id
UNION
 SELECT c.PropertyId, 
 AgentId, 
 MLSLinkId, 
 ListingPrice, 
 ZipCode, 
 Bedrooms, 
 Bathrooms 
FROM dbo.AgentProperty c join PropertyDetails d on c.PropertyId = d.PropertyId
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 최적화 기능에서 사용하는 현재 기울이기 임계값에 영향을 줄 수 있습니다.
추적 플래그 9481(전역, 세션 또는 쿼리 수준)과 같은 CE(카디널리티예측 도구) 추적 플래그
사용 중인 CE 모델을 낮추거나 CE 모델이 여러 조건자의 독립성과 관련하여 가정하는 데 영향을 주려는 데이터베이스 범위 구성 옵션입니다. 이는 다중 열 통계가 존재하지 않는 경우 특히 유용하며, PSP 최적화에서 해당 조건자의 후보를 평가하는 능력에 영향을 줍니다.
자세한 내용은 SQL Server 2014 카디널리티 예측 도구로 쿼리 계획 최적화 백서의 여러 조건자에 대한 상관 관계 가정 증가 섹션을 참조하세요. 최신 CE 모델은 조건자의 결합 및 분리에 대한 몇 가지 상관 관계와 보다 낮은 독립성을 가정하려고 시도합니다. 레거시 CE 모델을 사용하면 다중 열 조인 시나리오에서 조건자의 선택성을 계산하는 방법에 영향을 줄 수 있습니다. 이 작업은 특정 시나리오에 대해서만 고려해야 하며, 대부분의 워크로드에 레거시 CE 모델을 사용하지 않는 것이 좋습니다.
PSP 최적화는 현재 각 쿼리 변형을 새 준비된 문으로 컴파일하고 실행합니다. 이는 디스패처 계획이 모듈(즉, 저장 프로시저, 트리거, 함수, 뷰 등)을 기반으로 하는 경우 쿼리 변형이 부모 모듈의
object_id와의 연결을 잃는 이유 중 하나입니다. 준비된 문으로,object_id는sys.objects의 개체에 직접 매핑할 수 있는 것은 아니지만 기본적으로 일괄 처리 텍스트의 내부 해시를 기반으로 계산된 값입니다. 자세한 내용은sys.dm_exec_plan_attributesDMV 설명서의 반환된 테이블 섹션을 참조하세요.쿼리 변형 계획은 계획 캐시 개체 저장소(
CACHESTORE_OBJCP)에 배치되고 디스패처 계획은 SQL Plans 캐시 저장소(CACHESTORE_SQLCP)에 배치됩니다. 하지만 PSP 기능은 부모 쿼리가 동적 또는 임시 T-SQL이 아닌 모듈의 일부인 경우 PSP가 ShowPlan XML에 추가하는 PLAN PER VALUE 힌트의 일부인 ObjectID 특성 내에 쿼리 변형 부모의object_id를 저장합니다. 캐시된 프로시저, 함수 및 트리거에 대한 집계 성능 통계는 해당 용도로 계속 사용될 수 있습니다.sys.dm_exec_query_statsDMV와 유사한 보기에 있는 것과 같은 더 세부적인 실행 관련 통계에는 여전히 쿼리 변형에 대한 데이터가 포함되어 있지만,sys.objects테이블 내에 있는 쿼리 변형의object_id과 개체 간의 연결은 더 세분화된 런타임 통계가 필요한 각 쿼리 변형에 대한 ShowPlan XML의 추가 처리 없이는 현재 정렬되지 않습니다. 쿼리 저장소르 사용하도록 설정된 경우 추가 ShowPlan XML 구문 분석 기술 없이도 쿼리 변형에 대한 런타임 및 대기 통계 정보를 쿼리 저장소에서 가져올 수 있습니다.PSP 쿼리 변형은 새 준비된 문으로 실행되므로, ShowPlan XML을 파쇄하고 텍스트 패턴 일치 기술(즉, 추가 XQuery 처리)을 적용하지 않으면 다양한 계획 캐시 관련
sys.dm_exec_*DMV에object_id가 자동으로 노출되지 않습니다. 현재 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_OBJCP및CACHESTORE_SQLCP캐시 저장소의 크기가 과도한 경우 추적 플래그 174를 적용하는 것이 좋습니다.쿼리 저장소에서 쿼리에 대해 저장된 고유한 계획 변형의 수는
max_plans_per_query구성 옵션으로 제한됩니다. 쿼리 변형에 2개 이상의 계획이 있을 수 있으므로, 쿼리 저장소 내의 쿼리당 총 200개의 계획이 있을 수 있습니다. 이 수에는 부모 쿼리에 속하는 모든 디스패처에 대한 모든 쿼리 변형 계획이 포함됩니다.max_plans_per_query쿼리 저장소 구성 옵션을 늘리는 것이 좋습니다.- 고유한 계획 수가 어떻게 기본 쿼리 저장소
max_plans_per_query한도를 초과할 수 있는지 보여 주는 예는 다음과 같은 동작이 있는 시나리오입니다. 쿼리 ID가 10인 쿼리가 있는데 디스패처 계획이 2개이고, 각 디스패처 계획에는 각각 20개의 쿼리 변형(총 40개의 쿼리 변형)이 있다고 가정해 보겠습니다. 쿼리 ID 10의 총 계획 수는 쿼리 변형에 대한 40개의 계획과 2개의 디스패처 계획입니다. 부모 쿼리 자체(쿼리 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 기능이RECOMPILE힌트가 있는 쿼리에서 작동하지 않으므로 기존 쿼리 변형 계획이 계획 캐시에서 제거된 후 시스템에서 PSP가 아닌 계획을 생성합니다.쿼리 저장소 힌트 결과는 확장 이벤트
query_store_hints_application_success및query_store_hints_application_failed이벤트를 사용하여 관찰할 수 있습니다. sys.query_store_query_hints 테이블의 경우 적용된 쿼리 힌트에 대한 정보가 포함됩니다. 부모 쿼리에만 힌트가 적용된 경우 시스템 카탈로그에는 부모 쿼리에 대한 힌트 정보가 포함되지만, 자식 쿼리에 대한 힌트 정보는 포함되지 않고 자식 쿼리는 부모 쿼리의 힌트를 상속합니다.
다음 표에는 쿼리 힌트 및 계획 강제 동작이 있는 PSP가 요약되어 있습니다.
| 쿼리 변형 힌트 또는 계획 | 부모에 사용자 적용 힌트가 있음 | 부모에 피드백 적용 힌트가 있음 | 부모에 수동으로 강제 적용된 계획이 있음 | 부모에 APC 1 강제 계획이 있음 |
|---|---|---|---|---|
| 사용자를 통한 힌트 | 쿼리 변형 힌트 | 쿼리 변형 힌트 | 쿼리 변형 힌트 | 해당 없음 |
| 피드백을 통한 힌트 | 쿼리 변형 힌트 | 쿼리 변형 힌트 | 쿼리 변형 힌트 | 해당 없음 |
| 사용자가 강제 적용한 계획 | 쿼리 변형 강제 계획 |
쿼리 변형 강제 계획 |
쿼리 변형 강제 계획 |
쿼리 변형 강제 계획 |
| APC가 강제 적용한 계획 | 쿼리 변형 강제 계획 |
쿼리 변형 강제 계획 |
쿼리 변형 강제 계획 |
쿼리 변형 강제 계획 |
| 힌트 또는 강제 계획 없음 | 부모 사용자의 힌트 | 힌트 없음 | 작업 없음 | 작업 없음 |
1 자동 조정 기능의 자동 계획 수정 구성 요소
확장 이벤트
parameter_sensitive_plan_optimization_skipped_reason: 매개 변수 중요한 계획 기능을 건너뛸 때 발생합니다. 이 이벤트를 사용하여 PSP 최적화를 건너뛰는 이유를 모니터링할 수 있습니다.다음 쿼리는 PSP를 건너뙤는 이유를 모두 보여 줍니다.
SELECT 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 데이터베이스를 예로 사용하여 이 데이터베이스에 T2라는 테이블과 usp_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 메모리 클럭에 있는 쿼리 저장소 메모리 내 표현에서 쿼리 저장소 디스크 버전으로 유지될 때 발생할 수 있는 경합 상태 때문에 발생합니다. 런타임 통계로 표시된 런타임 통계는 DATA_FLUSH_INTERVAL_SECONDS 문의 SET QUERY_STORE 옵션(기본값 15분)으로 정의된 기간 동안 메모리에 유지됩니다. Management Studio 쿼리 저장소 대화 상자를 사용하여 내부적으로 초로 변환되는 데이터 플러시 간격(분)의 값을 입력할 수 있습니다. 메모리 부족 시 런타임 통계는 DATA_FLUSH_INTERVAL_SECONDS 옵션으로 정의된 것보다 먼저 디스크에 플러시될 수 있습니다. 쿼리 저장소 쿼리 계획 정리(즉, 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) 명령을 사용합니다. 매개 변수가 없는 쿼리를 찾으려면 쿼리 저장소 매개 변수가 없는 쿼리 찾기를 참조하세요.