sql活动监视器 死锁_监视SQL Server死锁–简单方法

sql活动监视器 死锁

SQL Server is a very powerful tool and wherever I go, I see the tool being way much underutilized. Some people even don’t know about the features which are already in the SQL Server and they have not used it, like SQL Server Extended Events. Though, Extended Events is a way more complex and detailed topic which I cannot cover in just one article I will discuss one of its very good utilities out of the countless.

SQL Server是一个非常强大的工具，无论我走到哪里，我都看到该工具未得到充分利用。 有些人甚至不了解SQL Server中已经存在的功能，并且尚未使用它们，例如SQL Server扩展事件。 虽然，扩展事件是一种更复杂，更详细的主题，但我无法在一篇文章中涉及到，我将讨论无数个其很好的实用程序。

Here, I would like to give you about a small, simple yet a powerful way of using SQL Server Extended Events and monitoring SQL Server Deadlocks.

在这里，我想向您介绍一种使用SQL Server扩展事件和监视SQL Server死锁的小型，简单而有效的方法。

As we all are aware of deadlocks and ideally speaking we would never want to hear about it in our production environments but deadlocks do happen and we do want to get in the Root Cause Analyses of the deadlocks.

因为我们都知道死锁，所以理想情况下，我们永远都不想在生产环境中听到死锁，但是死锁的确会发生，我们确实希望获得死锁的根本原因分析。

Before going into the details of my simple script which I will discuss in detail very shortly, I want to let you know about the any prerequisite for the script to run, there are NONE. Yes! If you are using Microsoft SQL Server 2008 or later then you don’t have to do anything else just understand what I will explain here and use the script. That’s all, you have the solution right then and there. It was already inside the SQL Server just out of the box (not the query but the product features I am using).

在讨论我的简单脚本的细节之前，我将很快进行详细讨论，我想让您知道脚本运行的任何先决条件，没有。 是! 如果您使用的是Microsoft SQL Server 2008或更高版本，则无需执行其他任何操作，只需了解我将在此处解释的内容并使用脚本即可。 仅此而已，您就可以在那里找到解决方案。 它已经开箱即用在SQL Server内部（不是查询，而是我正在使用的产品功能）。

So let’s start and discuss extended events features which I will be using. Extended events capture a lot of data from the system and you should explore that as well. There have a lot of useful detailed information which is already being captured. Please have a look at this event on MSDN here.

因此，让我们开始讨论我将要使用的扩展事件功能。 扩展事件从系统中捕获了大量数据，您也应该对其进行探索。 有许多有用的详细信息已被捕获。 请在此处查看MSDN上的此事件。

As of now, we are only concerned about the deadlocks. The deadlocks are also captured in the system trace extended event with the object name (a column in the system trace) as “xml_deadlock_report”. So, we will be querying a system view “sys.fn_xe_file_target_read_file” to query the extended event trace “system_health” which is already running by default so you don’t have to setup anything. You can get detailed information about the system view “sys.fn_xe_file_target_read_file” from MSDN here.

到目前为止，我们只关心僵局。 死锁还会在系统跟踪扩展事件中捕获，对象名称（系统跟踪中的列）为“ xml_deadlock_report” 。 因此，我们将查询系统视图“ sys.fn_xe_file_target_read_file”以查询默认情况下已运行的扩展事件跟踪“ system_health” ，因此您无需进行任何设置。 您可以在此处从MSDN获得有关系统视图“ sys.fn_xe_file_target_read_file”的详细信息。

This query will give you only the deadlocks captured.The script is pretty simple and also has comments so I won’t go into each and every detail here and will not describe everything but some important points should be mentioned here.

该查询只会给您捕获死锁。该脚本非常简单，并且具有注释，因此在这里我不会详细介绍每个细节，也不会描述所有内容，但在这里要提一些要点。

First we mentioned earlier we get the deadlock graph which is by default in an XML Format. After that, we need to parse the XML so that we can read it easily. There is a Microsoft SQL Server Stored Procedure for that and it’s pretty handy, i.e. “sp_xml_preparedocument”. You can get detailed information about the system stored procedure on MSDN here.

首先，我们在前面提到了死锁图，该图默认为XML格式。 之后，我们需要解析XML，以便我们可以轻松读取它。 为此，有一个Microsoft SQL Server存储过程，它非常方便，即“ sp_xml_preparedocument” 。 您可以在此处获取有关MSDN上系统存储过程的详细信息。

