sp_server_diagnostics(Transact-SQL)

적용 대상:SQL Server

잠재적 오류를 탐지하기 위해 SQL Server에 대한 진단 데이터 및 상태 정보를 캡처합니다. 이 프로시저는 반복 모드로 실행되며 주기적으로 결과를 보냅니다. 일반 연결 또는 전용 관리자 연결에서 호출할 수 있습니다.

Transact-SQL 구문 표기 규칙

구문

sp_server_diagnostics [ @repeat_interval = ] 'repeat_interval'
[ ; ]

인수

[ @repeat_interval = ] 'repeat_interval'

상태 정보를 보내기 위해 저장 프로시저가 반복적으로 실행되는 시간 간격을 나타냅니다.

@repeat_interval 기본값인 int입니다0. 유효한 매개 변수 값이거나 같거나 그 이상5인 값입니다0. 저장 프로시저는 전체 데이터를 반환하려면 5초 이상 실행해야 합니다. 반복 모드에서 실행할 저장 프로시저의 최소값은 5초입니다.

이 매개 변수를 지정하지 않았거나 지정된 값이면 저장 프로시저는 0데이터를 한 번 반환한 다음 종료합니다.

지정된 값이 최소값보다 작으면 오류가 발생하며 아무 것도 반환하지 않습니다.

지정된 값이 같거나 그 이상 5인 경우 저장 프로시저는 수동으로 취소될 때까지 상태를 반환하기 위해 반복적으로 실행됩니다.

반환 코드 값

0 (성공) 또는 1 (실패).

결과 집합

sp_server_diagnostics 는 다음 정보를 반환합니다.

다음은 다섯 가지 구성 요소에 대한 설명입니다.

• 시스템: 스핀 잠금, 심각한 처리 조건, 비수익 작업, 페이지 오류 및 CPU 사용량에 대한 시스템 관점에서 데이터를 수집합니다. 이 정보는 전반적인 상태 권장 사항을 생성합니다.

• 리소스: 물리적 및 가상 메모리, 버퍼 풀, 페이지, 캐시 및 기타 메모리 개체에 대한 리소스 관점에서 데이터를 수집합니다. 이 정보는 전반적인 상태 권장 사항을 생성합니다.

• query_processing: 작업자 스레드, 작업, 대기 유형, CPU 집약적 세션 및 차단 태스크에 대한 쿼리 처리 관점에서 데이터를 수집합니다. 이 정보는 전반적인 상태 권장 사항을 생성합니다.

• io_subsystem: IO에서 데이터를 수집합니다. 진단 데이터 외에도 이 구성 요소는 IO 하위 시스템에 대해서만 클린 정상 또는 경고 상태를 생성합니다.

• 이벤트: 링 버퍼 예외, 메모리 브로커에 대한 링 버퍼 이벤트, 메모리 부족, 스케줄러 모니터, 버퍼 풀, 스핀 잠금, 보안 및 연결에 대한 세부 정보를 포함하여 서버에서 기록된 관심 있는 오류 및 이벤트에 대한 저장 프로시저를 통해 데이터와 표면을 수집합니다. 이벤트는 항상 상태로 표시됩니다 0 .

• <가용성 그룹의 이름: 지정된 가용성 그룹>(있는 경우 component_type = "Always On:AvailabilityGroup")에 대한 데이터를 수집합니다.

설명

오류 관점에서 systemresource, 및 query_processing 구성 요소는 오류 검색 io_subsystemevents 에 사용되고 구성 요소는 진단 목적으로만 사용됩니다.

다음 표에서는 각 구성 요소와 관련 상태를 보여 줍니다.

각 행의 x 상태는 구성 요소에 대한 유효한 상태를 나타냅니다. 예를 들어 다음 io_subsystem 으로 clean 표시하거나 warning. 오류 상태는 표시되지 않습니다.

사용 권한

서버에 대한 VIEW SERVER STATE 권한이 필요합니다.

예제

확장 이벤트 세션을 사용하여 상태 정보를 캡처하고 SQL Server 외부에 있는 파일에 저장하는 것이 가장 좋습니다. 따라서 오류가 있는 경우에도 계속 액세스할 수 있습니다.

A. 확장 이벤트 세션의 출력을 파일에 저장

다음 예에서는 이벤트 세션의 출력을 파일에 저장합니다.

CREATE EVENT SESSION [diag]
ON SERVER
    ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)
    ADD TARGET [asynchronous_file_target] (set filename='C:\temp\diag.xel');
GO
ALTER EVENT SESSION [diag]
    ON SERVER STATE = start;
GO

B. 확장 이벤트 세션 로그 읽기

다음 쿼리는 SQL Server 2016(13.x)에서 확장 이벤트 세션 로그 파일을 읽습니다.

SELECT xml_data.value('(/event/@name)[1]', 'varchar(max)') AS Name,
    xml_data.value('(/event/@package)[1]', 'varchar(max)') AS Package,
    xml_data.value('(/event/@timestamp)[1]', 'datetime') AS 'Time',
    xml_data.value('(/event/data[@name=''component_type'']/value)[1]', 'sysname') AS SYSNAME,
    xml_data.value('(/event/data[@name=''component_name'']/value)[1]', 'sysname') AS Component,
    xml_data.value('(/event/data[@name=''state'']/value)[1]', 'int') AS STATE,
    xml_data.value('(/event/data[@name=''state_desc'']/value)[1]', 'sysname') AS State_desc,
    xml_data.query('(/event/data[@name="data"]/value/*)') AS Data
