sp_server_diagnostics (Transact-SQL)

项目
04/10/2024

捕获有关 SQL Server 的诊断数据和运行状况信息，以检测潜在故障。该过程以重复模式运行，并定期发送结果。可以从常规连接或专用管理员连接调用它。

语法

sp_server_diagnostics [ @repeat_interval = ] 'repeat_interval'
[ ; ]

参数

[ @repeat_interval = ] 'repeat_interval'

指示存储过程重复运行以发送运行状况信息的时间间隔。

@repeat_interval默认值为 0int。有效的参数值或 0等于或大于 5的任何值。存储过程至少要运行 5 秒钟才能返回完整数据。存储过程以重复模式运行的最短时间为 5 秒。

如果未指定此参数，或者指定值为 0，则存储过程一次返回数据，然后退出。

如果指定的值小于最小值，则会引发错误并返回任何值。

如果指定的值等于或大于 5，则存储过程将重复运行以返回运行状况状态，直到手动取消。

返回代码值

0（成功）或 1（失败）。

结果集

sp_server_diagnostics 返回以下信息。

列	数据类型	描述
`create_time`	datetime	指示行创建的时间戳。单个行集中的每一行都具有相同的时间戳。
`component_type`	sysname	指示行是否包含 SQL Server 实例级别组件或 AlwaysOn 可用性组的信息： `instance` `Always On:AvailabilityGroup`
`component_name`	sysname	指示组件的名称或可用性组的名称： `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`	int	指示组件的运行状况。可以是以下值之一：`0`、、`12`或`3`
`state_desc`	sysname	描述状态列。与状态列中的值对应的说明： 0: `Unknown` 1: `clean` 2: `warning` 3: `error`
`data`	varchar （max）	指定特定于组件的数据。

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

系统：从系统角度收集数据，了解旋转锁、严重处理条件、非生成任务、页面错误和 CPU 使用率。此信息会提供总体运行状态建议。
资源：从资源角度收集数据，了解物理内存和虚拟内存、缓冲池、页面、缓存和其他内存对象。此信息会提供总体运行状态建议。
query_processing：从查询处理角度收集数据，了解工作线程、任务、等待类型、CPU 密集型会话和阻止任务。此信息会提供总体运行状态建议。
io_subsystem：收集有关 IO 的数据。除了诊断数据外，此组件还可生成仅适用于 IO 子系统的干净运行状况或警告运行状态。
事件：通过服务器记录的错误和事件的存储过程收集数据和图面，包括有关环形缓冲区异常的详细信息、有关内存中转站的环形缓冲区事件、内存不足、计划程序监视器、缓冲池、旋转锁、安全性和连接性的详细信息。事件始终显示为 0 状态。
<可用性组>的名称：收集指定可用性组的数据（如果 component_type = "Always On:AvailabilityGroup"）。

注解

从故障的角度来看，systemresourcequery_processing和组件用于故障检测，而io_subsystem组件events仅用于诊断目的。

下表将组件映射到其关联的运行状态。

组件	干净 (1)	警告 (2)	错误 (3)	未知 (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

每 x 行中表示组件的有效运行状况状态。例如， io_subsystem 显示为 clean 或 warning。它不显示错误状态。

注意

内部 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. 读取每个组件的详细输出

相关内容

反馈

其他资源

°C 将输出捕获 `sp_server_diagnostics` 到表