sp_server_diagnostics (Transact-SQL)

このページはお役に立ちましたか?

追加のフィードバックはありますか?

フィードバックが Microsoft に送信されます。[送信] ボタンを押すと、お客様からのフィードバックが Microsoft 製品とサービスの品質向上のために使用されます。 プライバシー ポリシー。

適用対象:SQL Server (サポートされているすべてのバージョン)

潜在的な障害を検出するために、SQL Server に関する診断データと正常性の情報をキャプチャします。 プロシージャは繰り返しモードで実行され、結果は定期的に送信されます。 通常の接続または DAC 接続から呼び出すことができます。

適用対象: SQL Server ( SQL Server 2012 (11.x) 以降)。

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 次の情報を返します。

列	データ型	説明
create_time	datetime	行の作成のタイムスタンプを示します。 単一の行セットの各行は、同じタイムスタンプを持っています。
component_type	sysname	行に、インスタンス レベル コンポーネントまたは可用性グループのSQL Server情報が含まれているAlways On示します。 instance Always On:AvailabilityGroup
component_name	sysname	コンポーネントの名前または可用性グループの名前を示します。 システム resource query_processing io_subsystem events 可用性グループの名前>
state	int	コンポーネントの正常性状態を示します。 0 1 2 3
state_desc	sysname	状態列について説明します。 state 列の値に対応する説明は次のとおりです。 0: 不明 1: クリーン 2: 警告 3: エラー
data	varchar (max)	コンポーネントに固有のデータを指定します。

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

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

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

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

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

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

• 可用性グループの名前>: 指定された可用性グループのデータを収集します (component_type = "Always On:AvailabilityGroup")。

注釈

障害の観点からは、システム、リソース、query_processing コンポーネントが障害検出に利用され、io_subsystem コンポーネントとイベント コンポーネントは診断目的でのみ利用されます。

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

コンポーネント	クリーン (1)	警告 (2)	エラー (3)	不明 (0)
システム	x	x	x
resource	x	x	x
query_processing	x	x	x
io_subsystem	x	x
events				x

各行の (x) は、そのコンポーネントに対して有効な正常性状態を表します。 たとえば、io_subsystemまたは警告として表示されます。 エラー状態は表示されない。

Note

内部プロシージャsp_server_diagnostics実行は、優先度の高いプリエプティブ スレッドに実装されます。

アクセス許可

サーバーに対する 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\MSSQL13.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 xml  
);  
INSERT INTO SpServerDiagnosticsResult  
EXEC sp_server_diagnostics; 

次のクエリ例では、テーブルから出力の概要を読み取ります。

SELECT create_time,
       component_name,
       state_desc 
FROM SpServerDiagnosticsResult;  

次のクエリ例では、テーブルの各コンポーネントから詳細な出力の一部を読み取ります。

-- system
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

-- Resource Monitor
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

-- Nonpreemptive waits
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

-- Preemptive waits
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 intensive requests
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

-- Blocked Process Report
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

-- IO
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

-- Event information
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