RESOURCE_GOVERNOR_IDLE과 쿼리 성능

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