sp_server_diagnostics(Transact-SQL)
잠재적 오류를 감지하기 위해 SQL Server에 대한 진단 데이터 및 상태 정보를 캡처합니다. 이 프로시저는 반복 모드로 실행되며 주기적으로 결과를 보냅니다. 일반 연결 또는 DAC 연결에서 호출할 수 있습니다.
.gif "항목 링크 아이콘")
구문
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 가용성 그룹에 대한 정보가 들어 있는지를 나타냅니다.
|
component_name |
Sysname |
구성 요소의 이름이나 가용성 그룹의 이름을 나타냅니다.
|
state |
Int |
구성 요소의 상태를 나타냅니다.
|
state_desc |
Sysname |
state 열을 설명합니다. state 열의 값에 해당하는 설명은 다음과 같습니다.
|
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