We will parse the document using the system stored procedure and will get the following columns from the deadlock graph XML. The columns which we be getting will be under the following node so we start reading XML from that particular node “event/data/value/deadlock/process-list/process”.

我们将使用系统存储过程来解析文档，并将从死锁图XML中获取以下列。 我们将获得的列将在以下节点下，因此我们开始从该特定节点“ event / data / value / deadlock / process-list / process”中读取XML。

Columns read from the XML Deadlock:

从XML死锁中读取的列：

1. Id
   The Process ID which is required to uniquely identify each process.

   ID
   唯一标识每个流程所需的流程ID。

2. Hostname
   Hostname is the machine name of the application which is requesting the query.

   主机名
   主机名是请求查询的应用程序的计算机名。

3. Loginname
   Login name is the SQL/Windows authentication login which is authenticated to initiate the session.

   登录名
   登录名是经过身份验证以启动会话SQL / Windows身份验证登录名。

4. Clientapp
   Client Application like dot net framework application or SQL Server Management studio etc.

   Clientapp
   客户端应用程序，例如点网框架应用程序或SQL Server Management Studio等。

5. Inputbuf & executionStack/frame
   Above two columns get the actual TSQL executed by the sessions. This is the most important part as it will have all the actual details in it.

   Inputbuf和执行堆栈/框架
   以上两列获取会话实际执行的TSQL。 这是最重要的部分，因为它将包含所有实际细节。

6. Lastbatchcompleted
   The time of the deadlock is captured in this column. This is also important for logging and analysis purpose.

   最后一批完成
   死锁时间在此列中捕获。 这对于日志记录和分析目的也很重要。

There is one variable “@GetDeadLocksForLastMinutes”, which you can configure as per your requirements, which is to set as a threshold of time (in minutes) for the deadlock script to watch for events back in history. The script then captures all the information from the XML and puts it into a Temporary Table and loops through all the Deadlocks which might have occurred in the timeframe mentioned. At the end it will just display all the information captured in it using simple columns. All the result set columns are discussed in this article later.

您可以根据需要配置一个变量“ @GetDeadLocksForLastMinutes” ，该变量将设置为死锁脚本监视历史记录中事件的时间阈值（以分钟为单位）。 然后，脚本从XML捕获所有信息，并将其放入临时表中，并遍历在上述时间范围内可能发生的所有死锁。 最后，它将仅使用简单的列显示其中捕获的所有信息。 稍后将在本文中讨论所有结果集列。

In my client’s production environment, I have added an alert for the deadlocks captured. I run this script every hour to get an alert with all the detailed information. This can be configured according to your needs and client’s requirements.

在客户的生产环境中，我为捕获的死锁添加了警报。 我每小时运行一次此脚本，以获取有关所有详细信息的警报。 可以根据您的需要和客户的要求进行配置。

Here is my script to get all the deadlocks data!

这是我的脚本，用于获取所有死锁数据！

 
/*
Declaration of the variables 
 
#DeadLockXMLData to store each Dead lock XML from the extended Event
#DeadLockDetails to store deadlock process, victim and application information
@GetDeadLocksForLastMinutes For how many number of Minutes to watch for
 
*/
SET NOCOUNT ON;
CREATE TABLE #DeadLockXMLData(DeadLockXMLData XML,DeadLockNumber INT)
CREATE TABLE #DeadLockDetails(ProcessID nVARCHAR(50),HostName nVARCHAR(50),LoginName nVARCHAR(100)
,ClientApp nVARCHAR(100), Frame nVARCHAR(MAX),TSQLString nVARCHAR(MAX),DeadLockDateTime DATETIME,IsVictim TINYINT,DeadLockNumber INT)
DECLARE @DeadLockXMLData AS XML,@DeadLockNumber INT,@getInputBuffer CURSOR,@Document AS INT, @SQLString NVARCHAR (MAX),@GetDeadLocksForLastMinutes INT;
 
SET	   @GetDeadLocksForLastMinutes = 5;
 
/*INSERT THE DEADLOCKS FROM EXTENDED EVENTS TO TEMP TABLES & FILTER ONLY DEADLOCKS*/
INSERT INTO #DeadLockXMLData(DeadLockXMLData,DeadLockNumber)
SELECT  CONVERT(XML, event_data) DeadLockXMLData,ROW_NUMBER() OVER (ORDER BY Object_name) DeadLockNumber
FROM	sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
WHERE   OBJECT_NAME = 'xml_deadlock_report'
 
