데이터베이스 개발자 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
1890 장기 트랜잭션 확인 및 경고 설정 jevida(강성욱) 2016.10.11 1418
1889 SQL 데이터 수집툴 DiagManager 사용법 jevida(강성욱) 2016.10.11 1319
1888 Windows Server 2012 R2 그룹 관리 서비스 계정 및 SQL 서버 jevida(강성욱) 2016.10.11 1833
1887 Kerbros 구성 관리자 툴 jevida(강성욱) 2016.10.11 1160
1886 SQL Server Geography 및 Geometory 데이터 형식 jevida(강성욱) 2016.10.11 2357
1885 SQL Server를 이용한 신용카드 보안 -조직이 PCI DSS 준수를 달성 하기 위한 요건 jevida(강성욱) 2016.10.11 1363
1884 TDE 암호화 사용하기 jevida(강성욱) 2016.10.11 3263
1883 SQL Server 암호화 하기 jevida(강성욱) 2016.10.11 2725
1882 의도하지 않은 분산트랜잭션 사용 jevida(강성욱) 2016.10.11 877
1881 Affinity I/O Mask jevida(강성욱) 2016.10.11 1165
1880 LPE_BATCH 스핀락 jevida(강성욱) 2016.10.11 882
1879 SQL Server 17953 오류 jevida(강성욱) 2016.10.11 981
1878 SQL 연결 18056 오류 jevida(강성욱) 2016.10.11 1360
1877 SQL 2008 R2 Sp1 적용과 9013 오류 - 포맷 섹터 크기에 따른 오류 jevida(강성욱) 2016.10.11 1792
1876 JDBC 로깅 파일 위치 설정 jevida(강성욱) 2016.10.11 958
1875 NUMA Node 메모리 블록 jevida(강성욱) 2016.10.11 1046
1874 AppDomain unloading 오류 로그 – CLR 오류 jevida(강성욱) 2016.10.11 1187
1873 SQL Server IO and Latch 설명 jevida(강성욱) 2016.10.11 3139
1872 마지막 백업 시간 및 DBCC 확인 [1] jevida(강성욱) 2016.10.08 1591
» DMV를 활용한 CPU 트러블슈팅 - Sys.dm_exec_query_stats, sys.dm_os_ring_buffers 활용 [1] jevida(강성욱) 2016.10.08 1906





XE Login