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/



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp




No. Subject Author Date Views
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 467
1666 Event Session을 이용한 Memory Health check jevida(강성욱) 2016.01.11 6965
1665 Event Session을 이용한 CPU Health check jevida(강성욱) 2016.01.11 8462
» DMV를 이용한 I/O Health check jevida(강성욱) 2016.01.11 7357
1663 CONCAT 함수 (문자열 연결하기)와 주의 사항 jevida(강성욱) 2016.01.11 28085
1662 SQL Server 2012 Columnstore Index jevida(강성욱) 2016.01.11 7571
1661 SQL Server 2012 File Stream / Table 관련 DMV jevida(강성욱) 2016.01.11 4881
1660 SQL Server2012 DMV – 메모리 덤프, 서비스, 구성 확인 jevida(강성욱) 2016.01.11 4809
1659 SQL Server2012 향상된 디버깅 기능 jevida(강성욱) 2016.01.11 4395
1658 SQL Server2012 매개 변수 구성 jevida(강성욱) 2016.01.11 3508
1657 SQL Server2012 도움말 설치 jevida(강성욱) 2016.01.11 3472
1656 SQL Server2012 Checkpoint 제어 jevida(강성욱) 2016.01.11 5347
1655 SSIS 패지키의 메타데이터 유효성 검사 중지하기 jevida(강성욱) 2016.01.11 2978
1654 SSIS 로그 보관기간 설정 (CleanUp 기간 설정) jevida(강성욱) 2016.01.11 4337
1653 SSIS 패키지 보호 레벨 jevida(강성욱) 2016.01.11 3897
1652 DATABASE의 TABLE 및 COLUMN COMMENT 출력 joe 2015.09.10 7773
1651 백업 히스토리 모니터링 jevida(강성욱) 2015.03.31 11638
1650 백업 전략과 Differential Database Backup jevida(강성욱) 2015.03.31 11238
1649 SQL ServerPoint in time recovery jevida(강성욱) 2015.03.31 9067
1648 Striping SQL Server Database Backup jevida(강성욱) 2015.03.31 8705
1647 SQL 서버 감사가 있는 데이터베이스 복원 후 설정 jevida(강성욱) 2015.03.31 11920





XE Login