sql server死锁_如何使用扩展事件和SQL Server代理自动执行SQL Server死锁收集过程

sql server死锁

介绍 (Introduction)

This article is the last one of a series in which we discussed how to collect data about deadlocks so that we can not only monitor them but also build reports based on our collection results.

本文是该系列文章的最后一篇，其中我们讨论了如何收集有关死锁的数据，这样我们不仅可以监视死锁，还可以根据收集的结果生成报告。

If you came directly to this article, you will find below a list of previous articles with a little word about each of them. We recommend you to read them all before going any further.

如果您直接阅读本文，您将在下面的以前的文章列表中找到有关它们的简短信息。 我们建议您先阅读所有内容，然后再进行操作。

1. What are SQL Server deadlocks and how to monitor them 什么是SQL Server死锁以及如何监视死锁
   In this article, we’ve described what a deadlock is and what are the differences between deadlocks and blocking. We’ve also seen that there are multiple ways to monitor them (SQL Server Error Log, SQL Server Profiler and, starting SQL Server 2008,
   在本文中，我们描述了什么是死锁以及死锁和阻塞之间的区别。 我们还看到有多种监视它们的方法（SQL Server错误日志，SQL Server Profiler以及启动SQL Server 2008的system_health or homemade Extended Events). system_health或自制的扩展事件）。
2. How to report on SQL Server deadlock occurrences 如何报告SQL Server死锁事件
   In the next article, we talked about data collection procedures that can be used to store information about deadlocks into a table from either Error Log and Extended Events. This information takes the form of an XML description of the deadlock events.
   在下一篇文章中，我们讨论了可用于将有关死锁的信息从错误日志和扩展事件存储到表中的数据收集过程。 此信息采用死锁事件的XML描述的形式。

   We’ve also seen that we could generate a nice timeline of their occurrences over time for a given time period.

   我们还看到，我们可以为给定时间段内的事件随时间生成一个不错的时间表。

3. How to use SQL Server Extended Events to parse a Deadlock XML and generate statistical reports 如何使用SQL Server扩展事件来解析死锁XML并生成统计报告
   In the previous article, we defined and implemented (as dynamically as possible) a process for deadlock handling based on Extended Events consisting into three steps that were:
   在上一篇文章中，我们基于扩展事件定义并实现了（尽可能动态地）死锁处理过程，该过程包括三个步骤：
   

   The „Extract” step is implemented in a stored procedure called Monitoring.CollectDeadlockInformation while the “Transform/Shred” step is implemented in the Reporting.ShredDeadlockHistoryTbl stored procedure. You can download the code of this procedure (and for related objects) from the following link, or at the end of third article of this series.

   “提取”步骤在名为Monitoring.CollectDeadlockInformation的存储过程中实现，而“转换/撕碎”步骤在Reporting.ShredDeadlockHistoryTbl存储过程中实现。 您可以从以下链接或本系列第三篇文章的结尾下载此过程的代码（以及相关对象的代码）。

Now, it’s time to automate the first two steps of the above process. This means that we need to make choices on different aspects:

现在，是时候自动化上述过程的前两个步骤了。 这意味着我们需要在不同方面做出选择：

• system_health or homemade? And in what configuration? system_health还是自制的？ 并以什么配置？
• Which tables should be used as input or output of which part of the process?
  哪些表应该用作过程的哪个部分的输入或输出？
• How will we automate things? Will we use a SQL Agent Job or a Windows Scheduled Task?
  我们将如何使事情自动化？ 我们将使用SQL Agent Job还是Windows Scheduled Task？

These questions will find answers in following sections.

这些问题将在以下各节中找到答案。

选择我们初始数据收集的来源 (Choosing source of our initial data collection)

Here, we have two choices. We can either use a built-in Extended Events called system_health or create a specific one. If we choose system_health Extended Events though, there are some additional tasks that may be necessary to get something equivalent to a homemade Extended Event.

在这里，我们有两个选择。 我们可以使用称为system_health的内置扩展事件，也可以创建特定事件。 但是，如果我们选择system_health扩展事件，则可能需要执行一些其他任务才能获得等同于自制扩展事件的内容。

In order to properly choose our source, let’s first review what has to be done for each option.

为了正确选择我们的来源，让我们首先回顾每个选项必须做的事情。

创建专门用于死锁监视的扩展事件 (Creating an Extended Event dedicated to deadlock monitoring)

You will find attached to this article a file called:

您会在本文附件中找到一个文件：

Runnable.SetupMonitoringExtendedEvent.sql

Runnable.SetupMonitoringExtendedEvent.sql

This file contains the code to create such a specialized Extended Event Session. It’s a modified version of the one provided in first article and is almost executable directly: all we have to do is to edit follo