sp_server_diagnostics (Transact-SQL)sp_server_diagnostics (Transact-SQL)

適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) 適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions)

潜在的な障害を検出するために、SQL ServerSQL Server に関する診断データと正常性の情報をキャプチャします。Captures diagnostic data and health information about SQL ServerSQL Server to detect potential failures. プロシージャは繰り返しモードで実行され、結果は定期的に送信されます。The procedure runs in repeat mode and sends results periodically. 通常の接続または DAC 接続から呼び出すことができます。It can be invoked from either a regular or a DAC connection.

適用対象: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以降)。Applies to: SQL ServerSQL Server ( SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later).

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'   

引数Arguments

[ @repeat_interval = ] 'repeat_interval_in_seconds' 正常性情報を送信するために、ストアドプロシージャが繰り返し実行される時間間隔を示します。[ @repeat_interval = ] 'repeat_interval_in_seconds' Indicates the time interval at which the stored procedure will run repeatedly to send health information.

repeat_interval_in_secondsint で、既定値は0です。repeat_interval_in_seconds is int with the default of 0. 有効なパラメーター値は0、または5以上の値です。The valid parameter values are 0, or any value equal to or more than 5. ストアドプロシージャは、完全なデータを返すために少なくとも5秒間実行する必要があります。The stored procedure has to run at least 5 seconds to return complete data. 繰り返しモードで実行するストアドプロシージャの最小値は5秒です。The minimum value for the stored procedure to run in the repeat mode is 5 seconds.

このパラメーターが指定されていない場合、または指定した値が 0 の場合、このストアド プロシージャはデータを 1 回返して終了します。If this parameter is not specified, or if the specified value is 0, the stored procedure will return data one time and then exit.

指定された値が最小値より小さい場合は、エラーが発生し、何も返されません。If the specified value is less than the minimum value, it will raise an error and return nothing.

指定した値が 5 以上の場合、このストアド プロシージャは、手動でキャンセルされるまで繰り返して正常性状態を返します。If the specified value is equal to or more than 5, the stored procedure runs repeatedly to return the health state until it is manually canceled.

リターン コードの値Return Code Values

0 (成功) または 1 (失敗)0 (success) or 1 (failure)

結果セットResult Sets

sp_server_diagnostics は次の情報を返します。sp_server_diagnostics returns the following information

Column データ型Data type 説明Description
create_timecreate_time datetimedatetime 行の作成のタイムスタンプを示します。Indicates the time stamp of row creation. 単一の行セットの各行は、同じタイムスタンプを持っています。Each row in a single rowset has the same time stamp.
component_typecomponent_type sysnamesysname 行に SQL ServerSQL Server インスタンスレベルコンポーネントまたは Always On 可用性グループの情報が含まれているかどうかを示します。Indicates whether the row contains information for the SQL ServerSQL Server instance level component or for an Always On availability group:

インスタンスinstance

Always On: AvailabilityGroupAlways On:AvailabilityGroup
component_namecomponent_name sysnamesysname コンポーネントの名前または可用性グループの名前を示します。Indicates the name of component or the name of the availability group:

システムsystem

resourceresource

query_processingquery_processing

io_subsystemio_subsystem

eventsevents

<name of the availability group>
statestate intint コンポーネントの正常性状態を示します。Indicates the health status of the component:

00

11

22

33
state_descstate_desc sysnamesysname 状態列について説明します。Describes the state column. State 列の値に対応する説明は次のとおりです。Descriptions that correspond to the values in the state column are:

0: 不明0: Unknown

1: クリーン1: clean

2: 警告2: warning

3: エラー3: error
datadata varchar (max)varchar (max) コンポーネントに固有のデータを指定します。Specifies data that is specific to the component.

