sp_server_diagnostics (Transact-SQL)

發行項
05/19/2015

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

語法

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 可用性群組的資訊：執行個體 alwaysOn:AvailabilityGroup
component_name	Sysname	指出元件的名稱或可用性群組的名稱：系統資源 query_processing io_subsystem 事件 <name of the availability group>
state	Int	指出元件的健全狀態： 0 1 2 3
state_desc	Sysname	描述狀態資料行。對應至狀態資料行值的描述如下： 0：未知 1：乾淨 2：警告 3：錯誤
data	Varchar (max)	指定元件的相關資料。

以下是五種元件的說明：

系統：從系統觀點來收集有關微調鎖定、嚴重處理條件、沒有產量的工作、分頁錯誤及 CPU 使用率的資料。這項資訊產生整體的健全狀態建議。
資源：從資源觀點來收集有關實體和虛擬記憶體、緩衝集區、頁面、快取和其他記憶體物件的資料。這項資訊產生整體的健全狀態建議。
query_processing：從查詢處理觀點來收集有關工作執行緒、工作、等候類型、需要大量 CPU 的工作階段和封鎖工作的資料。這項資訊產生整體的健全狀態建議。
io_subsystem：收集 IO 相關資料。除了診斷資料之外，這個元件只產生 IO 子系統的乾淨良好或警告的健全狀態。
事件：收集有關伺服器所記錄的相關錯誤和事件資料，包括信號緩衝區例外狀況，以及有關記憶體 Broker、記憶體不足、排程器監視器、緩衝集區、微調鎖定、安全性和連接性的信號緩衝區事件等詳細資料，並透過預存程序呈現。事件永遠會顯示狀態 0。
<name of the availability group>：收集指定可用性群組的資料 (如果 component_type = "alwaysOn:AvailabilityGroup")。

備註

從失敗觀點來看，系統、資源和 query_processing 元件將會用於失敗偵測，而 io_subsystem 和事件元件只供診斷之用。

下表將元件對應到其相關聯的健全狀態。

元件	乾淨 (1)	警告 (2)	錯誤 (3)	未知 (0)
system	x	x	x
resource	x	x	x
query_processing	x	x	x
io_subsystem	x	x
事件				x

每個資料列中的 (x) 表示元件的有效健全狀態。例如，io_subsystem 會顯示為乾淨或警告。它不會顯示錯誤狀態。

權限

需要伺服器的 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

請參閱

概念

容錯移轉叢集執行個體的容錯移轉原則

共用方式為

sp_server_diagnostics (Transact-SQL)

語法

引數

傳回碼值

結果集

備註

權限

範例

請參閱

概念

其他資源