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 可用性群組的資訊：

  • 執行個體

  • 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

請參閱

概念

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