/*START A CURSOR TO LOOP THROUGH ALL THE DEADLOCKS AS YOU MIGHT GET MUTLTIPLE DEADLOCK IN PRODUCTION AND YOU WOULD WANT ALL OF THEM*/
SET	   @getInputBuffer = CURSOR FOR
SELECT  DeadLockXMLData,DeadLockNumber  FROM	#DeadLockXMLData
OPEN	   @getInputBuffer
 
FETCH NEXT
FROM	   @getInputBuffer INTO @DeadLockXMLData,@DeadLockNumber
 
WHILE	@@FETCH_STATUS = 0
 
BEGIN
SET	   @Document	=   0
SET	   @SQLString	=   ''
 
EXEC	   sp_xml_preparedocument @Document OUTPUT, @DeadLockXMLData
 
/*INSERT PARSED DOCUMENT'S DATA FROM XML TO TEMP TABLE FOR READABILITY*/
INSERT INTO #DeadLockDetails(ProcessID,HostName,LoginName,ClientApp,Frame,TSQLString,DeadLockDateTime,DeadLockNumber)
SELECT  ProcessID, HostName,LoginName,ClientApp, Frame,TSQL AS  TSQLString,LastBatchCompleted,@DeadLockNumber
FROM	   OPENXML(@Document, 'event/data/value/deadlock/process-list/process')
WITH 
(
ProcessID [varchar](50) '@id',
HostName [varchar](50) '@hostname',
LoginName [varchar](50) '@loginname',
ClientApp [varchar](50) '@clientapp',
CustomerName [varchar](100) '@clientapp',
TSQL [nvarchar](4000) 'inputbuf',
Frame nVARCHAR(4000) 'executionStack/frame',
LastBatchCompleted nVARCHAR(50) '@lastbatchcompleted'
)
 
/*UPDATE THE VICTIM SPID TO HIGHLIGHT TWO QUERIES SEPARETELY, THE PROCESS (WHO CREATED THE DEADLOCK) AND THE VICTIM*/
 
UPDATE  #DeadLockDetails
SET	   IsVictim = 1
WHERE   ProcessID IN (
SELECT  ProcessID 
FROM	   OPENXML(@Document, 'event/data/value/deadlock/victim-list/victimProcess')
WITH 
(
ProcessID [varchar](50) '@id',
HostName [varchar](50) '@hostname',
LoginName [varchar](50) '@loginname',
ClientApp [varchar](50) '@clientapp',
CustomerName [varchar](100) '@clientapp',
TSQL [nvarchar](4000) 'inputbuf',
Frame nVARCHAR(4000) 'executionStack/frame',
LastBatchCompleted nVARCHAR(50) '@lastbatchcompleted'
)
)
 
EXEC sp_xml_removedocument @Document
 
FETCH NEXT
FROM	   @getInputBuffer INTO @DeadLockXMLData,@DeadLockNumber
 
END
 
CLOSE   @getInputBuffer
DEALLOCATE @getInputBuffer
 
 
/*GET ALL THE DEADLOCKS AS A RESULT IN EASY READABLE TABLE FORMAT AND ANALYZE IT FOR FURTHER OPTIMIZATION */
 
