DMV를 활용한 CPU 트러블슈팅 - Sys.dm_exec_query_stats, sys.dm_os_ring_buffers 활용

DMV를 활용한 CPU 트러블슈팅

• Sys.dm_exec_query_stats, sys.dm_os_ring_buffers 활용

 

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

 

SQL Server에서 제공되는 DMV(Dynamic Management View, 동적관리뷰)를 활용하여 CPU 사용량이 높은 쿼리를 확인 할 수 있다.

 

이 포스트는 SQL Server Premier Field Engineer Blog에 게시된 내용으로 필자가 읽고 이해한 내용을 정리하였으며 번역의 오류나 기술적 오류가 있음을 미리 알려둔다. 자세한 내용은 원문을 참고 한다.

 

다음 스크립트는 Sys.dm_exec_query_stats, Sys.dm_os_ring_buffers DMV를 활용하여 30초 간격으로 CPU 사용량을 체크하여 특정 임계값 보다 클 경우 캡처 할 수 있도록 하였다.

Sys.dm_exec_query_stats는 캐시된 쿼리 계획에 대한 집계 성능 통계를 반환한다. Sys.dm_os_ring_buffers에서 'RING_BUFFER_SCHDULER_MONITOR'는 60초 간격 SQL프로세를 캡처하여 정보를 제공 한다.

 

Declare @SQLCPUThrehold_Percent int Set @SQLCPUThrehold_Percent = 75 WHILE (1 = 1) BEGIN SELECT TOP 2 CONVERT (varchar(30), getdate(), 126) AS runtime, record.value('(Record/@id)[1]', 'int') AS record_id, record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_idle_cpu, record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization into #tempCPU FROM sys.dm_os_sys_info inf CROSS JOIN ( SELECT timestamp, CONVERT (xml, record) AS record FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%<SystemHealth>%') AS t ORDER BY record.value('(Record/@id)[1]', 'int') DESC     -- If the IF statement is satisfied, last 2 Ring buffer records had CPU > threshold so we capture the plans if ( (select count(*) from #tempCPU) = (select count(*) from #tempCPU where sql_cpu_utilization >@SQLCPUThrehold_Percent)) begin -- insert top 25 Statements and plans by CPU into the table insert into Tbl_troubleshootingPlans SELECT TOP 25 getdate() as runtime, qs.Execution_count as Executions, qs.total_worker_time as TotalCPU, qs.total_physical_reads as PhysicalReads, qs.total_logical_reads as LogicalReads, qs.total_logical_writes as LogicalWrites, qs.total_elapsed_time as Duration, qs.total_worker_time/qs.execution_count as [Avg CPU Time], substring (qt.text,qs.statement_start_offset/2,(case when qs.statement_end_offset = -1 then len (convert (nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text, qt.dbid as DBID, qt.objectid as OBJECT_ID, cast ( query_plan as xml) as XMLPlan FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(plan_handle) ORDER BY TotalCPU DESC end   drop table #tempCPU   waitfor delay '0:00:30' end go

 

 

 

CPU 부하를 높이기 위하여 다음 쿼리를 실행한다. CPU 사용률이 임계값에 도달하지 않는다면 여러 세션에서 다음 쿼리를 실행한다.

declare @a nvarchar(50)   while 1=1 begin     set @a = @@VERSION end

 

 

 

30초 간격으로 샘플을 수집하기 때문에 일정 시간이 지난 후 캡처 데이터를 저장한 테이블을 조회하여 수집된 정보를 확인 할 수 있다. 수집된 항목에는 CPU 사용량 및 실행 쿼리, 실행계획등을 확인 할 수 있다.

select * from Tbl_troubleshootingPlans with (nolock)

 

 

 

XMLPlan을 클릭하면 실행 계획을 그래픽 화면으로 확인 할 수 있다.

 

 

장시간 프로파일러를 사용하기에는 시스템에 대한 부담이 크고 비정기적으로 발생하는 이슈를 해결하기 위해서 이와 같이 특정한 임계치 이상일 때 해당 정보를 캡처하는 시스템을 활용하면 시스템에 문제가 발생하였을 때 트러블슈팅을 위한 정보 수집에 매우 유용할 듯 하다.

 

[참고자료]

• http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx
• http://technet.microsoft.com/ko-kr/library/ms189741(v=SQL.105).aspx

 

 

 

2013-10-28 / 강성욱 / http://sqlmvp.kr