다음을 통해 공유


sp_server_diagnostics(Transact-SQL)

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

항목 링크 아이콘 Transact-SQL 구문 표기 규칙

구문

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'

인수

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

    repeat_interval_in_seconds는 int이며 기본값은 0입니다. 올바른 매개 변수 값은 0 또는 5보다 크거나 같은 값입니다. 전체 데이터를 반환하려면 저장 프로시저가 적어도 5초 간격으로 실행되어야 합니다. 반복 모드에서 실행되는 저장 프로시저의 최소값은 5초입니다.

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

    지정한 값이 최소값보다 작으면 오류가 발생하고 아무 결과도 반환되지 않습니다.

    지정한 값이 5보다 크거나 같으면 저장 프로시저가 수동으로 취소할 때까지 반복적으로 실행되어 상태를 반환합니다.

반환 코드 값

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

결과 집합

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

데이터 형식

설명

creation_time

Datetime

행 만들기의 타임스탬프를 나타냅니다. 단일 행 집합의 각 행은 타임스탬프가 같습니다.

component_type

Sysname

행에 SQL Server 인스턴스 수준 구성 요소에 대한 정보가 들어 있는지 또는 AlwaysOn 가용성 그룹에 대한 정보가 들어 있는지를 나타냅니다.

  • instance

  • alwaysOn:AvailabilityGroup

component_name

Sysname

구성 요소의 이름이나 가용성 그룹의 이름을 나타냅니다.

  • system

  • resource

  • query_processing

  • io_subsystem

  • events

  • <name of the availability group>

state

Int

구성 요소의 상태를 나타냅니다.

  • 0

  • 1

  • 2

  • 3

state_desc

Sysname

state 열을 설명합니다. state 열의 값에 해당하는 설명은 다음과 같습니다.

  • 0:Unknown

  • 1:clean

  • 2:warning

  • 3:error

data

Varchar (max)

구성 요소와 관련된 데이터를 지정합니다.

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

  • system: 시스템 큐브 뷰에서 spinlock, 엄격한 처리 조건, 잠겨 있는 태스크, 페이지 폴트 및 CPU 사용에 대한 데이터를 수집합니다. 이 정보는 전반적인 상태 권장 사항을 생성합니다.

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

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

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

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

  • <name of the availability group>: 지정된 가용성 그룹의 데이터를 수집합니다(component_type = "alwaysOn:AvailabilityGroup"인 경우).

주의

오류 큐브 뷰에서 시스템, 리소스 및 쿼리 처리 구성 요소는 오류 감지에 활용되고, IO 하위 시스템 및 이벤트 구성 요소는 진단용으로만 활용됩니다.

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

구성 요소

정상(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 하위 시스템은 정상 또는 경고로 표시됩니다. 오류 상태는 표시되지 않습니다.

사용 권한

서버에 대한 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\MSSQL11.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 nvarchar(max)
)
INSERT INTO SpServerDiagnosticsResult 
EXEC sp_server_diagnostics

참고 항목

개념

장애 조치(failover) 클러스터 인스턴스용 장애 조치(failover) 정책