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

이 항목은 다음에 적용됩니다. 예SQL Server 예Azure SQL Database아니요Azure SQL Data Warehouse 아니요병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

이 문서에서는 SQL ServerSQL Server (SQL Server 2017(14.x)SQL Server 2017 (14.x)부터 시작) 및 SQL DatabaseSQL Database에서 쿼리 성능을 향상시키는 데 사용할 수 있는 다음과 같은 적응 쿼리 처리 기능을 소개합니다.This article introduces these adaptive query processing features that you can use to improve query performance in SQL ServerSQL Server (starting with SQL Server 2017(14.x)SQL Server 2017 (14.x)) and SQL DatabaseSQL 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 ServerSQL Server의 쿼리 실행 후 계획에는 실행에 필요한 최소 필수 메모리 및 모든 행을 메모리에 포함하기 위한 이상적인 메모리 부여 크기가 포함됩니다.A query’s post-execution plan in SQL ServerSQL 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 an excessive memory grant condition, 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 an insufficiently sized memory grant condition, 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. 이 이벤트는 계획의 노드 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. 매개 변수 스니핑 및 매개 변수 민감도에 대한 자세한 내용은 쿼리 처리 아키텍처 가이드를 참조하세요.For more information about parameter sniffing and parameter sensitivity, refer to the Query Processing Architecture Guide.

메모리 부여 피드백 캐싱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. 변경 내용은 현재 쿼리 저장소에 캡처되지 않습니다.Changes are currently not captured in the Query Store. 캐시에서 계획을 제거하면 피드백이 유지되지 않습니다.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. 이 이벤트는 현재 실행 횟수 기록, 메모리 부여 피드백이 계획을 업데이트한 횟수, 수정 전 적합한 추가 메모리 부여, 메모리 부여 피드백이 캐시된 계획을 수정한 후 적합한 추가 메모리 부여를 추적합니다.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.

호환성 수준을 변경하지 않고 메모리 부여 피드백 비활성화Disabling memory grant feedback without changing the compatibility level

데이터베이스 호환성 수준 140 이상을 유지하면서 데이터베이스 또는 명령문 범위에서 메모리 부여 피드백을 비활성화할 수 있습니다.Memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. 데이터베이스에서 발생하는 모든 쿼리 실행에 대한 일괄 처리 모드 메모리 부여 피드백을 비활성화하려면 해당 데이터베이스의 컨텍스트 내에서 다음을 실행합니다.To disable batch mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

활성화될 경우 sys.database_scoped_configurations에서 이 설정이 enabled로 표시됩니다.When enabled, this setting will appear as enabled in sys.database_scoped_configurations.

데이터베이스에서 발생하는 모든 쿼리 실행에 대한 일괄 처리 모드 메모리 부여 피드백을 재활성화하려면 해당 데이터베이스의 컨텍스트 내에서 다음을 실행합니다.To re-enable batch mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

또한 DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK을 USE HINT 쿼리 힌트로 지정하여 특정 쿼리에 대한 일괄 처리 모드 메모리 부여 피드백을 비활성화할 수 있습니다.You can also disable batch mode memory grant feedback for a specific query by designating DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK as a USE HINT query hint. 예를 들어 다음과 같이 사용할 수 있습니다.For example:

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK')); 

USE HINT 쿼리 힌트는 데이터베이스 범위 구성 또는 추적 플래그 설정보다 우선합니다.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

일괄 처리 모드 적응 조인Batch mode Adaptive Joins

일괄 처리 모드 적응 조인 기능을 사용하면 해시 조인 또는 중첩된 루프 조인 메서드 선택을 첫 번째 입력이 검사된 까지 지연할 수 있습니다.The batch mode Adaptive Joins feature enables the choice of a Hash Join or Nested Loops 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 Loops 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 Loops 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 Loops 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 Loops 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 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, the Clustered Index Seek now has rows flowing through it.
  • 해시 조인 빌드 단계를 계속 진행하지 않았으므로 두 번째 분기를 통해 흐르는 행이 없습니다.And since the Hash Join build phase did not continue, there are no 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 indexed Nested Loops Join equivalent plan. 중첩된 루프가 해시 조인인 것처럼 추가 메모리가 요청됩니다.The additional memory is requested as if the Nested Loops was a Hash Join. 동등한 중첩된 루프 스트리밍 조인에 비해 스탑앤고(stop-and-go) 작업으로서 빌드 단계에 대한 오버헤드도 있습니다.There is also overhead for the build phase as a stop-and-go operation versus a Nested Loops 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 설명Description
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 Loops 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 Loops 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 Loops 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 versus the cost of a Nested Loops Join alternative. 이 교차 지점에서 결정되는 임계값에 따라 다시 조인 작업에 사용되는 실제 알고리즘이 결정됩니다.At this intersection point, the threshold is determined that in turn determines the actual algorithm used for the join operation.

조인 임계값

호환성 수준을 변경하지 않고 적응형 조인 비활성화Disabling adaptive joins without changing the compatibility level

데이터베이스 호환성 수준 140 이상을 유지하면서 데이터베이스 또는 명령문 범위에서 적응형 조인을 비활성화할 수 있습니다.Adaptive joins can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher.
데이터베이스에서 발생하는 모든 쿼리 실행에 대한 적응형 조인을 비활성화하려면 해당 데이터베이스의 컨텍스트 내에서 다음을 실행합니다.To disable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

