데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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을 클릭하면 실행 계획을 그래픽 화면으로 확인 할 수 있다.

 

 

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

 

[참고자료]

 

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
» DMV를 활용한 CPU 트러블슈팅 - Sys.dm_exec_query_stats, sys.dm_os_ring_buffers 활용 [1] jevida(강성욱) 2016.10.08 1907
1870 백업 미디어 세트에 압축 백업 추가하기 jevida(강성욱) 2016.10.08 1451
1869 Collation에 따른 실행계획 변경과 성능 문제 jevida(강성욱) 2016.10.08 1667
1868 SQL Connection Timeout 디버깅 with BizTalk Server jevida(강성욱) 2016.10.08 2279
1867 인스턴스 파일 초기화 활성 jevida(강성욱) 2016.10.08 1710
1866 누락된 인덱스 확인하기 jevida(강성욱) 2016.10.08 2871
1865 비클러스터 인덱스 페이지 내용 jevida(강성욱) 2016.10.08 2011
1864 ATTACH DATABASE 오류 1314 jevida(강성욱) 2016.10.08 1161
1863 SQL Server 커넥션 풀링 jevida(강성욱) 2016.10.08 4051
1862 가상 SQL Server에 Hot Add vCPU 사용하기 jevida(강성욱) 2016.10.08 1112
1861 DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기 jevida(강성욱) 2016.10.08 1348
1860 쉐어포인트의 SQL Server 접속 문제 jevida(강성욱) 2016.10.08 1446
1859 Lazy Log Truncation jevida(강성욱) 2016.10.08 1290
1858 인덱스 구성과 상황에 따른 인덱스 성능 jevida(강성욱) 2016.10.08 1462
1857 Max worker thread 초과 이슈 jevida(강성욱) 2016.10.08 2662
1856 SQL Server Failover 클러스터 설치 트러블슈팅 jevida(강성욱) 2016.10.08 2342
1855 MAXDOP 극대화 하기 jevida(강성욱) 2016.10.08 2018
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2019
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1842
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3439





XE Login