DMV를 이용한 I/O Health check

DMV를 이용한 I/O Health check

   

-       Version : SQL Server 2012

   

SQL Server를 운영할 때 디스크의 I/O는 DB의 성능과 매우 밀접한 관련이 있다. SQL Server 2012에서 I/O와 관련 래치 대기 시간 지속 시간 등을 sp_server_diagnostics 값을 이용하여 지속적인 I/O 자료를 수집하여 분석함으로써 SQL Server I/O 서브시스템의 상태를 확인 할 수 있다.

   

다음 스크립트는 DMV를 이용하여 성능을 수집한다.

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/ioSubsystem/@ioLatchTimeouts)[1]','bigint') as [IO Latch Timeouts],         EventXML.value('(/event/data/value/ioSubsystem/@totalLongIos)[1]','bigint') as [Total Long IOs],         EventXML.value('(/event/data/value/ioSubsystem/longestPendingRequests/pendingRequest/@filePath)[1]','varchar(8000)') as [Longest Pending Request File],         EventXML.value('(/event/data/value/ioSubsystem/longestPendingRequests/pendingRequest/@duration)[1]','bigint') as [Longest Pending IO Duration]     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)') = 'IO_SUBSYSTEM' ORDER BY [Event Time];             DROP TABLE #SystemHealthSessionData     END

   

 

   

   

수집된 결과를 엑셀등을 이용하여 (파워뷰 또는 수식을 이용한 비주얼라이제이션) 보고서를 만든다면 좋은 자료로 활용 될 수 있다.

   

   

   

[참고자료]

http://troubleshootingsql.com/2013/07/25/powerview-and-system-health-session-io-health/

   

   

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