五餅二魚工作室

最近因為任務的需要，幾乎都在使用 Azure SQL Database 了，除非幫同事處理事情，要不然大部分都轉到雲端來處理了。

而最近在一些時候，都會有部分人員反映系統有問題，後來我們在 Web API 當發生異常的時候，將錯誤訊息傳到 Slack 上面，發現當有問題發生的時候，都是因為 Deadlock 造成被犧牲，才導致後端會有異常。但雖然知道原因了，要怎麼來查找 Azure SQL Database 是甚麼樣的指令發生 Deadlock 的呢 ? 這個問題看似簡單，但實際處理的時候會有點小麻煩，因為在雲端的 Azure SQL Database 並沒有辦法讓我們去使用 SQL Trace or SQL Profiler，而就算使用 Extended Event ，我們也沒有辦法去指定將資訊寫到那些目錄上去，因此花了一點時間來爬文找資料。

一般來說應該大部分找到的文章，都會關連到這一篇「Lesson Learned #19: How to obtain the deadlocks of your Azure SQL Database?」，因為在這一篇文章當中，會提到一個沒有特別公開的系統函數 sys.fn_xe_telemetry_blob_target_read_file，這個函數主要是讓我們取得 Extended Event 的資料，但該怎麼來用呢 ?

從相關文章中，可以知道  sys.fn_xe_telemetry_blob_target_read_file 會需要傳入四個參數，而就算我們用 SSMS 查看該函數的參數，也不是那麼清楚那四個函數代表的意義是甚麼 ( 謎之音：都說是 Undocument 的函數了，那怎麼會讓你知道怎麼去使用 )。

因此透過 Google & Bing 去查找到那個函數怎麼使用，第一個參數大部分都是傳 el 或 dl ，而二三四的參數，幾乎也都是傳入 null ，因此看起來就剩下第一個參數做甚麼用了，因此我就用一個 Azure SQL Database 來測試看看，這兩個到底有甚麼不同。當我們實際將指令放在 SSMS 上面來做時間，並且測試出來之後，發現當使用 dl 參數值的時候，那就只會單純回傳 Deadlock 的發生時候的紀錄；而使用 el 的參數時，除了原本 Deadlock 的紀錄萬外，還多增加回傳比較多的資訊。以下我們就跑個範例：

在這個範例中，我使用 dl 的參數，重下面的結果中可以看到，我們可以取出相關 deadlock 的資訊，只是這些資訊是存放在 event_data 的欄位，採用 XML 的方式來儲存

而另外從下面這個範例中可以看出，當我們使用 el 參數和 dl 參數的差異，主要是用來取不同的檔案，而 el 這些開頭的檔案，就不僅只有 deadlock 的資訊，還有相關的登入資訊也都存在裡面了。

---

當我們知道可以有這些資訊之後，該怎麼來分析問題呢 ? 直接看 XML 檔案，那密密麻麻的一堆，可能看起來會有點吃力，此時您可以借用一個方式，將 deadlock 的 event_data 中的資料先 select 出來，就如同前面我們一開始使用 sys.fn_xe_telemetry_blob_target_read_file 的範例一樣，將所要分析的 event_data 的資料貼到記事本上面

 

接著我們將檔案給儲存，副檔名記得改成是 xml ，這樣我們就可以把這個檔案拖拉到 chrome 或者是選擇用 Edge 來開啟，這樣就可以有比較好的方式來顯示這些 XML 的資料了。

而如果眼尖的朋友，從上面的 XML 中可以發現，中間會有一個部分是主要 DeadLock 的資訊，雖然用 XML 是比較好閱讀一點，但還是不如用圖形會比較有感，因此我們可以稍微再做一點加工，針對我們的 event_data 的欄位做個處理

WITH CTE AS (
       SELECT CAST(event_data AS XML)  AS [target_data_XML]
       FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)
)
SELECT 
	target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') AS Timestamp,
	target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
	target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(100)') AS db_name
FROM CTE

此時可以看到我們將 deadlock 的資訊給取出轉成為 XML 的格式，SSMS 也很貼心的幫我們轉成連結，可以讓我們方便來看這些 XML，因此我們就選擇我們所要查看的 XML 點擊下去，然後將開起來的 XML 給儲存成為副檔名是 XDL 的檔案。然後用 SSMS 去開啟那個檔案，就可以用圖形的方式來作呈現了

---

而除了上述的方式，在 Azure SQL Database 的開發團隊，也有另外提供一些不一樣的改變，利用跟 XML 內的資料做 CROSS APPLY ，去找出那些語法較常發生 deadlock ( 文章來源 : Deadlock analysis for SQL Azure Database )

WITH CTE AS (
    SELECT CAST(event_data AS XML) AS [target_data_XML] FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)
)
SELECT [db_name], [query_text], [wait_resource], COUNT(*) as [number_of_deadlocks] FROM (
    SELECT LTRIM(RTRIM(Replace(Replace(c.value('.', 'varchar(max)'),CHAR(10),' '),CHAR(13),' '))) as query_text,
    D.value('@waitresource', 'nvarchar(250)') AS [wait_resource],
    target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(250)') AS [db_name]
    from CTE 
    CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS T(C)
    CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock/process-list/process)') AS Q(D)
) deadlock
GROUP BY [query_text], [wait_resource], [db_name]
ORDER BY [number_of_deadlocks] DESC

透過這樣的方式，也是一種很便利的處理模式。