유지관리 계획과 병렬 처리 – 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
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 38459
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 20748
1754 SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1644
1753 SQL Server 페이지 및 익스텐트 아키텍처(1/4) – 페이지 및 익스텐트 이해 jevida(강성욱) 2016.09.27 4038
1752 SQL Server Error Log 보관 주기 설정 jevida(강성욱) 2016.09.15 2332
1751 SQL Server 네트워크 백업 트러블슈팅(UNC 설정) jevida(강성욱) 2016.09.15 5473
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 4279
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1649
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 2038
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1544
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1373
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1725
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1304
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 4935
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1304
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1125
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1258
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1138
» 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1218
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1370
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1761
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1310





XE Login