SQL Server中如何監控死結（Deadlock）

標籤：擴充事件   xevent   extented events   

SQL Server 中如何監控死結（Deadlock）

 

什麼是死結？

所謂死結： 是指兩個或兩個以上的進程在執行過程中，由於競爭資源或者由於彼此通訊而造成的一種阻塞的現象，若無外力作用，它們都將無法推進下去。此時稱系統處於死結狀態或系統產生了死結，這些永遠在互相等待的進程稱為死結進程。

 

由於資源佔用是互斥的，當某個進程提出申請資源後，使得有關進程在無外力協助下，永遠分配不到必需的資源而無法繼續運行，這就產生了一種特殊現象：死結。

 

在SQL Server中為了阻止死結大量充斥在系統中，我們有一個死結監控的後端線程來協助解決死結。

 

死結監控線程

 

如果我們查看sys.dm_os_waiting_tasks，我們可以發現一個系統任務一直處於等待狀態：REQUEST_FOR_DEADLOCK_SEARCH。該線程每五秒鐘被喚醒，來查看是否有死結。如果發現死結，它將結束一個會話。它會殺掉兩個會話中的一個，讓另一個會話擁有需要的所有資源。

 

SQL Server會判斷，要確保殺掉的是最容易復原的會話。因為如果SQL Server殺掉一個事務，它所做的任何工作必須復原到資料庫的同步狀態。它由LOG USED的值來決定。

 

650) this.width=650;" title="clip_image001" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image001" src="http://s3.51cto.com/wyfs02/M00/58/45/wKiom1StBaHADYLOAADKYvo0Qkk960.jpg" border="0" height="270" />

 

我們可以看到上例圖殺掉了會話75而不是192，因為會話75使用了648位元組日誌而會話192使用了944位元組。

 

後端線程每五分鐘喚醒檢查死結。如果發現，它遵照上例的流程去決定如何解決。然而，當它第一次喚醒，立馬喚醒第二次，確保不是一個嵌套死結。如果有，會被殺掉，然後返回睡眠狀態。下一次喚醒在4.90秒之後（預估喚醒時間花費10毫秒）。每次遞減100毫秒，將每秒喚醒10次處理死結。

 

如何監控死結？

 

方法一：

Windows效能監控器（Performance Monitor）

Object: SQLServer:Locks

Counter: Number of Deadlocks/sec

Instance: _Total

 

下面的查詢提供了自從上次重啟以來在本伺服器上發生的所有死結：

 

SELECT cntr_value AS NumOfDeadLocksFROM sys.dm_os_performance_countersWHERE object_name = ‘SQLServer:Locks‘AND counter_name = ‘Number of Deadlocks/sec‘AND instance_name = ‘_Total‘

 

方法二：

跟蹤標識（Trace Flags）1204和1222

 

Trace Flag 1204至少從SQL Server 2000開始存在。Trace Flag 1222從SQL Server 2005被包含進來。兩者的死結資訊被記錄到SQL Server錯誤記錄檔（ERRORLOG）。

 

方法三：

SQL Server Profiler和服務端的SQL Trace

 

Trace Event Class: Locks Event Name: Deadlock Graph

像上面樣本一樣給出一個XML圖示。非常容易閱讀並找出當前進行中什麼動作。

 

方法四：

擴充事件（Extended Events）

 

自從SQL Server 2008開始的監控新方式。擴充事件最終會取代SQL Server Profiler（注意：SQL Server Profiler在被放棄屬性列表中）。和SQL Server Profiler一樣它提供了相同的XML圖示，並且在效能影響上更輕量級。

 

方法五：

System Health

 

一個新的預設跟蹤，但它不像SQL Server預設跟蹤（Default Trace）那樣有有限數量的跟蹤資訊且不能修改。我們可以修改system health的定義，它內建於擴充事件中。不像預設跟蹤，system health可以跟蹤到剛才已經發生過的死結資訊。我們可以從system health擷取這些資訊用來分析而不用部署我們自己的擴充事件監控。

本文出自 “SQL Server Deep Dives” 部落格，請務必保留此出處http://ultrasql.blog.51cto.com/9591438/1600374

SQL Server中如何監控死結（Deadlock）