5つのコンポーネントの説明を次に示します。Here are the descriptions of the five components:

  • システム: システムの観点から、スピンロック、重大な処理条件、応答しないタスク、ページフォールト、および CPU 使用率のデータを収集します。system: Collects data from a system perspective on spinlocks, severe processing conditions, non-yielding tasks, page faults, and CPU usage. この情報から、全体的な正常性状態の推奨設定が生成されます。This information is produces an overall health state recommendation.

  • リソース: 物理メモリ、仮想メモリ、バッファープール、ページ、キャッシュ、およびその他のメモリオブジェクトについて、リソースの観点からデータを収集します。resource: Collects data from a resource perspective on physical and virtual memory, buffer pools, pages, cache and other memory objects. この情報によって、全体的な正常性状態の推奨事項が生成されます。This information produces an overall health state recommendation.

  • query_processing: ワーカースレッド、タスク、待機の種類、CPU を集中的に消費するセッション、およびブロックしているタスクのクエリ処理の観点からデータを収集します。query_processing: Collects data from a query-processing perspective on the worker threads, tasks, wait types, CPU intensive sessions, and blocking tasks. この情報によって、全体的な正常性状態の推奨事項が生成されます。This information produces an overall health state recommendation.

  • io_subsystem: io でデータを収集します。io_subsystem: Collects data on IO. このコンポーネントは、診断データだけでなく、IO サブシステムに対してのみクリーンな正常性状態または警告状態を生成します。In addition to diagnostic data, this component produces a clean healthy or warning health state only for an IO subsystem.

  • イベント: サーバーによって記録されたエラーとイベントについて、ストアドプロシージャを介してデータを収集します。これには、リングバッファーの例外の詳細、メモリブローカーに関するリングバッファーイベント、メモリ不足、スケジューラモニター、バッファープール、スピンロック、セキュリティ、および接続が含まれます。events: Collects data and surfaces through the stored procedure on the errors and events of interest recorded by the server, including details about ring buffer exceptions, ring buffer events about memory broker, out of memory, scheduler monitor, buffer pool, spinlocks, security, and connectivity . イベントには、常に状態として0が表示されます。Events will always show 0 as the state.

  • <name of the availability group>: 指定された可用性グループのデータを収集します (component_type が "Always On: AvailabilityGroup" の場合)。<name of the availability group>: Collects data for the specified availability group (if component_type = "Always On:AvailabilityGroup").

コメントRemarks

障害の観点から見ると、システム、リソース、および query_processing コンポーネントはエラーの検出に利用され、io_subsystem とイベントのコンポーネントは診断目的でのみ利用されます。From a failure perspective, the system, resource, and query_processing components will be leveraged for failure detection while the io_subsystem and events components will be leveraged for diagnostic purposes only.

次の表は、コンポーネントと関連する正常性状態の対応を示しています。The following table maps the components to their associated health states.

コンポーネントComponents クリーン (1)Clean (1) 警告 (2)Warning (2) エラー (3)Error (3) 不明 (0)Unknowns (0)
システムsystem xx xx xx
resourceresource xx xx xx
query_processingquery_processing xx xx xx
io_subsystemio_subsystem xx xx
eventsevents xx

各行の (x) は、そのコンポーネントに対して有効な正常性状態を表します。The (x) in each row represents valid health states for the component. たとえば、io_subsystem はクリーンまたは警告として表示されます。For example, io_subsystem will either show as clean or warning. エラー状態は表示されません。It will not show the error states.

注意

Sp_server_diagnostics の内部プロシージャの実行は、優先度の高いプリエンプティブスレッドで実装されます。Execution of sp_server_diagnostics internal procedure is implemented on a preemptive thread at high priority.

アクセス許可Permissions

サーバーに対する VIEW SERVER STATE 権限が必要です。Requires VIEW SERVER STATE permission on the server.

Examples

拡張セッションを使用して正常性の情報をキャプチャし、SQL Server の外部にあるファイルに保存することをお勧めします。It is best practice to use the extended sessions to capture the health information and save it to a file that is located outside of SQL Server. そのため、エラーが発生してもアクセスできます。Therefore, you can still access it if there is a failure. 次の例は、イベント セッションからの出力をファイルに保存します。The following example saves the output from an event session to a file:

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  

次のクエリ例では、拡張セッションログファイルを読み取ります。The example query below reads the extended session log file:

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 の出力を非繰り返しモードでテーブルにキャプチャします。The following example captures the output of sp_server_diagnostics to a table in a non-repeat mode:

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; 

次のクエリ例では、テーブルから出力の概要を読み取ります。The example query below reads the summary output from the table:

SELECT create_time,
       component_name,
       state_desc 
FROM SpServerDiagnosticsResult;  

次のクエリ例では、テーブルの各コンポーネントから詳細な出力の一部を読み取ります。The example query below reads some of the detailed output from the each component in the table:

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

参照See Also

Failover Policy for Failover Cluster InstancesFailover Policy for Failover Cluster Instances