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

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

유지관리 계획과 병렬 처리 – CHECKDB

 

  • Version : SQL Server 2012

 

 

SQL Server를 운영하는데 있어서 병렬처리는 성능에 영향을 미칠 수 있다. 그렇다고 병럴처리가나쁘다는 뜻이 아니다. 비즈니스 환경에 따라 병렬 또는 싱글 프로세스를 제어하여 최적의 플랜으로 운영할 수 있도록 해야 한다.

 

데이터베이스 유지 관리 계획에서 CHECKDB를 실행 할 때 MAXDOP 옵션을 통해서 병럴처리를 제어해 보자.

 

 

현재 MAXDOP 상태를 확인 한다.

SELECT [name], [value], [value_in_use]

FROM [sys].[configurations]

WHERE [name] = 'max degree of parallelism';

 

 

Value_in_use 값이 0 일 때에는 디폴트 값으로 전체 프로세스를 다 활용하겠다는 뜻이다.

 

 

실습에서는 MAXDOP 값을 2로 변경 하였다.

(현재 코어는 4Core 이다. (2core + HyperThread)

sp_configure 'show advanced options', 1

go

 

RECONFIGURE WITH OVERRIDE

go

 

SP_CONFIGURE 'MAX DEGREE OF PARALLELISM', 2

 

GO

 

RECONFIGURE WITH OVERRIDE

 

GO

 

SP_CONFIGURE

GO

 

 

 

쿼리 플랜을 캡처하기 위하여 SQL Server에 이벤트를 등록하자. 실습 버전은 SQL Server 2012에서 테스트 되었음을 다시 한번 알려 둔다.

CREATE EVENT SESSION [CapturePlans] ON SERVER

ADD EVENT sqlserver.query_post_execution_showplan(

    ACTION(sqlserver.plan_handle,sqlserver.sql_text)),

ADD EVENT sqlserver.sp_statement_completed(

ACTION(sqlserver.sql_text))

ADD TARGET package0.event_file(SET filename=N'C:\temp\CapturePlans.xel'),

ADD TARGET package0.ring_buffer(SET max_memory=(102400))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,

MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

GO

 

ALTER EVENT SESSION [CapturePlans]

ON SERVER

STATE=START;

GO

 

 

 

DBCC CHECKDB를 실행 한다.

DBCC CHECKDB (AdventureWorks2012) WITH NO_INFOMSGS;

GO

 

 

 

캡처한 이벤트를 저장한 폴더를 확인해 보면 다음과 같이 이벤트 파일이 생성된 것을 확인 할 수 있다.

 

 

파일을 실행하면 SSMS에서 플랜 정보 및 이벤트 정보를 확인할 수 있다.

쿼리계획을 확인해 보면 병렬로 처리 되었음을 확인 할 수 있다. (MAXDOP = 2)

 

XML정보를 확인해 보면 할당된 프로세스와 실제 프로세스에서 처리한 로우수 등 다양한 정보를 확인 할 수 있다.

 

 

그렇다면 이번에는 MAXDOP를 1로 설정하여 싱글로 처리되도록 수정 하자.

sp_configure 'show advanced options', 1;

go

 

RECONFIGURE WITH OVERRIDE;

GO

 

sp_configure 'max degree of parallelism', 1;

GO

 

RECONFIGURE WITH OVERRIDE;

GO

 

ALTER EVENT SESSION [CapturePlans]

ON SERVER

STATE=START;

GO

 

DBCC CHECKDB (AdventureWorks2012) WITH NO_INFOMSGS;

GO

 

ALTER EVENT SESSION [CapturePlans]

ON SERVER

STATE=STOP;

GO

 

 

다음과 같이 싱글(MAXDOP = 1)로 처리 된 것을 확인 할 수 있다.

 

 

XML 정보를 살펴보면 프로세스 할당이 1개만 되어 있기 때문에 병렬처리에는 0으로 확인 되었다.

 

 

비즈니스 환경에 따라 MAXDOP를 설정하여 자원을 효율적으로 운용할 수 있다. 하지만 주의할 것은 MAXDOP의 설정은 전역으로 사용되기 때문에 다른 쿼리에 까지 영향을 미친다. 엔터프라이즈 환경에서 여러 인스턴스로 분리 되어 있을 경우 필요에 따라 DOP를 설정하여 운용하면 좋을 듯 하다.

 


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

No. Subject Author Date Views
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 4228
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1270
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1083
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1210
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1091
» 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1159
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1324
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1703
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1256
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1465
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1090
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 977
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3173
1730 SQL Server에서 Trigger 활성 / 비활성 감시 jevida(강성욱) 2016.09.14 1512
1729 DDL Trigger를 이용한 데이터베이스 변경 사항 추적 jevida(강성욱) 2016.09.14 1291
1728 Trigger를 이용한 SQL Server 커넥션 풀링 확인 jevida(강성욱) 2016.09.14 1102
1727 SQL Server Trigger jevida(강성욱) 2016.09.14 960
1726 인덱스에 대한 SORT_IN_TEMPDB 옵션 jevida(강성욱) 2016.09.14 865
1725 인덱스 DDL 작업의 디스크 공간 요구 사항 jevida(강성욱) 2016.09.14 925
1724 XML nodes() 함수를 이용한 OPENXML 교체 jevida(강성욱) 2016.09.14 996





XE Login