sp_server_diagnostics(Transact-SQL)

적용 대상:SQL Server

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

적용 대상: SQL Server(SQL Server 2012(11.x) 이상)

Transact-SQL 구문 표기 규칙

구문

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'   

인수

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

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

이 매개 변수를 지정하지 않거나 지정한 값이 0이면 저장 프로시저가 데이터를 한 번만 반환한 후 종료됩니다.

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

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

반환 코드 값

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

결과 집합

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

데이터 형식 설명
create_time datetime 행 만들기의 타임스탬프를 나타냅니다. 단일 행 집합의 각 행에는 동일한 타임스탬프를 가집니다.
component_type sysname 행에 SQL Server 인스턴스 수준 구성 요소 또는 Always On 가용성 그룹에 대한 정보가 포함되어 있는지 여부를 나타냅니다.

인스턴스

Always On:AvailabilityGroup
component_name sysname 구성 요소의 이름 또는 가용성 그룹의 이름을 나타냅니다.

시스템

resource

query_processing

io_subsystem

이벤트

<가용성 그룹의 이름>
state int 구성 요소의 상태를 나타냅니다.

0

1

2

3
state_desc sysname 상태 열을 설명합니다. 상태 열의 값에 해당하는 설명은 다음과 같습니다.

0: 알 수 없음

1: 클린

2: 경고

3: 오류
data varchar(max) 구성 요소와 관련된 데이터를 지정합니다.

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

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

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

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

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

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

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

설명

오류 관점에서 시스템, 리소스 및 query_processing 구성 요소는 오류 검색에 활용되고 io_subsystem 및 이벤트 구성 요소는 진단 목적으로만 활용됩니다.

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

구성 요소 정리 (1) 경고(2) 오류(3) 알 수 없음 (0)
시스템 x x x
resource x x x
query_processing x x x
io_subsystem x x
이벤트 x

각 행의 (x)는 구성 요소에 대한 유효한 상태를 나타냅니다. 예를 들어 io_subsystem 깨끗하거나 경고로 표시됩니다. 오류 상태는 표시되지 않습니다.

참고 항목

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

사용 권한

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

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

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  

아래 예제 쿼리는 확장 세션 로그 파일을 읽습니다.

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;  

다음 예제에서는 반복하지 않는 모드에서 테이블에 대한 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;  

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

-- system
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

-- Resource Monitor
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

-- Nonpreemptive waits
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

-- Preemptive waits
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 intensive requests
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

-- Blocked Process Report
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

-- IO
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

-- Event information
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