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

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

SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리

  • 병렬 인덱스 작업

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012 (Enterprise Edition only)

 

인덱스를 생성하거나 재생성 하는 작업 또는 클러스터형 인덱스를 삭제하는 작업을 위해 작성된 쿼리 계획에서는 여러 마이크로프로세서가 있는 컴퓨터에서 병렬 다중 스레드 작업을 할 수 있다.

 

SQL Server는 다른 쿼리에 사용하는 것과 동일한 알고리즘을 사용하여 인덱스 작업에 대한 병렬처리 수준(실행할 스레드 수)을 결정한다. 인덱스 작업에 대한 최대 병렬 처리 수준은 max degree of parallelism 서버 구성 옵션을 따른다.

 

 

CREATE INDEX, ALTER INDEX, DROP INDEX 및 ALTER TABLE 문에서 MAXDOP 인덱스 옵션을 설정하여 개별 인덱스 작업에 대한 max degree of parallelism 값을 재정의 할 수 있다.

 

데이터베이스 엔진에서 인덱스 실행 계획을 작성하는 경우 병렬 작업의 수는 가장 낮은 값으로 설정 된다.

  • 컴퓨터의 마이크로 프로세서의 수(또는 CPU)
  • max degree of parallelism 서버 구성
  • SQL Server 스레드에 대해 수행된 작업의 임계값을 넘지 않은 CPU 수
  • SQL Server 데이터베이스 엔진이 시스템에서 진행 중인 작업이 많음을 감지하면 실행 되기 전 인덱스 작업의 병렬처리 수준이 자동으로 감소 한다.
  • 분할되지 않은 인덱스의 선행 키 열의 고유 값 수가 제한되거나 각 고유 값의 빈도가 상당히 다양한 경우 데이터 베이스 엔진은 병렬 처리 수준을 줄일 수도 있다.

 

예를 들어 8개의 CPU가 있으나 max degree of parallelism이 6으로 설정된 경우 최대 병렬 스레드 수는 6이다. 인덱스 실행 계획을 작성 할 때 컴퓨터에 있는 5개의 CPU가 SQL Server 작업의 임계값을 초과하는 경우 실행 계획은 3개의 병렬 스레드만 지정 한다.

 

병렬 인덱스 작업의 주요 단계는 다음과 같다.

  • 조정 스레드(coordination thread)는 신속하게 무작위로 테이블을 검색하여 인덱스 키의 분포를 예상한다.
  • 조정 스레드(coordination thread)는 병렬 작업 수준에 해당하는 여러 키 범위를 만드는 키 경계를 설정한다. 여기서 각 키 범위는 비슷한 개수의 행을 포함할 수 있도록 결정 된다.(모든 CPU를 사용하도록 충분한 키 범위를 설정 할 수 없는 경우 병렬 처리 수준도 이에 따라 줄어 든다.)

 

  • 조정 스레드(coordination thread)는 병렬 작업 수준에 해당하는 여러 스레드를 디스패치하고 이러한 스레드가 작업을 완료할 때까지 대기 한다.
  • 각 스레드는 해당 스레드에 할당된 범위 내의 키 값을 갖는 행만 검색하는 필터를 사용하여 기본 테이블을 검색 한다.
  • 각 스레드는 키 범위에서 행의 인덱스 구조를 작성 한다. 분할된 인덱스의 경우 각 스레드는 지정한 수만큼의 파티션을 작성한다. 파티션은 스레드간에 공유되지 않는다.
  • 모든 병렬 스레드가 완료 된 후 조정 스레드는 인덱스 하위 단위를 단일 인덱스에 연결 한다. (오프라인에서만 가능)

 

 

 

[온라인 인덱스 작업]

온라인 인덱스 작업은 인덱스 작업 중 동시 사용자 작업을 허용한다. MAXDOP 옵션을 사용하여 온라인 인덱스 작업에만 사용되는 최대 프로세서 수를 제어할 수 있다. 이렇게 하면 인덱스 작업에 사용되는 리소스와 현재 사용자가 사용하는 리소스를 적절히 조정할 수 있다.

 

 

[분할 인덱스 작업]

쿼리 최적화 프로그램에서 작업 작업에 병렬 처리 수준을 적용할 경우 정렬이 필요한 분할 인덱스 작업의 메모리 요구 사항이 늘어날 수 있다. 병렬 처리 수준이 높을수록 메모리 요구사항이 늘어난다.

 

 

[참고 자료]

병렬 인덱스 작업 : http://msdn.microsoft.com/ko-kr/library/ms191292(v=sql.105).aspx

병렬 인덱스 작업 구성 : http://msdn.microsoft.com/ko-kr/library/ms189329(v=sql.105).aspx

온라인 인덱스 구조 : http://msdn.microsoft.com/ko-kr/library/ms191261(v=sql.105).aspx

분할된 테이블 및 인덱스 : http://technet.microsoft.com/ko-kr/library/ms345146(v=sql.90).aspx

 

 

 

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

No. Subject Author Date Views
1791 메모리 관리 아키텍처 – 버퍼 관리_페이지 쓰기 jevida(강성욱) 2016.09.28 1056
1790 메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기 jevida(강성욱) 2016.09.28 1250
1789 메모리 관리 아키텍처 – 버퍼 관리 jevida(강성욱) 2016.09.28 1818
1788 메모리 관리 아키텍처 – Min/Max Server Memory 효과 jevida(강성욱) 2016.09.28 2557
1787 메모리 관리 아키텍처 – 동적 메모리 관리 jevida(강성욱) 2016.09.28 1365
1786 메모리 관리 아키텍처 – 프로세스 주소 공간 jevida(강성욱) 2016.09.28 1363
1785 메모리 관리 아키텍처 – 메모리 아키텍처 jevida(강성욱) 2016.09.28 1873
1784 데이터 압축 상태에 대한 개체 크기 예상 jevida(강성욱) 2016.09.28 1368
1783 sp_MSforeachdb, sp_MSforeachtable 프로시저 활용하기 jevida(강성욱) 2016.09.28 2887
1782 SQL Server 쿼리 처리 아키텍처_분산 쿼리 아키텍처 jevida(강성욱) 2016.09.28 1145
» SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 인덱스 작업 jevida(강성욱) 2016.09.28 1341
1780 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 처리 수준 jevida(강성욱) 2016.09.28 1853
1779 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 jevida(강성욱) 2016.09.28 1791
1778 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - Preparing SQL Statements jevida(강성욱) 2016.09.28 1009
1777 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 강제 매개 변수화 jevida(강성욱) 2016.09.28 999
1776 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 단순 매개 변수화 jevida(강성욱) 2016.09.28 880
1775 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 매개 변수 및 실행 계획 재사용 jevida(강성욱) 2016.09.28 1098
1774 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 jevida(강성욱) 2016.09.28 1440
1773 DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인 jevida(강성욱) 2016.09.28 989
1772 SQL Server 쿼리 처리 아키텍처_저장 프로시저 및 트리거 실행 jevida(강성욱) 2016.09.27 941





XE Login