활성화될 경우 sys.database_scoped_configurations에서 이 설정이 enabled로 표시됩니다.When enabled, this setting will appear as enabled in sys.database_scoped_configurations. 데이터베이스에서 발생하는 모든 쿼리 실행에 대한 적응형 조인을 재활성화하려면 해당 데이터베이스의 컨텍스트 내에서 다음을 실행합니다.To re-enable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;

또한 DISABLE_BATCH_MODE_ADAPTIVE_JOINS를 USE HINT 쿼리 힌트로 지정하여 특정 쿼리에 대한 적응형 조인을 비활성화할 수 있습니다.You can also disable adaptive joins for a specific query by designating DISABLE_BATCH_MODE_ADAPTIVE_JOINS as a USE HINT query hint. 예를 들어 다음과 같이 사용할 수 있습니다.For example:

SELECT s.CustomerID,
       s.CustomerName,
       sc.CustomerCategoryName
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS')); 

USE HINT 쿼리 힌트는 데이터베이스 범위 구성 또는 추적 플래그 설정보다 우선합니다.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

다중 문 테이블 반환 함수에 대한 인터리브 실행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(12.x)SQL Server 2014 (12.x)SQL Server 2016(13.x)SQL Server 2016 (13.x)에서는 MSTVF에 대한 고정 카디널리티 추정이 100이고, 이전 버전에서는 1입니다.MSTVFs have a fixed cardinality guess of 100 in SQL Server 2014(12.x)SQL Server 2014 (12.x) and SQL Server 2016(13.x)SQL Server 2016 (13.x), 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는 인터리브 방식으로 실행할 수 없습니다.Also, MSTVFs are not eligible for interleaved execution if they do not use runtime constants.

인터리브 실행 혜택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).
  2. 전체 쿼리에서 중간 결과의 크기 변경이 중요합니다.And 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:

실행 계획 특성Execution Plan attribute 설명Description
ContainsInterleavedExecutionCandidatesContainsInterleavedExecutionCandidates QueryPlan 노드에 적용됩니다.Applies to the QueryPlan node. true이면 계획에 인터리브 실행 후보가 포함됩니다.When true, means the plan contains interleaved execution candidates.
IsInterleavedExecutedIsInterleavedExecuted TVF 노드에 대한 RelOp 아래의 RuntimeInformation 요소의 특성입니다.Attribute of the RuntimeInformation element under the RelOp for the TVF node. true이면 작업이 인터리브 실행 작업의 일부로 구체화된 것입니다.When true, 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 설명Description
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 showplan 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.

호환성 수준을 변경하지 않고 인터리브된 실행 비활성화Disabling interleaved execution without changing the compatibility level

데이터베이스 호환성 수준 140 이상을 유지하면서 데이터베이스 또는 명령문 범위에서 인터리브된 실행을 비활성화할 수 있습니다.Interleaved execution can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. 데이터베이스에서 발생하는 모든 쿼리 실행에 대한 인터리브된 실행을 비활성화하려면 해당 데이터베이스의 컨텍스트 내에서 다음을 실행합니다.To disable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;

활성화될 경우 sys.database_scoped_configurations에서 이 설정이 enabled로 표시됩니다.When enabled, this setting will appear as enabled in sys.database_scoped_configurations. 데이터베이스에서 발생하는 모든 쿼리 실행에 대한 인터리브된 실행을 재활성화하려면 해당 데이터베이스의 컨텍스트 내에서 다음을 실행합니다.To re-enable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;

또한 DISABLE_INTERLEAVED_EXECUTION_TVF를 USE HINT 쿼리 힌트로 지정하여 특정 쿼리에 대한 인터리브 실행을 비활성화할 수 있습니다.You can also disable interleaved execution for a specific query by designating DISABLE_INTERLEAVED_EXECUTION_TVF as a USE HINT query hint. 예를 들어 다음과 같이 사용할 수 있습니다.For example:

SELECT  [fo].[Order Key], [fo].[Quantity], [foo].[OutlierEventQuantity]
FROM    [Fact].[Order] AS [fo]
INNER JOIN [Fact].[WhatIfOutlierEventQuantity]('Mild Recession',
                            '1-01-2013',
                            '10-15-2014') AS [foo] ON [fo].[Order Key] = [foo].[Order Key]
                            AND [fo].[City Key] = [foo].[City Key]
                            AND [fo].[Customer Key] = [foo].[Customer Key]
                            AND [fo].[Stock Item Key] = [foo].[Stock Item Key]
                            AND [fo].[Order Date Key] = [foo].[Order Date Key]
                            AND [fo].[Picked Date Key] = [foo].[Picked Date Key]
                            AND [fo].[Salesperson Key] = [foo].[Salesperson Key]
                            AND [fo].[Picker Key] = [foo].[Picker Key]
OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));

USE HINT 쿼리 힌트는 데이터베이스 범위 구성 또는 추적 플래그 설정보다 우선합니다.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

참고 항목See Also

SQL Server 데이터베이스 엔진 및 Azure SQL Database에 대한 성능 센터 Performance Center for SQL Server Database Engine and Azure SQL Database
쿼리 처리 아키텍처 가이드 Query Processing Architecture Guide
실행 계획 논리 및 물리 연산자 참조 Showplan Logical and Physical Operators Reference
조인 Joins
Demonstrating Adaptive Query Processing(적응 쿼리 처리 시연)Demonstrating Adaptive Query Processing