sql活动监视器 死锁_使用system_health扩展事件监视SQL Server死锁

sql活动监视器 死锁

Performance monitoring is a must to do the task for a DBA. You should ensure that the database performance is optimal all the time without any impact on the databases. Performance issues act like an open stage, and you need to look at every aspect such as CPU, RAM, server performance, database performance, indexes, blocking, waits, and SQL Server deadlocks. You might face frequent deadlocks issues, and they have a direct impact on the application performance.

性能监视是执行DBA任务所必需的。 您应确保数据库性能始终保持最佳状态，而不会影响数据库。 性能问题就像一个开放阶段，您需要查看各个方面，例如CPU，RAM，服务器性能，数据库性能，索引，阻塞，等待和SQL Server死锁 。 您可能会经常遇到死锁问题，它们直接影响应用程序性能。

You can also go through these articles: SQL Server deadlock definition and Overview and What are SQL Server deadlocks and how to monitor them, to understand deadlock in SQL Server.

您也可以阅读以下文章： SQL Server死锁的定义和概述以及什么是SQL Server死锁以及如何监视死锁，以了解SQL Server中的死锁。

Let’s have a quick overview of “Deadlocks in SQL Server”. Later, we will cover how we can monitor deadlocks using the system_health extended event.

让我们快速了解一下“ SQL Server中的死锁”。 稍后，我们将介绍如何使用system_health扩展事件监视死锁。

SQL Server死锁简介 (Introduction to SQL Server Deadlocks)

SQL Server obtains locks on required resources to perform specific tasks such as Select, Insert, Update, Delete. It is normal behavior and helps to ensure the ACID properties (Atomicity- Consistency – Isolation – Durability).

SQL Server在执行特定任务（例如选择，插入，更新，删除）所需的资源上获得锁。 这是正常行为，有助于确保ACID属性（原子性-一致性-隔离性-耐久性）。

If two processes in SQL Server wants to have exclusive access to a resource held by the other process, it is called a deadlock situation.

如果SQL Server中的两个进程希望对另一个进程拥有的资源具有独占访问权，则称为死锁情况。

In the following image, we can see that thread A is waiting for the resources held by thread B, and similarly, thread B is waiting for the resources held by thread A. Both the threads cannot process due to insufficient resources.

在下图中，我们可以看到线程A正在等待线程B所拥有的资源，并且类似地，线程B正在等待线程A所拥有的资源。这两个线程由于资源不足而无法处理。

We can further understand the SQL Server deadlock scenario using the following image.

我们可以使用下图进一步了解SQL Server死锁方案。

• User A: Intent Exclusive (IX) lock on the Invoice table and page it requires to modify
  用户A：Invoice表和页面上需要修改的Intent Exclusive（IX）锁
• User A also requires an Exclusive lock (X) on the row of the Invoice table
  用户A在发票表的行上还需要排他锁（X）
• User B wants to read the same data user A wants to update. It tries to place an Intent Shared lock on the Invoice table and shared lock on the page to read the data. It has to wait until the User A releases the Exclusive lock on the page
  用户B要读取用户A要更新的相同数据。 它尝试将Intent Shared锁放在Invoice表上，并将共享锁放在页面上以读取数据。 它必须等到用户A释放页面上的互斥锁
• User A also requires to read data from the Invoice Details page to complete the transaction, but that page has an Exclusive lock by the user B. This thread also needs to wait for the other transactions to complete
  用户A还需要从“发票明细”页面中读取数据以完成交易，但用户B对该页面具有排他锁。此线程还需要等待其他交易完成

SQL Server automatically monitors the dead