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

유지관리 계획과 병렬 처리 – 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를 설정하여 운용하면 좋을 듯 하다.

 

 

2013-03-12 / 강성욱 / http://sqlmvp.kr