sp_server_diagnostics (Transact-SQL)
捕获有关 SQL Server 的诊断数据和运行状况信息,以检测潜在故障。该过程以重复模式运行,并定期发送结果。 可通过常规连接或 DAC 连接调用该过程。
语法
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 可用性组:
|
component_name |
Sysname |
指示组件的名称或可用性组的名称:
|
state |
Int |
指示组件的运行状况状态:
|
state_desc |
Sysname |
描述状态列。 与状态列中的值对应的说明:
|
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