使用sp_server_diagnostics

Troubleshooting SQL Server is all about gathering the right evidence. Ordinarily we utilise a variety of different methods and analyse their output to look for specific areas where we would focus our diagnostic efforts. We could for example, use the results of various DMVs to look at wait statistic and resource information to help us focus our investigation in a particular area of SQL Server.

对SQL Server进行故障排除只是为了收集正确的证据。 通常，我们使用各种不同的方法并分析其输出，以寻找我们将集中精力进行诊断工作的特定领域。 例如，我们可以使用各种DMV的结果来查看等待统计信息和资源信息，以帮助我们将调查重点放在SQL Server的特定领域。

One available option that I haven’t seen commonly used is a system stored procedure introduced in SQL Server 2012 called sys.sp_server_diagnostics. This stored procedure provides a quick assessment of a SQL instance by capturing and returning health and event related information that is conveniently categorised for us.

我没有看到的一个常用的常用选项是SQL Server 2012中引入的系统存储过程sys.sp_server_diagnostics。 该存储过程通过捕获并返回方便地归类给我们的与运行状况和事件相关的信息来提供对SQL实例的快速评估。

To execute, using a regular or admin connection, run the following T-SQL command:

要使用常规或管理员连接执行，请运行以下T-SQL命令：

 
  EXEC sp_server_diagnostics
 

By default the capture process lasts for duration of five seconds before the output is returned to the results grid as displayed in the image below:

默认情况下，捕获过程将持续五秒钟，然后将输出返回到结果网格，如下图所示：

The stored procedure can also be executed in a repeated mode where a time interval can be passed and execution will repeat continuously (until cancelled) and the output will be sent to the results pane each stated interval duration:

存储过程也可以在重复模式下执行，在该模式下可以经过一个时间间隔，并且执行将连续重复（直到取消），并且每个指定的间隔持续时间将输出发送到结果窗格：

 
  EXEC sp_server_diagnostics 5
 

Each returned row represents a particular category of health-related information:

返回的每一行代表与健康相关的信息的特定类别：

System

系统

Returns system data on items such as spinlocks, latch warnings, page faults, CPU utilisation and other performance conditions.

返回有关自旋锁，闩锁警告，页面错误，CPU利用率和其他性能条件的项目的系统数据。

Resource

资源资源

The resource category focuses on memory related objects including, amongst other items, available physical and virtual memory, free page information and memory low flags.

资源类别集中于与内存相关的对象，除其他项目外，还包括可用的物理和虚拟内存，可用页面信息和内存不足标志。

Query_Processing

查询处理

Query Processing returns cumulative wait statistic information including the top preemptive and non-preemptive waits ordered by both count and wait duration.

查询处理返回累积的等待统计信息，包括按计数和等待时间排序的优先抢占式和非抢占式等待。

IO_Subsystem

IO_子系统

Returns IO information such as IO related timeouts and pending IO requests.

返回IO信息，例如与IO相关的超时和未决的IO请求。

Events

大事记

The events category returns comprehensive event information retrieved during the execution duration.

事件类别返回在执行期间检索到的全面事件信息。

A sixth category is present when running the stored procedure on an instance where Always On Availability Groups are implemented (component name is cleared in this example):

在实现了始终在线可用性组的实例上运行存储过程时，存在第六类（在此示例中清除了组件名称）：