소개
Feedback 의 마지막 기능입니다. Query Optimizing에서 Degree Of Parallelism(DOP)이 무엇이고 쿼리 성능에 어떤 영향을 미치는지 그리고 SQL Server 2022에서는 어떤 변화가 있는지를 소개하겠습니다.
참고. 아쉽지만 Feedback 기능들은 Enterprise Edition에서 지원됩니다.
Degree of Parallelism(DOP)
병렬쿼리
한 쿼리를 실행할 때 CPU를 두 개 이상 사용하는 경우 병렬쿼리입니다. 아래 그림과 같이 실행 계획의 각 연산자 아이콘의 주황색(?) 동그라미에 화살표 두 개(아마도 동시에 처리한다는 의미) 가 그려져 있고 “Parallelism” 연산자 아이콘도 나타납니다.
[그림] 병렬 처리하는 쿼리의 실행 계획 중 일부
Ouery Optimizing 절차에서 조기에 완료하지 못하고 마지막 단계까지 이르렀을 때, 쿼리 추정 비용이 “병렬 처리 비용 임계값”(cost threshold for parallelism)에 설정된 값(기본값 5)을 초과하면 병렬처리 실행 계획을 검토하고 직렬 계획과 비교 시 비용이 더 적다면 그 때 병렬 쿼리가 최종 선택됩니다.
참고. 기본 값을 변경하고 싶으면 아래 그림처럼 서버 옵션 설정에서 “병렬 처리에 대한 비용 임계값“을 조정할 수 있습니다.
[그림] 서버 옵션, “병렬 처리에 대한 비용 임계값” 과 “최대 병렬 처리 수준”
MAXDOP, 병렬처리용 CPU 수 제한
이 때 CPU를 몇 개 사용할 것인가 또한 Query Optimizer가 결정합니다. 결정의 기준은 서버 또는 데이터베이스에 설정된 일명 MAXDOP(“최대 병렬 처리 수준“, 디폴트 0 – 그 의미는 All) 값을 사용합니다, 이 값을 초과하지 않는 범위 내에서 Optimizer가 최대 CPU수를 결정하는 것이죠. 서버 단위 설정은 위 그림에서 볼 수 있고 DB 단위 설정은 아래 그림에서 볼 수 있습니다. 또는 쿼리 단위로 OPTION (MAXDOP n) 힌트를 사용할 수도 있죠.
참고. “최대 병렬 처리 수준” 설정에 대한 정보는 MS 문서 “최대 병렬 처리 수준 구성“를 참조하세요.
[그림] Database 단위 옵션, 그 중 “Max DOP” 항목
Degree of Parallelism Feedback 이란?
병렬 쿼리가 아예 없다면 모를까, 실제로 쿼리가 병렬로 처리될 때 몇 개의 CPU를 사용하는 것이 좋을까에 대한 답은 일종의 “가성비”(비용 대비 효과)입니다 즉 비용(CPU/Thread, IO, Memory) 대비 효과(Duration 등) 측면에서 좋은 가성비를 가지는 CPU 개수를 선택하는 것이 권장된다는 것이죠. 한 쿼리에서 너무 많은 CPU를 쓰는 것도 너무 적은 CPU를 쓰는 것도 우리가 원하는 성능과는 거리가 멀다는 겁니다.
뿐만아니라 병렬처리할 데이터의 분포가 어떻게 되는지? 또는 각 CPU(정확히는 스레드)별로 배분된 행 수의 분포가 어떻게 되는지 등에 따라서 쿼리 성능이 크게 달라질 수 있다는 것입니다. 예를 들어 아래 그림의 사례를 보면 쿼리에서 처리할 총 838,662건의 행 수를 전체 CPU(스레드) 64개에 균등하게 배분하고 이를 동시에 처리하는 것이 가장 좋겠지만 실제로는 스레드 5번에 전체 행 수가 몰방(몰빵^^)되어서 병렬 처리 자체가 무의미해지는 결과를 보여줍니다.
[그림] 사례 – 병렬 쿼리에서 특정 연산자에 각 CPU(스레드)별로 배분된 행 수의 분포, 매우 극단적인 경우임
결국 서버나 데이터베이스에서 설정하는 MAXDOP 값은 기본 값으로서의 의미가 있는 것이고 실제로는 병렬 쿼리 개별적으로 가성비가 좋은 DOP 값을 찾아서 사용하는 것이 좋다는 것입니다. 문제는 그렇게 하기 위해 동일 쿼리를 서로 다른 DOP로 여러 번 실행한 결과 데이터를 바탕으로 가성비를 비교해 봐야 나올 수 있는 결과라는 것이죠. 이를 사용자가 일일이 하기에는 그 또한 가성비가 떨어지는 일이 될 것입니다 (물론, 전문적으로 쿼리 튜닝 작업을 할 때는 이러한 단계를 거치기도 합니다)
DOP Feedback은 이러한 과정을 SQL Server가 자체적으로 수행하고 최종 결정을 하겠다는 의미입니다. 몇 번의 반복 작업으로 DOP를 조정(2 ~ MAXDOP 사이)하고 그 효과를 측정한 뒤 더 좋아지고 나빠지는 결과 비교 데이터를 바탕으로 최종 DOP를 결정하는 작업입니다. 최종 결과는 쿼리 저장소(Query Store)에 함께 저장하고 재사용합니다.
[영상] 이해를 돕기 위해 DOP Feedback 동작을 재현한 데모 영상을 제 Youtube에 올려두었습니다. 화면 오른쪽의 확장 이벤트 추적을 통해 DOP 수가 수 차례 변경되고 그 결과를 평가하는 과정의 되폴이를 볼 수 있습니다.
정리
DOP Feedback 도 기대되는 기능 중에 하나입니다. ERP/GW/MES/EMR 등 복잡한 업무를 처리하는 DB에서 병렬쿼리는 꼭 필요한 작업이면서 또한 성능에 큰 영향을 미치지만 전문가가 아닌한 쉽지 않은 튜닝 작업이기 때문입니다.
다시 말씀드립니다만, SQL Server 2022의 Cumulative Update에는 Feedback 관련 기능 들에 대한 패치들도 적용되어 있으므로(버그도 잡고 기능도 개선하고 있다는 것이죠) 업그레이드 시 가급적 최신 CU를 적용해서 사용하시기 바랍니다.
모두 건강하세요.
김정선 드림
