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


RESOURCE_GOVERNOR_IDLE과 쿼리 성능

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

이 글은 CSS SQL Server Engineers에 기재된 내용으로 원문을 읽고 해석한 것으로 필자의 이해력을 기반으로 기술하였습니다. 기술적 오류 또는 번역의 오류가 포함될 수 있으니 반드시 원문을 참고 바랍니다.

 

쿼리의 실행이 느릴 때 SQL Nexus(http://sqlnexus.codeplex.com/) 에서 다음과 같은 대기 유형을 캡처 했다. 대기 유형에서 RESOURCE_GOVERNOR_IDLE가 매우 높게 나타는것을 확인 하였다.

 

이 대기 유형은 CPU CAP 실행에 관련한 것이었다(CAP_CPU_PERCENT). CAP_CPU_PERCENT를 사용하면 SQL Server는 CPU pool에서 CPU CAP을 초과하지 않는 것을 보장한다. 만약 CPU_CAP_PERCENT를 10%로 설정한 경우 SQL Server는 CPU pool의 10%를 사용하는 것을 보장한다. SQL Server는 풀에게 부여되지 않는 퀀텀(quantum)을 차지하기 위해 실행 가능한 큐에 유휴 소비자(Idle Consumer)를 삽입한다. 유휴소비자가 기다리는 동안 RESOURCE_GOVERNOR_IDLE이 유휴소비자 퀀텀이 있음을 나타내기 시작했다. 여기에 특정 리소스 풀에 대한 실행 가능한 큐와 CAP_CPU_PERCENT 구성 없이 어떻게 보이는지에 대한 것이다.

 

 

대기 유형은 Sys.dm_os_ring_buffers에서 볼 수 있을 뿐만 아니라 sys.dm_os_ring_buffers 항목에서도 볼 수 있다.

select * from sys.dm_os_ring_buffers

where ring_buffer_type ='RING_BUFFER_SCHEDULER' and record like '%SCHEDULER_IDLE_ENQUEUE%'

 

<Record id = "139903" type ="RING_BUFFER_SCHEDULER" time ="78584090"><Scheduler address="0x00000002F0580040"><Action>SCHEDULER_IDLE_ENQUEUE</Action><TickCount>78584090</TickCount><SourceWorker>0x00000002E301C160</SourceWorker><TargetWorker>0x0000000000000000</TargetWorker><WorkerSignalTime>0</WorkerSignalTime><DiskIOCompleted>0</DiskIOCompleted><TimersExpired>0</TimersExpired><NextTimeout>6080</NextTimeout></Scheduler></Record>

 

이처럼 RESOURCE_GOVERNOR_IDLE 대기 유형 타입을 무시해서는 안된다. 사용자가 CPU CAP을 설정하는 경우 정확히 평가해야 한다. 너무 낮은 설정은 쿼리에 영향을 받을 수 있다.

 

다음 스크립트는 CPU CAP을 설정하고 실행 시간을 관찰한다.

--first measure how long this takes

select count_big (*) from sys.messages m1 cross join sys.messages m2 -- cross join sys.messages m3

go

 

--alter to 5 (make sure you revert it back later)

ALTER RESOURCE POOL [default]

WITH ( CAP_CPU_PERCENT = 5 );

go

ALTER RESOURCE GOVERNOR RECONFIGURE;

go

 

--see the configuration

select * from sys.dm_resource_governor_resource_pools

go

 

--now see how long it takes

select count_big (*) from sys.messages m1 cross join sys.messages m2 -- cross join sys.messages m3

go

 

--While the above query is running, open a different connection and run the following query

--you will see that it keeps going up. note that if you don't configure CAP_CPU_PERCENT, this value will be zero

select * from sys.dm_os_wait_stats where wait_type ='RESOURCE_GOVERNOR_IDLE'

 

 

--revert it back

ALTER RESOURCE POOL [default]

WITH ( CAP_CPU_PERCENT = 100 );

go

ALTER RESOURCE GOVERNOR RECONFIGURE;

go

 

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2015/04/10/what-is-resource-governor-idle-and-why-you-should-not-ignore-it-completely.aspx

 

SQL Server, mssql, 쿼리성능, 쿼리튜닝, DB튜닝, sys.dm_os_wait_stats, sys.dm_os_ring_buffers, SQL 대기, SQL Wait

2015-04-20 / 강성욱 / http://sqlmvp.kr

 

 

+ Recent posts