分享方式:


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。 有效的參數值是 0, 或任何等於或大於 5的值。 預存程式必須至少執行 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) 指定元件特定的數據。

以下是五個元件的描述:

  • system:從系統的觀點收集數據,以微調鎖定、嚴重處理條件、不產生工作、頁面錯誤和 CPU 使用量。 這項資訊會產生整體健全狀況狀態建議。

  • resource:從資源觀點收集實體和虛擬記憶體、緩衝池、頁面、快取和其他記憶體對象的數據。 這項資訊會產生整體健全狀況狀態建議。

  • query_processing:從查詢處理的觀點收集背景工作線程、工作、等候類型、CPU 密集會話和封鎖工作的數據。 這項資訊會產生整體健全狀況狀態建議。

  • io_subsystem:收集IO上的數據。 除了診斷數據之外,此元件只會針對IO子系統產生全新的狀況良好或警告健康情況狀態。

  • 事件:透過伺服器所記錄之錯誤和事件上的預存程式收集數據和表面,包括有關信號緩衝區例外狀況的詳細數據、記憶體代理程式的通道緩衝區事件、記憶體不足、排程器監視器、緩衝池、微調鎖定、安全性和連線能力。 事件一律會顯示 0 為狀態。

  • <可用性群組>的名稱:收集指定可用性群組的數據(如果 component_type = "Always On:AvailabilityGroup"為 )。

備註

從失敗的觀點來看,systemresource、 和 query_processing 元件會用於失敗偵測,而 io_subsystemevents 元件則僅用於診斷用途。

下表會將元件對應至其相關聯的健康情況狀態。

元件 乾淨 (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 顯示為 cleanwarning。 它不會顯示錯誤狀態。

注意

內部 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