sp_server_diagnostics(Transact-SQL)

아티클
04/10/2024

적용 대상: 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 는 다음 정보를 반환합니다.

열	데이터 형식	설명
`create_time`	datetime	행 만들기의 타임스탬프를 나타냅니다. 단일 행 집합의 각 행에는 동일한 타임스탬프를 가집니다.
`component_type`	sysname	행에 SQL Server 인스턴스 수준 구성 요소 또는 Always On 가용성 그룹에 대한 정보가 포함되어 있는지 여부를 나타냅니다. `instance` `Always On:AvailabilityGroup`
`component_name`	sysname	구성 요소의 이름 또는 가용성 그룹의 이름을 나타냅니다. `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`	int	구성 요소의 상태를 나타냅니다. 다음 값 중 하나일 수 있습니다. `0123`
`state_desc`	sysname	상태 열을 설명합니다. 상태 열의 값에 해당하는 설명은 다음과 같습니다. 0: `Unknown` 1: `clean` 2: `warning` 3: `error`
`data`	varchar(max)	구성 요소와 관련된 데이터를 지정합니다.

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

시스템: 스핀 잠금, 심각한 처리 조건, 비수익 작업, 페이지 오류 및 CPU 사용량에 대한 시스템 관점에서 데이터를 수집합니다. 이 정보는 전반적인 상태 권장 사항을 생성합니다.
리소스: 물리적 및 가상 메모리, 버퍼 풀, 페이지, 캐시 및 기타 메모리 개체에 대한 리소스 관점에서 데이터를 수집합니다. 이 정보는 전반적인 상태 권장 사항을 생성합니다.
query_processing: 작업자 스레드, 작업, 대기 유형, CPU 집약적 세션 및 차단 태스크에 대한 쿼리 처리 관점에서 데이터를 수집합니다. 이 정보는 전반적인 상태 권장 사항을 생성합니다.
io_subsystem: IO에서 데이터를 수집합니다. 진단 데이터 외에도 이 구성 요소는 IO 하위 시스템에 대해서만 깨끗한 정상 또는 경고 상태 상태를 생성합니다.
이벤트: 링 버퍼 예외, 메모리 브로커에 대한 링 버퍼 이벤트, 메모리 부족, 스케줄러 모니터, 버퍼 풀, 스핀 잠금, 보안 및 연결에 대한 세부 정보를 포함하여 서버에서 기록된 관심 있는 오류 및 이벤트에 대한 저장 프로시저를 통해 데이터와 표면을 수집합니다. 이벤트는 항상 상태로 표시됩니다 0 .
<가용성 그룹의 이름: 지정된 가용성 그룹>(있는 경우 component_type = "Always On:AvailabilityGroup")에 대한 데이터를 수집합니다.

설명

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

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

구성 요소	정리 (1)	경고(2)	오류(3)	알 수 없음 (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

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

참고 항목

sp_server_diagnostics 내부 프로시저는 우선 순위가 높은 선점 스레드에서 구현됩니다.

사용 권한

서버에 대한 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

다음을 통해 공유

sp_server_diagnostics(Transact-SQL)

구문

인수

[ @repeat_interval = ] 'repeat_interval'

반환 코드 값

결과 집합

설명

사용 권한

예제

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

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

C. 테이블에 출력 캡처 `sp_server_diagnostics`

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

피드백

추가 리소스

다음을 통해 공유

sp_server_diagnostics(Transact-SQL)

구문

인수

[ @repeat_interval = ] 'repeat_interval'

반환 코드 값

결과 집합

설명

사용 권한

예제

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

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

C. 테이블에 출력 캡처 sp_server_diagnostics

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

관련 콘텐츠

피드백

추가 리소스

C. 테이블에 출력 캡처 `sp_server_diagnostics`