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 の場合、このストアド プロシージャはデータを 1 回返して終了します。

  指定した値が最小値よりも小さい場合、エラーが発生し、データは返されません。

  指定した値が 5 以上の場合、このストアド プロシージャは、手動でキャンセルされるまで繰り返して正常性状態を返します。

リターン コード値

0 (成功) または 1 (失敗)

結果セット

sp_server_diagnostics は、次の情報を返します。

5 つのコンポーネントの説明は以下のとおりです。

• system: システムの観点から、スピンロック、サーバー処理の条件、応答していないタスク、ページ フォールト、および CPU 使用率のデータを収集します。 この情報から、全体的な正常性状態の推奨設定が生成されます。

• resource: リソースの観点から、物理メモリおよび仮想メモリ、バッファー プール、ページ、キャッシュおよびその他のメモリ オブジェクトのデータを収集します。 この情報から、全体的な正常性状態の推奨設定が生成されます。

• query_processing: クエリ処理の観点から、ワーカー スレッド、タスク、待機の種類、CPU を集中的に使用するセッション、およびブロックしているタスクの情報を収集します。 この情報から、全体的な正常性状態の推奨設定が生成されます。

• io_subsystem: IO のデータを収集します。 このコンポーネントは診断データのほかに、IO サブシステムのみについてクリーンまたは警告の正常性状態を生成します。

• events: ストアド プロシージャを介して、サーバーによって記録されたエラーと対象イベント (リング バッファーの例外の詳細、メモリ ブローカーに関するリング バッファー イベント、メモリ不足、スケジューラ モニター、バッファー プール、スピンロック、セキュリティ、および接続を含む) のデータを収集して表示します。 イベント状態としては、常に 0 が表示されます。

• <name of the availability group>: 指定された可用性グループのデータを収集します (component_type = "alwaysOn:AvailabilityGroup" の場合)。

説明

障害の観点からは、system、resource、query_processing の各コンポーネントは障害の検出に利用され、io_subsystem および events コンポーネントは診断目的のみに利用されます。

次の表は、各コンポーネントと関連する正常性状態をマップしたものです。

各行の (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

関連項目

概念

フェールオーバー クラスター インスタンスのフェールオーバー ポリシー