[SQL] 利用 SQL Profile 來偵測死結

利用 SQL Profile 擷取和分析死結發生的原因。

首先要在 SQL Profile 內建立追蹤來擷取相關事件和資料行。在追蹤的[事件選取範圍]索引標籤內，預設是不會出現 Lock 相關事件，必須先勾選"顯示所有事件"和"顯示所有資料行"，全部選項才會完整出現。

勾選 Locks 事件並選擇需要的欄位資訊

1. DeadLock graph：提供死結的 XML 描述。（SQL 2005 開始支援）
2. Lock：DeadLock：追蹤交易何時要求鎖定已由其他交易鎖定的資源，而造成死結。

DeadLock graph 和 Lock：DeadLock 的 LoginName 在顯示的資料上的差異

• DeadLock graph： sa
• Lock：DeadLock：連線使用者

在 [事件選取範圍] 索引標籤上選取 Deadlock graph 事件，[事件擷取設定] 索引標籤才會出現，[事件擷取設定] 索引標籤內的死結 XML 設定，可以單獨把 DeadLock graph 產生的死結圖形儲存成 xdl 檔案。

• 單一檔案中的所有死結 XML 批次：將所有死結圖表事件儲存在單一 XML 檔案中
• 相異檔案中的每一個死結 XML 批次：為每一個死結圖表建立新的 XML 檔案。

設定好追蹤後，利用 [SQL] 模擬死結產生 這篇的作法來產生死結，就可以在 SQL Profile 上抓到死結相關資料。

• 這張是 DeadLock graph 事件抓到的死結圖形。

• 死結圖形包含處理序節點、資源節點，以及代表處理序與資源之間關聯性的邊緣。

1. 處理序節點（橢圓型圖示）：執行與死節相關的操作（INSERT、UPDATE 和 DELETE），點選橢圓形圖式會顯示 T-SQL 語法（上圖右下角 T-SQL 語法），被選為死結犧牲品的執行緒，其橢圓型圖式上會有一個大Ｘ（上圖左側）
2. 資源節點（矩型圖示）：資料庫物件（Table、Index、Page 等）資訊
3. 邊緣（線條圖示）：表示鎖定類型

透過死結圖形提供的資訊，能發現效能問題進而改善，降低死結發生的機率。

MSDN 參考資料
• 使用 SQL Server Profiler 分析死結
• 儲存死結圖形
• Locks 事件類別目錄
• 將死結數量降至最低

其他參考資料
• How to Track Down Deadlocks Using SQL Server 2005 Profiler - 減少死結發生的說明較 MSDN 具體
• Using SQL Profiler to Resolve Deadlocks in SQL Server
• [SQL] 用SQL Server Profiler 找出Deadlock原因 - 有張實務的 DeadLock 圖形可以看
• How to receive Deadlock information automatically via email
• 好漂亮的 Dead Lock 碎形圖