인덱스 리빌드시 병렬로 실행계획이 생성되지 않는 이유

인덱스 리빌드시 병렬로 실행계획이 생성되지 않는 이유

 

·         Version : SQL Server

 

SQL Server에서 인덱스를 리빌드 할때 병렬이 아닌 싱글로 실행되는 경우가 있다. 실행 계획을 살펴보면 현재 어떤 방식으로 실행되었는지에 대한 내용을 확인할 수있으며 경우에 따라 병렬로 실행되지 않는 원인을 나타내기도 한다. 아래 예시는 실행계획에 표시하는 병렬로 처리하지 못한 이유를 나타낸다.

But in this case, the query plan just say “CouldNotGenerateValidParallelPlan” like <QueryPlan DegreeOfParallelism=”0″ NonParallelPlanReason=”CouldNotGenerateValidParallelPlan” CachedPlanSize=”184″ CompileTime=”2″ CompileCPU=”2″ CompileMemory=”512″>.

 

인덱스 작업과 함께 병렬 처리가 작동하는 방식에 대해서는 아래 링크를 참고 한다

·         Configure Parallel Index Operations : https://technet.microsoft.com/en-us/library/ms189329(v=sql.120).aspx

·         Parallel Index Operations : https://technet.microsoft.com/en-us/library/ms191292(v=sql.105).aspx

·         SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 인덱스 작업 : http://sqlmvp.kr/140189541277

 

매우 높은 레벨에서 병렬처리가 작동하려면 파티션되지 않은 테이블 인덱스의 주요키가 여러개의 고유한 값을 가져야 한다. 기본적으로 각 병렬 스레드는 일련의 값 범위를 가져와서 작업하고 나중에 병합한다. 따라서 주요 키 열에 대해 하나의 값만 있으면 하나의 스레드만이 작업할 수 있다. 따라서 병렬 스레드로 실행되지 않는다.  또한 데이터베이스 엔진에서 인덱스 실행계획을 작성하는 경우 병렬 작업의 수는 가장 낮은 값으로 설정 된다.

·         컴퓨터의 마이크로 프로세서의 수(또는 CPU)

·         max degree of parallelism  서버구성

·         SQL Server 스레드에 대해 수행된 작업의 임계값을 넘지 않은 CPU 수

·         SQL Server 데이터베이스 엔진이 시스템에서 진행 중인 작업이 많음을 감지하면 실행 되기 전 인덱스 작업의병렬처리 수준이 자동으로 감소 한다.

·         분할되지않은 인덱스의 선행 키 열의 고유 값 수가 제한되거나 각 고유 값의 빈도가 상당히 다양한 경우 데이터 베이스 엔진은 병렬 처리 수준을 줄일 수도있다.

 

아래 예제는 싱글로 수행되는 인덱스 리빌드 작업을 강제로 병렬로 실행하기 위하만든 시나리오 이다. 하지만 실행계획은 병렬로 수행되었더라도 실제 작업은 1개의 스레드만이 작업을 수행하기 때문에 실질적인 이득은 없다.

1.       고유 행렬을 가진 우수한 데이터 분포를 이용하여 통계를 생성한다.

2.       모든 행에 대해 동일한 값으로 선행 열을 업데이트 한다.

3.       UPDATE STATISTICS WITH STATS_STREA = 2진값 을 사용하여 1단계에서 작성한 통계를 업데이트 한다. 이것은 옵티마이저가 선행 열에 많은 별개의 값이 있다고 생각하게 한다.

4.       인덱스 리빌드를 실행한다.

use master go create database testdb2 go use testdb2 go create table t (c1 int, c2 int, c3 int)   set nocount on begin tran   declare @i int = 0 while @i < 10000000 begin     insert into t values (@i, @i, @i)     set @i = @i + 1 end commit tran   go   create unique clustered index ix on t(c1,c2,c3)   go --this will use parallel plan ALTER INDEX ix on [dbo].t rebuild WITH (maxdop=10, online=on)   go --set leading column as dup update t set c1 = 1   go update statistics t with fullscan go --this will use serial plan ALTER INDEX ix on [dbo].t rebuild WITH (maxdop=10, online=on)     go --dbcc show_statistics (t, ix)

 

실제 실행 계획을 살펴보면 병렬로 스캔 작업이 발생하였지만 1개의 스레드만이 2416640 행의 작업을 수행했다.

<RunTimeInformation>                         <RunTimeCountersPerThread Thread=”8″ ActualRows=”0″ Batches=”0″ ActualExecutionMode=”Row” ActualEndOfScans=”1″ ActualExecutions=”1″ />                         <RunTimeCountersPerThread Thread=”2″ ActualRows=”0″ Batches=”0″ ActualExecutionMode=”Row” ActualEndOfScans=”1″ ActualExecutions=”1″ />                         <RunTimeCountersPerThread Thread=”6″ ActualRows=”0″ Batches=”0″ ActualExecutionMode=”Row” ActualEndOfScans=”1″ ActualExecutions=”1″ />                         <RunTimeCountersPerThread Thread=”5″ ActualRows=”0″ Batches=”0″ ActualExecutionMode=”Row” ActualEndOfScans=”1″ ActualExecutions=”1″ />                         <RunTimeCountersPerThread Thread=”7″ ActualRows=”0″ Batches=”0″ ActualExecutionMode=”Row” ActualEndOfScans=”1″ ActualExecutions=”1″ />                         <RunTimeCountersPerThread Thread=”1″ ActualRows=”2416640″ Batches=”0″ ActualExecutionMode=”Row” ActualRowsRead=”2416640″ ActualEndOfScans=”1″ ActualExecutions=”1″/>                         <RunTimeCountersPerThread Thread=”4″ ActualRows=”0″ Batches=”0″ ActualExecutionMode=”Row” ActualEndOfScans=”1″ ActualExecutions=”1″ />                         <RunTimeCountersPerThread Thread=”3″ ActualRows=”0″ Batches=”0″ ActualExecutionMode=”Row” ActualEndOfScans=”1″ ActualExecutions=”1″ />                         <RunTimeCountersPerThread Thread=”0″ ActualRows=”0″ ActualEndOfScans=”0″ ActualExecutions=”0″ />                       </RunTimeInformation>

 

 

[참고자료]

·         https://blogs.msdn.microsoft.com/psssql/2017/01/09/why-cant-i-get-a-parallel-plan-when-rebuilding-my-index/

·         Configure Parallel Index Operations : https://technet.microsoft.com/en-us/library/ms189329(v=sql.120).aspx

·         Parallel Index Operations : https://technet.microsoft.com/en-us/library/ms191292(v=sql.105).aspx

·         SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 인덱스 작업 : http://sqlmvp.kr/140189541277

 

 

2017-03-07 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, Index rebuild, Parallel plan, 인덱스 리빌드, 병렬처리, MS SQL, 쿼리처리 아키텍처, 다중 스레드, 실행계획, execution plan, 온라인 인덱스 리빌드, index