FROM (
    SELECT object_name AS event,
        CONVERT(XML, event_data) AS xml_data
    FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
) AS XEventData
ORDER BY TIME;

C. 테이블에 출력 캡처 sp_server_diagnostics

다음 예제에서는 반복이 아닌 모드에서 테이블의 sp_server_diagnostics 출력을 캡처합니다.

CREATE TABLE SpServerDiagnosticsResult (
    create_time DATETIME,
    component_type SYSNAME,
    component_name SYSNAME,
    [state] INT,
    state_desc SYSNAME,
    [data] XML
);

INSERT INTO SpServerDiagnosticsResult
EXEC sp_server_diagnostics;

다음 쿼리는 예제 테이블에서 요약 출력을 읽습니다.

SELECT create_time,
    component_name,
    state_desc
FROM SpServerDiagnosticsResult;

D. 각 구성 요소에서 자세한 출력 읽기

다음 예제 쿼리는 이전 예제에서 만든 테이블에서 각 구성 요소의 자세한 출력을 읽습니다.

시스템:

SELECT data.value('(/system/@systemCpuUtilization)[1]', 'bigint') AS 'System_CPU',
    data.value('(/system/@sqlCpuUtilization)[1]', 'bigint') AS 'SQL_CPU',
    data.value('(/system/@nonYieldingTasksReported)[1]', 'bigint') AS 'NonYielding_Tasks',
    data.value('(/system/@pageFaults)[1]', 'bigint') AS 'Page_Faults',
    data.value('(/system/@latchWarnings)[1]', 'bigint') AS 'Latch_Warnings',
    data.value('(/system/@BadPagesDetected)[1]', 'bigint') AS 'BadPages_Detected',
    data.value('(/system/@BadPagesFixed)[1]', 'bigint') AS 'BadPages_Fixed'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'system'
GO

리소스 모니터:

SELECT data.value('(./Record/ResourceMonitor/Notification)[1]', 'VARCHAR(max)') AS [Notification],
    data.value('(/resource/memoryReport/entry[@description=''Working Set'']/@value)[1]', 'bigint') / 1024 AS [SQL_Mem_in_use_MB],
    data.value('(/resource/memoryReport/entry[@description=''Available Paging File'']/@value)[1]', 'bigint') / 1024 AS [Avail_Pagefile_MB],
    data.value('(/resource/memoryReport/entry[@description=''Available Physical Memory'']/@value)[1]', 'bigint') / 1024 AS [Avail_Physical_Mem_MB],
    data.value('(/resource/memoryReport/entry[@description=''Available Virtual Memory'']/@value)[1]', 'bigint') / 1024 AS [Avail_VAS_MB],
    data.value('(/resource/@lastNotification)[1]', 'varchar(100)') AS 'LastNotification',
    data.value('(/resource/@outOfMemoryExceptions)[1]', 'bigint') AS 'OOM_Exceptions'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'resource'
GO

비선기적 대기:

SELECT waits.evt.value('(@waitType)', 'varchar(100)') AS 'Wait_Type',
    waits.evt.value('(@waits)', 'bigint') AS 'Waits',
    waits.evt.value('(@averageWaitTime)', 'bigint') AS 'Avg_Wait_Time',
    waits.evt.value('(@maxWaitTime)', 'bigint') AS 'Max_Wait_Time'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/topWaits/nonPreemptive/byDuration/wait') AS waits(evt)
WHERE component_name LIKE 'query_processing'
GO

선점 대기:

SELECT waits.evt.value('(@waitType)', 'varchar(100)') AS 'Wait_Type',
    waits.evt.value('(@waits)', 'bigint') AS 'Waits',
    waits.evt.value('(@averageWaitTime)', 'bigint') AS 'Avg_Wait_Time',
    waits.evt.value('(@maxWaitTime)', 'bigint') AS 'Max_Wait_Time'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/topWaits/preemptive/byDuration/wait') AS waits(evt)
WHERE component_name LIKE 'query_processing'
GO

CPU 집약적 요청:

SELECT cpureq.evt.value('(@sessionId)', 'bigint') AS 'SessionID',
    cpureq.evt.value('(@command)', 'varchar(100)') AS 'Command',
    cpureq.evt.value('(@cpuUtilization)', 'bigint') AS 'CPU_Utilization',
    cpureq.evt.value('(@cpuTimeMs)', 'bigint') AS 'CPU_Time_ms'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/cpuIntensiveRequests/request') AS cpureq(evt)
WHERE component_name LIKE 'query_processing'
GO

차단된 프로세스 보고서:

SELECT blk.evt.query('.') AS 'Blocked_Process_Report_XML'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/blockingTasks/blocked-process-report') AS blk(evt)
WHERE component_name LIKE 'query_processing'
GO

입력/출력:

SELECT data.value('(/ioSubsystem/@ioLatchTimeouts)[1]', 'bigint') AS 'Latch_Timeouts',
    data.value('(/ioSubsystem/@totalLongIos)[1]', 'bigint') AS 'Total_Long_IOs'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'io_subsystem'
GO

이벤트 정보:

SELECT xevts.evt.value('(@name)', 'varchar(100)') AS 'xEvent_Name',
    xevts.evt.value('(@package)', 'varchar(100)') AS 'Package',
    xevts.evt.value('(@timestamp)', 'datetime') AS 'xEvent_Time',
    xevts.evt.query('.') AS 'Event Data'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/events/session/RingBufferTarget/event') AS xevts(evt)
WHERE component_name LIKE 'events'
GO

관련 콘텐츠

• Failover Policy for Failover Cluster Instances