Event Session을 이용한 Session Health check

Event Session을 이용한 Session Health check

   

-       Version : SQL Server 2012

   

SQL Server 2012에서는 이벤트를 사용하여 SQL Server의 세션 상태를 확인 할 수 있다.

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

SET NOCOUNT ON -- Fetch data for only SQL Server 2012 instances     IF (SUBSTRING(CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)),1,CHARINDEX('.',CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)))-1) >= 11) BEGIN     -- Get UTC time difference for reporting event times local to server time     DECLARE @UTCDateDiff int = DATEDIFF(mi,GETUTCDATE(),GETDATE());         -- Store XML data retrieved in temp table     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';         -- Parse XML data and provide required values in the form of a table ;     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 [Event Time],         EventXML.value('(/event/data/text)[1]','varchar(255)') as Component,         EventXML.value('(/event/data/value/system/@latchWarnings)[1]','bigint') as [Latch Warnings],         EventXML.value('(/event/data/value/system/@isAccessViolationOccurred)[1]','bigint') as [Access Violations],         EventXML.value('(/event/data/value/system/@nonYieldingTasksReported)[1]','bigint') as [Non Yields Reported],         EventXML.value('(/event/data/value/system/@BadPagesDetected)[1]','bigint') as [Bad Pages Detected],         EventXML.value('(/event/data/value/system/@BadPagesFixed)[1]','bigint') as [Bad Pages Fixed]     FROM CTE_HealthSession     WHERE EventXML.value('(/event/@name)[1]', 'varchar(255)') = 'sp_server_diagnostics_component_result'         AND EventXML.value('(/event/data/text)[1]','varchar(255)') = 'SYSTEM' ORDER BY [Event Time]; DROP TABLE #SystemHealthSessionData END

   

   

   

   

[참고자료]

http://troubleshootingsql.com/2013/08/02/powerview-and-system-health-session-system/

   

2013-09-09 / 강성욱 / http://sqlmvp.kr