SQL 데이터베이스의 적응 쿼리 처리Adaptive query processing in SQL databases

이 항목 적용 대상: 예SQL Server(2017부터 시작)예Azure SQL Database아니요Azure SQL Data Warehouse 아니요병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2017)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

이 문서에서는 SQL Server 및 Azure SQL Database에서 쿼리 성능을 향상시키는 데 사용할 수 있는 다음과 같은 적응 쿼리 처리 기능을 소개합니다.This article introduces these adaptive query processing features that you can use to improve query performance in SQL Server and Azure SQL Database:

  • 일괄 처리 모드 메모리 부여 피드백Batch mode memory grant feedback.
  • 일괄 처리 모드 적응 조인Batch mode adaptive join.
  • 인터리브 실행Interleaved execution.

일반 수준에서 SQL Server는 다음과 같이 쿼리를 실행합니다.At a general level, SQL Server executes a query as follows:

  1. 쿼리 최적화 프로세스는 특정 쿼리에 대한 가능한 실행 계획 집합을 생성합니다.The query optimization process generates a set of feasible execution plans for a specific query. 이 시간 동안 계획 옵션의 비용이 추정되고 예상 비용이 가장 낮은 계획이 사용됩니다.During this time, the cost of plan options is estimated and the plan with the lowest estimated cost is used.
  2. 쿼리 실행 프로세스는 쿼리 최적화 프로그램에서 선택된 계획을 사용하고 실행에 이용합니다.The query execution process takes the plan chosen by the query optimizer and uses it for execution.

쿼리 최적화 프로그램에서 선택된 계획이 여러 가지 이유로 적합하지 않은 경우도 있습니다.Sometimes the plan chosen by the query optimizer is not optimal for a variety of reasons. 예를 들어 쿼리 계획을 통해 이동하는 예상 행 수가 잘못되었을 수 있습니다.For example, the estimated number of rows flowing through the query plan may be incorrect. 예상 비용은 실행에 이용하도록 선택되는 계획을 결정하는 데 도움이 됩니다.The estimated costs help determine which plan gets selected for use in execution. 카디널리티 예상치가 잘못된 경우 원래 추정이 부실해도 원래 계획이 사용됩니다.If cardinality estimates are incorrect, the original plan is still used despite the poor original assumptions.

적응 쿼리 처리 기능

적응 쿼리 처리를 사용하도록 설정하는 방법How to enable adaptive query processing

데이터베이스에 대해 호환성 수준 140을 사용하도록 설정하여 워크로드가 적응 쿼리 처리에 자동으로 적합하도록 만들 수 있습니다.You can make workloads automatically eligible for adaptive query processing by enabling compatibility level 140 for the database. Transact-SQL을 사용하여 설정할 수 있습니다.You can set this using Transact-SQL. 예를 들어For example:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

일괄 처리 모드 메모리 부여 피드백Batch mode memory grant feedback

SQL Server의 쿼리 실행 후 계획에는 실행에 필요한 최소 필수 메모리 및 모든 행을 메모리에 포함하기 위한 이상적인 메모리 부여 크기가 포함됩니다.A query’s post-execution plan in SQL Server includes the minimum required memory needed for execution and the ideal memory grant size to have all rows fit in memory. 메모리 부여 크기가 잘못 지정된 경우 성능이 저하됩니다.Performance suffers when memory grant sizes are incorrectly sized. 과도하게 부여하면 메모리가 낭비되고 동시성이 줄어듭니다.Excessive grants result in wasted memory and reduced concurrency. 메모리 부여가 부족하면 디스크로 분산되어 비용이 증가합니다.Insufficient memory grants cause expensive spills to disk. 반복 워크로드를 처리함으로써 일괄 처리 모드 메모리 부여 피드백은 쿼리에 필요한 실제 메모리를 다시 계산한 후 캐시된 계획에 대한 부여 값을 업데이트합니다.By addressing repeating workloads, batch mode memory grant feedback recalculates the actual memory required for a query and then updates the grant value for the cached plan. 동일한 쿼리 문을 실행할 경우 쿼리는 수정된 메모리 부여 크기를 사용하여 동시성에 영향을 주는 과도한 메모리 부여를 줄이고 디스크로 분산하여 비용을 늘리는 부족한 메모리 부여를 수정합니다.When an identical query statement is executed, the query uses the revised memory grant size, reducing excessive memory grants that impact concurrency and fixing underestimated memory grants that cause expensive spills to disk. 다음 그래프에서는 일괄 처리 모드 적응 메모리 부여 피드백을 사용하는 한 가지 예를 보여 줍니다.The following graph shows one example of using batch mode adaptive memory grant feedback. 쿼리를 처음 실행하는 경우 높은 분산으로 인해 기간이 88초였습니다.For the first execution of the query, duration was 88 seconds due to high spills:

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';
SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime and @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

