sp_server_diagnostics (Transact-SQL)

文章
04/10/2024

適用於：SQL Server

擷取有關 SQL Server 的診斷資料和健全狀況資訊，以偵測潛在的失敗。此程序會以重複模式執行，並定期傳送結果。您可以從一般連線或專用管理員連線叫用它。

語法

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	指出元件的健全狀態。可以是下列其中一個值： `0`、 `1`、 `2`或 `3`
`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_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` 數據表的輸出