데이터베이스 개발자 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
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 3704
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1583
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 1704
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1435
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1310
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1580
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1239
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 3665
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1239
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1063
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1180
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1071
» 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1132
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1302
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1662
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1233
1734 SQL Server Plan Guide 생성 및 사용 jevida(강성욱) 2016.09.14 1422
1733 SQL Server 그래픽 실행 계획 노드 정보 jevida(강성욱) 2016.09.14 1066
1732 프로파일러를 이용한 실행계획 캡처하기 jevida(강성욱) 2016.09.14 959
1731 SQL Server 그래픽 실행 계획 및 텍스트 실행 계획 jevida(강성욱) 2016.09.14 3123





XE Login