데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

·         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

No. Subject Author Date Views
2090 SQL Server DBA 체크리스트 jevida(강성욱) 2017.05.31 6715
2089 SQL Server 데이터베이스 백업이 성공적인지 확인하는 방법 jevida(강성욱) 2017.05.31 3729
2088 트리거가 적용된 메모리 최적화 테이블에서 alter table 실패 jevida(강성욱) 2017.05.31 3247
2087 메모리 최적화 테이블 변수 및 예상 행수 jevida(강성욱) 2017.05.31 2939
2086 백업 파일 복원과 3241 오류 (Microsoft® SQL Server® Backup to Microsoft Azure®Tool 사용) jevida(강성욱) 2017.05.31 3091
2085 In-Memory OLTP를 사용할 때 체크포인트 파일이 너무 많아 지는 이유 jevida(강성욱) 2017.05.31 2671
2084 메모리 최적화 테이블변수와 701 오류 (loop 사용으로 인한 메모리 부족 오류) jevida(강성욱) 2017.05.31 3334
2083 메모리 최적화 테이블에서 해시 인덱스 사용시 버킷 카운트의 중요성 jevida(강성욱) 2017.05.31 2936
» 인덱스 리빌드시 병렬로 실행계획이 생성되지 않는 이유 jevida(강성욱) 2017.03.13 4927
2081 SQL Server 833오류 (15 Sec Slow IO Detected) jevida(강성욱) 2017.03.13 4294
2080 SQL Server nonpreemptive 모드에서Long Sync IO 오류 jevida(강성욱) 2017.03.13 3094
2079 SQL Server DTC Transaction의 SPID = -2 반환 jevida(강성욱) 2017.03.13 2394
2078 SQL Server Read/Writer 동기화 작동 원리 jevida(강성욱) 2017.03.13 2749
2077 SQL Server가 논리 및 물리 프로세서를 결정하는 방법 jevida(강성욱) 2017.03.13 2861
2076 데이터베이스에서 사용자 삭제 오류 jevida(강성욱) 2017.03.13 2788
2075 SQL Server ODBC Driver 및 Command package Tool 설치 jevida(강성욱) 2017.01.11 4761
2074 SQL Server Install on Linux (Ubuntu – 16.0.4) jevida(강성욱) 2017.01.11 4995
2073 SQL Azure blob storage 백업을 위한 프록시 세팅 jevida(강성욱) 2017.01.11 4197
2072 SQL on Azure 환경에서 SQL 서버 연결 불능 문제(VNET 설정 문제) jevida(강성욱) 2017.01.11 3285
2071 SQL Server 2016 자동 통계 업데이트 임계값 변경 jevida(강성욱) 2017.01.11 4522





XE Login