Event Session을 이용한 CPU Health check

Event Session을 이용한 CPU Health check

   

-       Version : SQL Server 2012

   

SQL Server 2012에서는 이벤트를 사용하여 SQL Server 프로세스의 CPU 사용률과 시스템의 CPU 사용률에 대한 분단위의 정보를 얻을 수 있다. 또한 SQL Server에서 프로세스의 (소프트 및 하드)페이지 폴트 발생과 인스턴스 메모리의 사용량 변화, locked page 및 large page 할당 값을 얻을 수 있다.

   

다음 스크립트를 사용하여 이벤트 정보를 확인한다.

SET NOCOUNT ON   IF (SUBSTRING(CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)),1,CHARINDEX('.',CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)))-1) >= 11) BEGIN         DECLARE @UTCDateDiff int = DATEDIFF(mi,GETUTCDATE(),GETDATE());       SELECT TOP 1 CAST(xet.target_data AS XML) AS XMLDATA INTO #SystemHealthSessionData     FROM sys.dm_xe_session_targets xet         JOIN sys.dm_xe_sessions xe ON (xe.address = xet.event_session_address)     WHERE xe.name = 'system_health' AND xet.target_name = 'ring_buffer';           WITH CTE_HealthSession (EventXML) AS     (     SELECT C.query('.') EventXML     FROM #SystemHealthSessionData a         CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C)     )       SELECT         DATEADD(mi,@UTCDateDiff,EventXML.value('(/event/@timestamp)[1]', 'datetime')) as EventTime,         EventXML.value('(/event/data/value)[3]', 'int') as process_utilization,         EventXML.value('(/event/data/value)[4]', 'int') as system_idle,         EventXML.value('(/event/data/value)[7]', 'float') as page_faults,         EventXML.value('(/event/data/value)[8]', 'float') as working_set_delta,         EventXML.value('(/event/data/value)[9]', 'int') as memory_utilization     FROM CTE_HealthSession     WHERE EventXML.value('(/event/@name)[1]', 'varchar(255)') = 'scheduler_monitor_system_health_ring_buffer_recorded'     ORDER BY EventTime;           DROP TABLE #SystemHealthSessionData;     END   SET NOCOUNT OFF

   

   

   

l  EventTime : 이벤트를 수집한 시간.

l  Process_utilzation : SQL Server가 사용한 평균 CPU 비율 (1분)

l  System_Idle : 서버의 CPU 유휴 비율. 다른 프로세스의 CPU 사용량을 확인 할 수 있다.

l  Page_fault : 페이지 폴트

l  Working_set_delta : 이전에 보고된 값과 현재 값의 차이

l  Memory_utilization : DBCC Memorystatus 에서 커밋된 메모리 퍼센트 값.

   

   

위의 스크립트를 이용한 CPU의 헬스체크를 통하여 현재 시스템의 상태를 모니터링 함으로써 발생할 수 있는 문제점을 미리 대비해야한다.

   

위의 스크립트에서는 CPU의 사용량 계산을 평균 1분의 사용량으로 나타내는데 평균을 나타내는시간이 길다면 길고 짧다면 짧을 수도 있다. 많은 DBA들이 오해하는 부분이 CPU가 일정 수준 이상으로 사용되면 바로 알림을 받아야 하지 않을까 하는 생각도 하지만 개인적인 생각으로는 CPU는 수시로 스파이크 현상이 나타나기 때문에 일시적인 스파이크로 진단하기보다는 DBA가 생각하는 평균 시간 동안 꾸준히 사용량이 높을때를 장애로 판단하는 것이 좀더 정확하지 않을까 생각한다.

   

   

[참고자료]

http://troubleshootingsql.com/2013/07/11/powerview-and-system-health-sessioncpu-health/ 

  

   

   

2013-08-30 / 강성욱 / http://sqlmvp.kr