SELECT  DeadLockDateTime,HostName,LoginName,ClientApp,ISNULL(Frame,'')+' **'+ISNULL(TSQLString,'')+'**' VictimTSQL
	   ,(SELECT ISNULL(Frame,'')+' **'+ISNULL(TSQLString,'')+'**' AS TSQLString FROM #DeadLockDetails WHERE DeadLockNumber = D.DeadLockNumber AND ISNULL(IsVictim,0) = 0) ProcessTSQL
FROM	#DeadLockDetails D
WHERE   DATEDIFF(MINUTE,DeadLockDateTime,GETDATE()) <= @GetDeadLocksForLastMinutes
		AND IsVictim = 1
ORDER BY DeadLockNumber
 
DROP TABLE #DeadLockXMLData,#DeadLockDetails
 

For a demo I will create a deadlock and SQL Server Extended Events will capture it. We will then run the script to get that in a simple readable format for further analysis.

对于演示，我将创建一个死锁，SQL Server扩展事件将捕获死锁。 然后，我们将运行脚本以简单易读的格式获取脚本，以进行进一步的分析。

So, let’s start to create a deadlock (if you are lucky like me you will not be having deadlocks in your production so you have to create one).

因此，让我们开始创建一个死锁（如果像我这样幸运的话，您的产品中就不会有死锁，因此您必须创建一个死锁）。

First, create two tables and start an UPDATE on the first table as shown below in the figure:

首先，创建两个表，并在第一个表上启动UPDATE，如下图所示：

After the first transaction has been completed (keep in mind it’s NOT committed yet). You need to start another transaction and update both the tables so that the second transaction should be waiting for the first, as shown in the figure below:

在完成第一笔交易后（请记住，尚未提交）。 您需要启动另一个事务并更新两个表，以便第二个事务应等待第一个事务，如下图所示：

So, now the last step to create a deadlock is update the second table again in the first session so that both the sessions should try to grab each other’s resources (which are already waiting for other) and thus creating a deadlock.

因此，现在创建死锁的最后一步是在第一个会话中再次更新第二张表，以便两个会话都应尝试获取彼此的资源（已经在等待对方的资源），从而创建死锁。

So, there you go. Congratulations you have a deadlock!

所以，你去了。 恭喜您陷入僵局！

Now, grab my script and just run it. The Extended Event might take couple of minutes to write the current deadlock to the file so be patient it will give you the results. One you run the query it will give you the deadlocks occurred in last 5 minutes.

现在，获取我的脚本并运行它。 扩展事件可能需要几分钟才能将当前死锁写入文件，因此请耐心等待，它将为您提供结果。 一个运行查询的查询将使您了解最近5分钟内发生的死锁。

So, now you will have a result set and this is the detailed information about the deadlock captured. I have captured the most important yet basic columns from the deadlock graphs but this is not end of the world. You can get more and more details from the deadlock graph and customize this script as well.

因此，现在您将获得一个结果集，这是有关捕获的死锁的详细信息。 我已经从死锁图中捕获了最重要但最基本的列，但这还不是世界末日。 您可以从死锁图中获得越来越多的详细信息，并且也可以自定义此脚本。

The columns of the table are mentioned below for your reference and explanation:

下表列出了各列，以供您参考和解释：

1. DeadLockDateTime
   This is the time when the actual deadlock happened. The trace might take couple of minutes to populate the deadlock but the time will be accurate.

   死锁日期时间
   这是实际死锁发生的时间。 跟踪可能需要花费几分钟来填充死锁，但是时间将是准确的。

2. HostName
   The machine name which is accessing the data. This is the application server or the client application machine name. This is important to identify the system which is trying to access the data.

   主机名
   正在访问数据的机器名称。 这是应用程序服务器或客户端应用程序的计算机名称。 这对于识别正在尝试访问数据的系统很重要。

3. LoginName
   The SQL/Windows authenticated login name which is used to access the database. This is critical to identify which login is being used to access the data.

   登录名
   经过SQL / Windows身份验证的登录名，用于访问数据库。 这对于确定使用哪个登录名访问数据至关重要。

4. ClientApp
   The client application name will be provided in this column. It will be like SQL Server Management Studio or the dot net framework application or whichever application you are using to get the data to the user.

   ClientApp
   客户端应用程序名称将在此列中提供。 它将类似于SQL Server Management Studio或.net框架应用程序，或用于将数据获取给用户的任何应用程序。

5. VictimTSQL
   This is the TSQL for the session which was chosen as the deadlock victim and didn’t complete its processing.

   受害者TSQL
   这是会话的TSQL，它被选择为死锁受害者，但未完成其处理。

6. ProcessTSQL
   This is the TSQL for the process which was executed successfully but caused the deadlock to happen.

   ProcessTSQL
   这是成功执行但导致死锁发生的进程的TSQL。

In the table above the most important columns are the Victim and Process TSQL along with the Application name. After executing and getting all the information about the deadlock, now you can do further analysis and fix the problem. I cannot provide a solution for deadlocking as its way more complicated than just identifying the TSQL involved in the deadlock. But this is a good start to remove the deadlocks from the systems.

在上表中，最重要的列是“受害者”和“进程TSQL”以及“应用程序”名称。 执行并获取有关死锁的所有信息之后，现在您可以进行进一步的分析并解决问题。 我无法提供死锁解决方案，因为死锁的方法比仅识别死锁中涉及的TSQL更复杂。 但这是一个消除系统死锁的好开始。

翻译自: https://www.sqlshack.com/monitoring-sql-server-deadlocks-easy-way/

sql活动监视器 死锁