높은 분산

메모리 부여 피드백을 사용하여 두 번째로 실행하는 경우 기간이 1초(88초에서 감소)이고 분산이 완전히 제거되며 부여가 더 높습니다.With memory grant feedback enabled, for the second execution, duration is 1 second (down from 88 seconds), spills are removed entirely, and the grant is higher:

분산 없음

메모리 부여 피드백 크기 조정Memory grant feedback sizing

과도한 부여의 경우 부여된 메모리가 실제로 사용된 메모리 크기의 두 배를 초과하면 메모리 부여 피드백이 메모리 부여를 다시 계산하고 캐시된 계획을 업데이트합니다.For excessive grants, if the granted memory is more than two times the size of the actual used memory, memory grant feedback will recalculate the memory grant and update the cached plan. 메모리 부여가 1MB 미만인 계획은 초과분에 대해 다시 계산되지 않습니다.Plans with memory grants under 1 MB will not be recalculated for overages. 크기가 부족한 메모리 부여의 경우 일괄 처리 모드 연산자에 대해 디스크로 분산이 발생하며 메모리 부여 피드백이 메모리 부여 다시 계산을 트리거합니다.For insufficiently sized memory grants that result in a spill to disk for batch mode operators, memory grant feedback will trigger a recalculation of the memory grant. 분산 이벤트는 메모리 부여 피드백에 보고되며, spilling_report_to_memory_grant_feedback XEvent 이벤트를 통해 표시될 수 있습니다.Spill events are reported to memory grant feedback and can be surfaced via the spilling_report_to_memory_grant_feedback XEvent event. 이 이벤트는 계획의 노드 ID와 해당 노드의 분산 데이터 크기를 반환합니다.This event returns the node id from the plan and spilled data size of that node.

메모리 부여 피드백 및 매개 변수가 중요한 시나리오Memory grant feedback and parameter sensitive scenarios

최적 상태를 유지하려면 매개 변수 값마다 다른 쿼리 계획이 필요할 수도 있습니다.Different parameter values may also require different query plans in order to remain optimal. 이러한 유형의 쿼리를 "매개 변수가 중요한" 쿼리로 정의합니다.This type of query is defined as “parameter-sensitive.” 매개 변수가 중요한 계획의 경우 메모리 요구 사항이 불안정하면 쿼리에서 메모리 부여 피드백이 자동으로 비활성화됩니다.For parameter-sensitive plans, memory grant feedback will disable itself on a query if it has unstable memory requirements. 쿼리가 여러 번 반복 실행된 후 계획이 비활성화되며, memory_grant_feedback_loop_disabled XEvent를 모니터링하면 이를 관찰할 수 있습니다.The plan is disabled after several repeated runs of the query and this can be observed by monitoring the memory_grant_feedback_loop_disabled XEvent.

메모리 부여 피드백 캐싱Memory grant feedback caching

