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 可用性组：

  • instance

  • alwaysOn:AvailabilityGroup

component_name

Sysname

指示组件的名称或可用性组的名称：

  • system

  • resource

  • query_processing

  • io_subsystem

  • events

  • <name of the availability group>

state

Int

指示组件的运行状况状态：

  • 0

  • 1

  • 2

  • 3

state_desc

Sysname

描述状态列。 与状态列中的值对应的说明：

  • 0：未知

  • 1：清理

  • 2：警告

  • 3：错误

data

Varchar (max)

指定特定于组件的数据。

下面是对五个组件的说明：

  • 系统：从系统角度收集有关 spinlock、严重的处理情况、无法完成的任务、页面错误和 CPU 使用情况的数据。 此信息会产生总体运行状态建议。

  • 资源：从资源的角度收集有关物理和虚拟内存、缓存池、页面、缓存和其他内存对象的数据。 此信息会提供总体运行状态建议。

  • query_processing：从查询处理的角度收集有关工作线程、任务、等待类型、CPU 密集型会话和正在阻塞的任务的数据。 此信息会提供总体运行状态建议。

  • io_subsystem：收集有关 IO 的数据。 除了诊断数据外，此组件还可生成仅适用于 IO 子系统的干净运行状况或警告运行状态。

  • 事件：通过服务器记录的相关错误和事件中的存储过程收集数据和图面，包括有关环形缓冲区异常、有关内存 broker 的环形缓冲区事件、内存不足、计划程序监视、缓冲区池、spinlock、安全和连接的详细信息。 事件将始终显示 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

请参阅

概念

故障转移群集实例的故障转移策略