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

 

·         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].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].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

 

 


 

 

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



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp





profile

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

Kakao Talk : SQLMVP

Line : jevida


현재 LA에 거주하고 있으며 SQL에 관심있는 분이면 언제든 친추 환영합니다.