단일 실행에 대해 캐시된 계획에 피드백을 저장할 수 있습니다.Feedback can be stored in the cached plan for a single execution. 그러나 메모리 부여 피드백 조정의 혜택을 받는 것은 해당 문의 연속 실행입니다.It is the consecutive executions of that statement, however, that benefit from the memory grant feedback adjustments. 이 기능은 문의 반복 실행에 적용됩니다.This feature applies to repeated execution of statements. 메모리 부여 피드백은 캐시된 계획만 변경합니다.Memory grant feedback will change only the cached plan. 변경 내용은 현재 쿼리 Ssore에 캡처되지 않습니다.Changes are currently not captured in the query Ssore. 캐시에서 계획을 제거하면 피드백이 유지되지 않습니다.Feedback is not persisted if the plan is evicted from cache. 장애 조치(failover)가 있는 경우에도 피드백이 손실됩니다.Feedback will also be lost if there is a failover. OPTION(RECOMPILE)을 사용하는 문은 새 계획을 만들지만 캐시하지 않습니다.A statement using OPTION(RECOMPILE) creates a new plan and does not cache it. 캐시되지 않으므로 메모리 부여 피드백이 생성되지 않으며 해당 컴파일 및 실행을 위해 저장되지 않습니다.Since it is not cached, no memory grant feedback is produced and it is not stored for that compilation and execution. 그러나 OPTION(RECOMPILE)을 사용하지 않는 동등한 문(즉, 동일한 쿼리 해시 포함)을 캐시한 후 다시 실행하는 경우 연속된 문은 메모리 부여 피드백에서 혜택을 받을 수 있습니다.However, if an equivalent statement (that is, with the same query hash) that did not use OPTION(RECOMPILE) was cached and then re-executed, the consecutive statement can benefit from memory grant feedback.

메모리 부여 피드백 작업 추적Tracking memory grant feedback activity

memory_grant_updated_by_feedback XEvent 이벤트를 사용하여 메모리 부여 피드백 이벤트를 추적할 수 있습니다.You can track memory grant feedback events using the memory_grant_updated_by_feedback XEvent event. 이 이벤트는 현재 실행 횟수 기록, 메모리 부여 피드백이 계획을 업데이트한 횟수, 수정 전 적합한 추가 메모리 부여, 메모리 부여 피드백이 캐시된 계획을 수정한 후 적합한 추가 메모리 부여를 추적합니다.This event tracks the current execution count history, the number of times the plan has been updated by memory grant feedback, the ideal additional memory grant before modification and the ideal additional memory grant after memory grant feedback has modified the cached plan.

메모리 부여 피드백, 리소스 관리자 및 쿼리 힌트Memory grant feedback, resource governor and query hints

부여되는 실제 메모리는 리소스 관리자 또는 쿼리 힌트에 따른 쿼리 메모리 제한을 준수합니다.The actual memory granted honors the query memory limit determined by the resource governor or query hint.

일괄 처리 모드 적응 조인Batch mode adaptive joins

일괄 처리 모드 적응 조인 기능을 사용하면 해시 조인 또는 중첩된 루프 조인 메서드 선택을 첫 번째 입력이 검사된 까지 지연할 수 있습니다.The batch mode adaptive joins feature enables the choice of a hash join or nested loop join method to be deferred until after the first input has been scanned. 적응 조인 연산자는 중첩된 루프 계획으로 전환할 시기를 결정하는 데 사용되는 임계값을 정의합니다.The adaptive join operator defines a threshold that is used to decide when to switch to a nested loop plan. 따라서 계획이 실행 중에 더 나은 조인 전략으로 동적으로 전환할 수 있습니다.Your plan can therefore dynamically switch to a better join strategy during execution. 작동 방식은 다음과 같습니다.Here’s how it works:

  • 중첩된 루프 조인이 해시 조인보다 적합할 만큼 빌드 조인 입력의 행 수가 충분히 작으면 계획이 중첩된 루프 알고리즘으로 전환됩니다.If the row count of the build join input is small enough that a nested loop join would be more optimal than a hash join, your plan switches to a nested loop algorithm.
  • 빌드 조인 입력이 특정 행 수 임계값을 초과하면 전환이 발생하지 않으며 계획이 해시 조인을 계속 사용합니다.If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a hash join.

다음 쿼리는 적응 조인 예제를 설명하기 위해 사용됩니다.The following query is used to illustrate an adaptive join example:

SELECT  [fo].[Order Key], [si].[Lead Time Days],
[fo].[Quantity]
FROM    [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE   [fo].[Quantity] = 360;

이 쿼리는 336개의 행을 반환합니다.The query returns 336 rows. 활성 쿼리 통계를 사용하도록 설정하면 다음 계획이 표시됩니다.Enabling Live Query Statistics we see the following plan:

쿼리 결과 336개 행

계획에 다음이 표시됩니다.In the plan, we see the following:

  1. 해시 조인 빌드 단계에 대한 행을 제공하는 데 사용되는 columnstore 인덱스 검색이 있습니다.We have a columnstore index scan used to provide rows for the hash join build phase.
  2. 새 적응 조인 연산자가 있습니다.We have the new adaptive join operator. 이 연산자는 중첩된 루프 계획으로 전환할 시기를 결정하는 데 사용되는 임계값을 정의합니다.This operator defines a threshold that is used to decide when to switch to a nested loop plan. 이 예제에서 임계값은 78개 행입니다.For our example, the threshold is 78 rows. >= 78개 행이면 모두 해시 조인을 사용합니다.Anything with >= 78 rows will use a hash join. 임계값보다 작으면 중첩된 루프 조인이 사용됩니다.If less than the threshold, a nested loop join will be used.
  3. 336개 행을 반환하기 때문에 임계값을 초과하므로 두 번째 분기가 표준 해시 조인 작업의 프로브 단계를 나타냅니다.Since we return 336 rows, we are exceeding the threshold and so the second branch represents the probe phase of a standard hash join operation. 활성 쿼리 통계는 연산자를 통과하는 행(이 경우 "672/672")을 보여 줍니다.Notice that Live Query Statistics shows rows flowing through the operators – in this case “672 of 672”.
  4. 마지막 분기는 임계값을 초과하지 않을 경우 중첩된 루프 조인에서 사용하기 위한 Clustered Index Seek입니다.And the last branch is our Clustered Index Seek for use by the nested loop join had the threshold not been exceeded. "0/336"개 행이 표시됩니다(분기가 사용되지 않음).Notice that we see “0 of 336” rows displayed (the branch is unused). 이제 계획과 동일한 쿼리를 비교해 보겠습니다. 하지만 이번에는 테이블에 하나의 행만 있는 Quantity 값에 대해 쿼리합니다.Now let’s contrast the plan with the same query, but this time for a Quantity value that only has one row in the table:
SELECT  [fo].[Order Key], [si].[Lead Time Days],
[fo].[Quantity]
FROM    [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE   [fo].[Quantity] = 361;

쿼리가 하나의 행을 반환합니다.The query returns one row. 활성 쿼리 통계를 사용하도록 설정하면 다음 계획이 표시됩니다.Enabling Live Query Statistics we see the following plan:

쿼리 결과 하나의 행

계획에 다음이 표시됩니다.In the plan, we see the following:

  • 하나의 행이 반환되면 이제 Clustered Index Seek에 통과하는 행이 있음을 확인할 수 있습니다.With one row returned, you see the Clustered Index Seek now has rows flowing through it.
  • 해시 조인 빌드 단계를 계속 진행하지 않았으므로 두 번째 분기를 통과하는 행이 0개로 표시됩니다.And since we did not continue with the hash join build phase, you’ll see zero rows flowing through the second branch.

적응 조인 혜택Adaptive join benefits

작은 조인 입력 검색과 큰 조인 입력 검색 간에 자주 변동하는 워크로드가 이 기능에서 가장 큰 혜택을 받게 됩니다.Workloads with frequent oscillations between small and large join input scans will benefit most from this feature.

적응 조인 오버헤드Adaptive join overhead

적응 조인은 동등한 인덱스 중첩된 루프 조인 계획보다 메모리 요구 사항이 더 높습니다.Adaptive joins introduce a higher memory requirement than an index nested loop join equivalent plan. 중첩된 루프가 해시 조인인 것처럼 추가 메모리가 요청됩니다.The additional memory is requested as if the nested loop was a hash join. 동등한 중첩된 루프 스트리밍 조인에 비해 스탑앤고(stop-and-go) 작업으로서 빌드 단계에 대한 오버헤드도 있습니다.There is also overhead for the build phase as a stop-and-go operation versus a nested loop streaming equivalent join. 빌드 입력의 행 수가 변동될 수 있는 시나리오에서 해당 추가 비용과 함께 유연성이 제공됩니다.With that additional cost comes flexibility for scenarios where row counts may fluctuate in the build input.

적응 조인 캐싱 및 다시 사용Adaptive join caching and re-use

일괄 처리 모드 적응 조인은 문의 초기 실행에서 작동하며, 컴파일된 후에는 컴파일된 적응 조인 임계값과 외부 입력의 빌드 단계를 통과하는 런타임 행에 따라 연속 실행이 적응 상태로 유지됩니다.Batch mode adaptive joins work for the initial execution of a statement, and once compiled, consecutive executions will remain adaptive based on the compiled adaptive join threshold and the runtime rows flowing through the build phase of the outer input.

적응 조인 작업 추적Tracking adaptive join activity

적응 조인 연산자에는 다음과 같은 계획 연산자 특성이 있습니다.The adaptive join operator has the following plan operator attributes:

계획 특성Plan attribute DescriptionDescription
AdaptiveThresholdRowsAdaptiveThresholdRows 해시 조인에서 중첩된 루프 조인으로 전환하는 데 사용되는 임계값을 보여 줍니다.Shows the threshold use to switch from a hash join to nested loop join.
EstimatedJoinTypeEstimatedJoinType 가능한 조인 형식입니다.What the join type is likely to be.
ActualJoinTypeActualJoinType 실제 계획에서 임계값에 따라 최종적으로 선택된 조인 알고리즘을 보여 줍니다.In an actual plan, shows what join algorithm was ultimately chosen based on the threshold.

예상 계획은 정의된 적응 조인 임계값 및 예상 조인 형식과 함께 적응 조인 계획 모양을 보여 줍니다.The estimated plan shows the adaptive join plan shape, along with a defined adaptive join threshold and estimated join type.

적응 조인 및 쿼리 저장소 상호 운용성Adaptive join and Query Store interoperability

쿼리 저장소는 일괄 처리 모드 적응 조인 계획을 캡처하고 강제로 적용할 수 있습니다.Query Store captures and is able to force a batch mode adaptive join plan.

적응 조인 적합한 문Adaptive join eligible statements

논리 조인을 일괄 처리 모드 적응 조인에 적합하게 만드는 몇 가지 조건은 다음과 같습니다.A few conditions make a logical join eligible for a batch mode adaptive join:

  • 데이터베이스 호환성 수준이 140입니다.The database compatibility level is 140
  • 쿼리가 SELECT 문입니다(데이터 수정 문은 현재 적합하지 않음).The query is a SELECT statement (data modification statements are currently ineligible)
  • 인덱싱된 중첩된 루프 조인 또는 해시 조인 실제 알고리즘 둘 다에서 조인을 실행할 수 있습니다.The join is eligible to be executed both by an indexed nested loop join or a hash join physical algorithm
  • 해시 조인이 쿼리 전체의 Columnstore 인덱스 현재 상태를 통해 또는 조인에서 직접 참조되는 Columnstore 인덱싱된 테이블을 통해 일괄 처리 모드를 사용합니다.The hash join uses batch mode – either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join
  • 중첩된 루프 조인 및 해시 조인의 생성된 대체 솔루션에 동일한 첫 번째 자식(외부 참조)이 있어야 합니다.The generated alternative solutions of the nested loop join and hash join should have the same first child (outer reference)

적응 조인 및 중첩된 루프 효율성Adaptive joins and nested loop efficiency

적응 조인이 중첩된 루프 작업으로 전환하는 경우 해시 조인 빌드에서 이미 읽은 행이 사용됩니다.If an adaptive join switches to a nested loop operation, it uses the rows already read by the hash join build. 연산자는 외부 참조 행을 다시 읽지 않습니다.The operator does not re-read the outer reference rows again.

적응 임계값 행Adaptive threshold rows

다음 차트에서는 해시 조인 비용과 중첩된 루프 조인 대안 비용 간의 교차 예를 보여 줍니다.The following chart shows an example intersection between the cost of a hash join vs. the cost of a nested loop join alternative. 이 교차 지점에서 결정되는 임계값에 따라 다시 조인 작업에 사용되는 실제 알고리즘이 결정됩니다.At this intersection point, the threshold is determined that in turn determines the actual algorithm used for the join operation.

조인 임계값

다중 문 테이블 반환 함수에 대한 인터리브 실행Interleaved execution for multi-statement table valued functions

인터리브 실행을 사용하면 단일 쿼리 실행에 대한 최적화 및 실행 단계 사이의 단방향 경계가 변경되며 수정된 카디널리티 예상치에 따라 계획을 조정할 수 있습니다.Interleaved execution changes the unidirectional boundary between the optimization and execution phases for a single-query execution and enables plans to adapt based on the revised cardinality estimates. 최적화 중에 현재 MSTVF(다중 문 테이블 반환 함수)인 인터리브 실행 후보를 발견할 경우 최적화를 일시 중지하고, 해당 하위 트리를 실행하고, 정확한 카디널리티 예상치를 캡처한 다음 다운스트림 작업에 대해 최적화를 다시 시작합니다.During optimization if we encounter a candidate for interleaved execution, which is currently multi-statement table valued functions (MSTVFs), we will pause optimization, execute the applicable subtree, capture accurate cardinality estimates, and then resume optimization for downstream operations. SQL Server 2014 및 SQL Server 2016에서는 MSTVF에 대한 고정 카디널리티 추정이 "100"이고, 이전 버전에서는 "1"입니다.MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and “1” for earlier versions. 인터리브 실행은 다중 문 테이블 반환 함수와 연결된 이러한 고정 카디널리티 예상치 때문에 발생하는 워크로드 성능 문제에 도움이 됩니다.Interleaved execution helps workload performance issues that are due to these fixed cardinality estimates associated with multi-statement table valued functions.

다음 이미지에서는 MSTVF의 고정 카디널리티 예상치 영향을 보여 주는 전체 실행 계획의 하위 집합인 활성 쿼리 통계 출력을 보여 줍니다.The following image depicts a live query statistics ouput, a subset of an overall execution plan that shows the impact of fixed cardinality estimates from MSTVFs. 실제 행 흐름 및 예상 행 수를 확인할 수 있습니다.You can see the actual row flow vs. estimated rows. 중요한 세 가지 계획 영역은 다음과 같습니다(오른쪽에서 왼쪽으로 흐름).There are three noteworthy areas of the plan (flow is from right to left):

  1. MSTVF 테이블 검색의 고정 예상치는 100개 행입니다.The MSTVF Table Scan has a fixed estimate of 100 rows. 그러나 이 예제에서는 실제/예상인 "527597/100"을 통해 활성 쿼리 통계에 표시되는 것처럼 527,597개 행이 이 MSTVF 테이블 검색을 통과하므로 고정 예상치와 큰 차이가 있습니다.For this example, however, there are 527,597 rows flowing through this MSTVF Table Scan as seen in Live Query Statistics via the “527597 of 100” actual of estimated – so the fixed estimate is significantly skewed.
  2. 중첩된 루프 작업의 경우 100개의 행만 조인의 외부 측면에서 반환된다고 가정합니다.For the Nested Loops operation, only 100 rows are assumed to be returned by the outer side of the join. 실제로 MSTVF에서 반환되는 많은 행 개수를 고려할 때 다른 조인 알고리즘을 사용하는 것이 나을 수도 있습니다.Given the high number of rows actually being returned by the MSTVF, you are likely better off with a different join algorithm altogether.
  3. 해시 일치 작업의 경우 작은 경고 기호가 표시되며, 이 경우 디스크로 분산을 나타냅니다.For the Hash Match operation, notice the small warning symbol, which in this case is indicating a spill to disk.

행 흐름 및 예상 행 수

인터리브 실행을 사용하여 생성된 실제 계획과 이전 계획을 비교해 보세요.Contrast the prior plan with the actual plan generated with interleaved execution enabled:

인터리브 계획

  1. 이제 MSTVF 테이블 검색에 정확한 카디널리티 예상치가 반영됩니다.Notice that the MSTVF table scan now reflects an accurate cardinality estimate. 또한 이 테이블 검색 및 기타 작업의 순서 변경을 확인합니다.Also notice the re-ordering of this table scan and the other operations.
  2. 조인 알고리즘의 경우 중첩된 루프 작업에서 다수의 행이 관련된 경우에 더 적합한 해시 일치 작업으로 전환했습니다.And regarding join algorithms, we have switched from a Nested Loop operation to a Hash Match operation instead, which is more optimal given the large number of rows involved.
  3. 또한 MSTVF 테이블 검색에서 나오는 실제 행 수에 따라 더 많은 메모리를 다시 부여하기 때문에 분산 경고가 더 이상 표시되지 않습니다.Also notice that we no longer have spill-warnings, as we’re granting more memory based on the true row count flowing from the MSTVF table scan.

인터리브 실행 적합한 문Interleaved execution eligible statements

인터리브 실행의 MSTVF 참조 문은 현재 읽기 전용이어야 하며 데이터 수정 작업에 포함되면 안 됩니다.MSTVF referencing statements in interleaved execution must currently be read-only and not part of a data modification operation. 또한 MSTVF는 CROSS APPLY 내부에서 사용되는 경우 인터리브 실행에 적합하지 않습니다.Also, the MSTVFs are not be eligible for interleaved execution if they are used on the inside of a CROSS APPLY.

인터리브 실행 혜택Interleaved execution benefits

일반적으로 다운스트림 계획 작업 수와 더불어 실제 행 수와 예상치 간의 차이가 클수록 성능에 미치는 영향이 커집니다.In general, the higher the skew between the estimated vs. actual number of rows, coupled with the number of downstream plan operations, the greater the performance impact. 일반적으로 인터리브 실행은 다음과 같은 쿼리에 도움이 됩니다.In general, interleaved execution benefits queries where:

  1. 중간 결과 집합(이 경우 MSTVF)에 대한 실제 행 수와 예상치 간에 큰 차이가 있습니다.There is a large skew between the estimated vs. actual number of rows for the intermediate result set (in this case, the MSTVF), and…
  2. 전체 쿼리에서 중간 결과의 크기 변경이 중요합니다.…the overall query is sensitive to a change in the size of the intermediate result. 일반적으로 쿼리 계획의 하위 트리 위에 복잡한 트리가 있는 경우에 발생합니다.This typically happens when there is a complex tree above that subtree in the query plan. 단순한 MSTVF에서 "SELECT *"의 경우 인터리브 실행이 도움이 되지 않습니다.A simply "SELECT *" from an MSTVF will not benefit from interleaved execution.

인터리브 실행 오버헤드Interleaved execution overhead

오버헤드는 최소화되거나 없어야 합니다.The overhead should be minimal-to-none. MSTVF는 인터리브 실행이 도입되기 전에 이미 구체화되었지만, 이제 지연 최적화를 허용한 후 구체화된 행 집합의 카디널리티 예상치를 활용한다는 차이점이 있습니다.MSTVFs were already being materialized prior to the introduction of interleaved execution, however the difference is that now we’re now allowing deferred optimization and are then leveraging the cardinality estimate of the materialized row set. 계획에 영향을 주는 변경 내용과 마찬가지로, 일부 계획은 하위 트리의 카디널리티가 좋을수록 전체 쿼리에 대한 계획이 더 나빠지도록 변경될 수 있습니다.As with any plan affecting changes, some plans could change such that with better cardinality for the subtree we get a worse plan for the query overall. 완화에는 호환성 수준 되돌리기, 쿼리 저장소를 사용하여 비회귀 버전의 계획 강제 적용 등이 포함될 수 있습니다.Mitigation can include reverting the compatibility level or using Query Store to force the non-regressed version of the plan.

인터리브 실행 및 연속 실행Interleaved execution and consecutive executions

인터리브 실행 계획이 캐시되고 나면 첫 번째 실행에서 수정된 예상치가 있는 계획이 인터리브 실행을 다시 인스턴스화하지 않고 연속 실행에 사용됩니다.Once an interleaved execution plan is cached, the plan with the revised estimates on the first execution is used for consecutive executions without re-instantiating interleaved execution.

인터리브 실행 작업 추적Tracking interleaved execution activity

실제 쿼리 실행 계획에서 다음 사용 특성을 확인할 수 있습니다.You can see usage attributes in the actual query execution plan:

계획 특성Plan attribute DescriptionDescription
ContainsInterleavedExecutionCandidatesContainsInterleavedExecutionCandidates QueryPlan 노드에 적용할 때 "true"이면 계획에 인터리브 실행 후보가 포함됩니다.Applying to the QueryPlan node, when “true”, it means the plan contains interleaved execution candidates.
IsInterleavedExecutedIsInterleavedExecuted 이 특성은 TVF 노드에 대한 RelOp 아래의 RuntimeInformation 요소 내부에 있습니다.The attribute is inside the RuntimeInformation element under the RelOp for the TVF node. "true"이면 작업이 인터리브 실행 작업의 일부로 구체화된 것입니다.When “true”, it means the operation was materialized as part of an interleaved execution operation.

다음과 같은 XEvent를 통해 인터리브 실행 발생을 추적할 수도 있습니다.You can also track interleaved execution occurrences via the following XEvents:

XEventXEvent DescriptionDescription
interleaved_exec_statusinterleaved_exec_status 이 이벤트는 인터리브 실행 시 발생합니다.This event fires when interleaved execution is occurring.
interleaved_exec_stats_updateinterleaved_exec_stats_update 이 이벤트는 카디널리티 예상치가 인터리브 실행에 의해 업데이트되었음을 설명합니다.This event describes the cardinality estimates updated by interleaved execution.
Interleaved_exec_disabled_reasonInterleaved_exec_disabled_reason 이 이벤트는 가능한 인터리브 실행 후보를 사용한 쿼리가 실제로 인터리브 실행되지 않는 경우에 발생합니다.This event fires when a query with a possible candidate for interleaved execution does not actually get interleaved execution.

인터리브 실행을 통해 MSTVF 카디널리티 예상치를 수정할 수 있으려면 쿼리를 실행해야 합니다.A query must be executed in order to allow interleaved execution to revise MSTVF cardinality estimates. 그러나 예상 실행 계획은 ContainsInterleavedExecutionCandidates 특성을 통해 인터리브 실행 후보가 있는 경우를 보여 줍니다.However, the estimated execution plan still shows when there are interleaved execution candidates via the ContainsInterleavedExecutionCandidates attribute.

인터리브 실행 캐싱Interleaved execution caching

캐시에서 계획을 지우거나 제거한 경우 쿼리 실행 시 인터리브 실행을 사용하는 새 컴파일이 있습니다.If a plan is cleared or evicted from cache, upon query execution there is a fresh compilation that uses interleaved execution. OPTION(RECOMPILE)을 사용하는 문은 인터리브 실행을 사용하는 새 계획을 만들고 캐시하지 않습니다.A statement using OPTION(RECOMPILE) will create a new plan using interleaved execution and not cache it.

인터리브 실행 및 쿼리 저장소 상호 운용성Interleaved execution and query store interoperability

인터리브 실행을 사용하는 계획을 강제로 적용할 수 있습니다.Plans using interleaved execution can be forced. 계획은 초기 실행에 따라 카디널리티 예상치를 수정한 버전입니다.The plan is the version that has corrected cardinality estimates based on initial execution.

관련 항목:See Also

SQL Server 데이터베이스 엔진 및 Azure SQL Database에 대한 성능 센터Performance Center for SQL Server Database Engine and Azure SQL Database

쿼리 처리 아키텍처 가이드Query Processing Architecture Guide

Demonstrating Adaptive Query Processing(적응 쿼리 처리 시연)Demonstrating Adaptive Query Processing