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データを 1 回返してから終了します。

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

指定した値が等しいかそれ以上 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)	コンポーネントに固有のデータを指定します。

5 つのコンポーネントの説明を次に示します。

システム: スピンロック、重大な処理条件、非生成タスク、ページフォールト、CPU 使用率に関するシステムの観点からデータを収集します。この情報により、全体的な正常性状態に関する推奨事項が生成されます。
リソース: 物理メモリと仮想メモリ、バッファープール、ページ、キャッシュ、およびその他のメモリオブジェクトに関するリソースの観点からデータを収集します。この情報により、全体的な正常性状態に関する推奨事項が生成されます。
query_processing: ワーカースレッド、タスク、待機の種類、CPU 負荷の高いセッション、ブロックタスクに関するクエリ処理の観点からデータを収集します。この情報により、全体的な正常性状態に関する推奨事項が生成されます。
io_subsystem: IO 上のデータを収集します。診断データに加えて、このコンポーネントは IO サブシステムに対してのみ、正常または警告の正常性状態クリーンを生成します。
events: リングバッファー例外、メモリブローカーに関するリングバッファーイベント、メモリ不足、スケジューラモニター、バッファープール、スピンロック、セキュリティ、接続に関する詳細など、サーバーによって記録された対象のエラーとイベントに関するデータとサーフェスをストアドプロシージャを通じて収集します。イベントは常に状態として表示 0 されます。
<可用性グループの名前: 指定された可用性グループ>のデータを収集します (場合 component_type = "Always On:AvailabilityGroup")。

解説

障害の観点から見ると、エラー検出には system、コンポーネント resource、およびquery_processingコンポーネントが使用io_subsystemeventsされ、コンポーネントは診断目的でのみ使用されます。

次の表は、コンポーネントを関連する正常性状態にマップします。

コンポーネント	クリーン (1)	警告 (2)	Error (3)	不明 (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

各行は x 、コンポーネントの有効な正常性状態を表します。たとえば、次のように表示cleanするかwarning、 io_subsystem エラー状態は表示されません。

Note

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. 各コンポーネントから詳細な出力を読み取る

関連するコンテンツ

フィードバック

フィードバック

その他のリソース

リターンコードの値

A. 拡張イベントセッションからの出力をファイルに保存する

B. 拡張イベントセッションログの読み取り

C: テーブルへの出力をキャプチャ `sp_server_diagnostics` する