SQL Server Parallelism and Wait change (CXAPCKET, CXCONSUMER)

SQL Server Parallelism and Wait change (CXAPCKET, CXCONSUMER)

-          병렬처리에 발생하는 대기 종류 변경

 

·         Version : SQL Server 2016 SP2 later, SQL Server 2017 CU3, Azure SQL

 

SQL Server에서 CPU를 두개 이상 사용하는 컴퓨터에서는 운영체제의 다중 스레드로 쿼리나 인덱스 작업을 병렬로 수행하여 빠르고 효율적으로 요청 작업을 완료할 수 있다.  쿼리가 병렬로 실행될때 가장 이상적인 동작은 모든 작업이 생산자 스레드에 균등하게 분산되어 모두 같은 시간에 작업이 끝나서 결과를 소비자 스레드로 넘기는 것이다. 이때 모든 생성자가 완료되기를 기다리는동안 CXPACKET 대기(이때 발생하는 대기는 정상이다.)가 발생한다. 하지만 잘못된 병렬처리가 발생(작업의 불균등 분배)하여 스레드가 완료 될 때까지 일부 기다리는 스레드가 발생하는데 이때에도 CXAPCKET 대기가 발생한다. 그래서 정상적인(성능에 문제가 없는) CXPACKET인지, 비정상(성능에 문제가 있는) CXPACKET 인지 DBA는 구분할수 있어야 하는데 안타깝게도 SQL Server에서는 모두 CXPACKET로 표현되어 쉽게 알 수 있는 방법이 없다. (물론 실행계획을 캡쳐하여 스레드별로 처리되는 리소스를 확인하면 가능하다.)

 

SQL Server 2016 SP2 이상, SQL Server 2017 CU3 이상, Azure SQL 데이터베이스에서는 CXPACKET 대기가 보고되는 방식을 변경하였는데, 스레드간의 작업이 고르지 않게 분산되어 대기가 발생하는 (불량 유형의 대기)는 CXPACKET 대기로 계속 보고하고 소비자 스레드가 모든 제작자 스레드를 기다리는 정상적인(좋은 유형의 대기) 대기 스레드는 CXCONSUMER로 보고되어 사용자가 구분할 수 있도록 되었다.

 

기존의 SQL Server 2016 SP2 이하에서는 아래와 같이 CXPACKET 대기로만 표현되었다.

 

SQL Server2016 SP2 이후에서는 CXPACKET와 함께 CXCONSUMER로 구분할 수 있게 되었다.

 

 

이처럼 병렬처리시 대기에 대한 종류가 구분되어 사용자가 문제를 쉽게 파악할 수 있게 되었다. 하지만 주의해야할 점이 CXCONSUMMER이 보고되었기 때문에 무조건 성능에 문제가 없다고 판단할 수도 없다. 따라서 기존의CXPACKET 을 모니터링은 지속되어야 하며  CXCONSUMER를 함께 모니터링할 필요가 있다. 그리고 불필요한 병렬처리가 발생할 경우 쿼리를 수정하거나, 적절한 실행계획이 생성되기 위새 통계를 업데이트 하거나,  MAXDOP 옵션을 사용하여 SQL Server가 최적화 될 수 있도록 전략을 수립해야한다.

 

[참고자료]

·         쿼리 처리 아키텍처 - 병렬 쿼리 처리 : http://sqlmvp.kr/140189219438

·         CPU 리소스 상태에 따른 병렬 처리 제한 : http://sqlmvp.kr/140193366014

·         병렬 처리 대기 확인 : http://sqlmvp.kr/140207680181

·         SQL Server MAXDOP 설정 : http://sqlmvp.kr/140176043445

·         https://www.mssqltips.com/sqlservertip/5207/changes-to-cxpacket-waits-in-sql-server/

·         https://blogs.msdn.microsoft.com/sql_server_team/making-parallelism-waits-actionable/

 

 

 

2018-01-23 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

SQL Server, MS SQL, SQL Statistics, SQL Parallelism, CXPACKET, CXCONSUMER, worker thread, sql wait