343
死鎖監控四步走，從此性能不再愁！
作者介紹
許昌永，高級DBA，微軟SQL Server MVP，十年以上SQL Server使用經驗。曾就職於騰訊公司，從事了六年遊戲行業SQL Server數據庫開發和管理。目前就職於跨境電商DX.COM三年多，負責公司SQL Server和MongoDB的數據庫架構設計、高可用部署、運維管理和性能優化等工作。翻譯出版了書籍《PowerShellV3——SQL Server 2012數據庫自動化運維權威指南》
在SQL Server的日常管理中，讓SQL Server高效運行，且性能良好，是DBA需要做的事。DBA需要了解數據庫的日常運行情況，對性能進行分析和調優，需要對線上環境部署監控。那我們都需要監控哪些方麵呢？
1. SQL Server服務器的CPU、內存、IO、網絡流量、緩存等資源性能怎麼樣，各個相關服務如SQL Server服務、SQL Server代理服務等是否正常運行，這些一般使用開源的監控軟件Zabbix來設置告警，當然針對數據庫服務器的特性，添加一些SQL Server數據庫引擎的性能計數器進行收集。
2. SQL Server各種日誌會記錄有用的信息。因此可以監控SQL Server錯誤日誌、SQL Server代理日誌等。
3. SQL Server數據庫避免不了一些異常狀態，比如錯誤的腳本導致的異常，空間不夠，磁盤掛了，複製失敗了等。這裏我先提提SQL Server事件。這個意味著SQL Server發生特定錯誤產生的事件，每個事件都有對應的數據庫、嚴重級別、錯誤號、錯誤文本。可以針對一些極其嚴重的錯誤如823、824、825、832、855、856等進行特定錯誤監控，還可以針對嚴重的錯誤級別進行監控，如錯誤等級從19到25。
4. 生產環境都會部署各種高可用技術，無論是鏡像、日誌傳送、複製還是Alwayson，都需要部署相應的監控，注意一個是要監控是否正常運行，再就是性能怎麼樣，設置一定的告警閾值。
上麵的監控基本能滿足基本生產需求，那麼我們還要監控哪些方麵呢？
5. SQL Server的連接超時、執行超時、死鎖。
6. SQL Server活動進程、慢查詢、阻塞。
7. 等待統計對於分析SQL Server引擎性能瓶頸非常關鍵，幫助診斷SQL Server以及特定查詢和批處理的性能問題。
8.環形緩衝區包含了最小的係統輸出，記錄了大量的XML格式信息，用於幫助分析狀態的變化提供更好的思路。可以監控連接、異常、調度、安全、內存等。
9. 審核SQL Server數據庫引擎實例或單獨的數據庫，跟蹤和記錄數據庫引擎中發生的事件。
10. 可以結合Powershell實現自動化監控部署、結合SSRS實現平台化展示。再進一步深入到Web端的部署、運維、監控、性能分析等一體化。
監控是SQL Server數據庫引擎的一大主題，了解整個數據庫引擎的監控架構，並做好全麵的監控，是很必要的。我們就拿最常見的死鎖來談談SQL Server的監控。
SQL Server中如何監控死鎖
1什麼是死鎖？
所謂死鎖： 是指兩個或兩個以上的進程在執行過程中，由於競爭資源或者由於彼此通信而造成的一種阻塞的現象，若無外力作用，它們都將無法推進下去。此時稱係統處於死鎖狀態或係統產生了死鎖，這些永遠在互相等待的進程稱為死鎖進程。
由於資源占用是互斥的，當某個進程提出申請資源後，使得有關進程在無外力協助下，永遠分配不到必需的資源而無法繼續運行，這就產生了一種特殊現象：死鎖。
在SQL Server中為了阻止死鎖大量充斥在係統中，我們有一個死鎖監控的後端線程來幫助解決死鎖。
2死鎖監控線程
如果我們查看sys.dm_os_waiting_tasks，我們可以發現一個係統任務一直處於等待狀態：REQUEST_FOR_DEADLOCK_SEARCH。該線程每五秒鍾被喚醒，來查看是否有死鎖。如果發現死鎖，它將結束一個會話。它會殺掉兩個會話中的一個，讓另一個會話擁有需要的所有資源。
SQL Server會判斷，要確保殺掉的是最容易回滾的會話。因為如果SQL Server殺掉一個事務，它所做的任何工作必須回滾到數據庫的同步狀態。它由LOG USED的值來決定。
我們可以看到上例圖殺掉了會話75而不是192，因為會話75使用了648字節日誌而會話192使用了944字節。
後端線程每五分鍾喚醒檢查死鎖。如果發現，它遵照上例的流程去決定如何解決。然而，當它第一次喚醒，立馬喚醒第二次，確保不是一個嵌套死鎖。如果有，會被殺掉，然後返回睡眠狀態。下一次喚醒在4.90秒之後（預估喚醒時間花費10毫秒）。每次遞減100毫秒，將每秒喚醒10次處理死鎖。
3如何監控死鎖？
方法一：
-
Windows性能監控器（Performance Monitor）
-
Object: SQLServer:Locks
-
Counter: Number of Deadlocks/sec
-
Instance: _Total
下麵的查詢提供了自從上次重啟以來在本服務器上發生的所有死鎖：
SELECT cntr_value AS NumOfDeadLocks
FROM sys.dm_os_performance_counters
WHERE 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 2012圖形界麵來部署一個擴展事件跟蹤會話。然後可以生成SQL腳本，在2008或2008 R2版本下運行類似的跟蹤。
步驟1：
通過“Object Explorer”連接到實例，展開“Management”、“Extended Events”、“Sessions”。
步驟2：
右鍵點擊“Sessions”，創建一個新的會話向導。
步驟3：
輸入會話名稱“Deadlock_Monitor”，點擊下一步。
步驟4：
選擇不使用模板（像SQL Server Profiler模板一樣，預設了一些默認選項一起啟動，但沒有一個滿足我們需求的模板），點擊下一步。
步驟5：
選擇要捕獲的事件，在“Event library”輸入deadlock，可看到如下圖所示：
步驟6：
選擇“xml_deadlock_report”，添加到右側選擇的事件列表中。再單擊下一步。
步驟7：
選擇要捕獲的列，這裏我們選擇下一步。
步驟8：
定義過濾條件，這裏我們忽略這個設置，點擊下一步。
步驟9：
選擇保存數據到文件，設置文件路徑和最大值等。點擊下一步。
步驟10：
檢查所有的配置，點擊完成來安裝和啟用會話。
步驟11：
現在我們可以啟動捕獲，並查看活動數據。
步驟12：
在剛才創建會話“Deadlock_Monitor”上右鍵點擊生成腳本。
步驟13：
在會話“Deadlock_Monitor”上右鍵選擇啟動會話。
步驟14：
分別在兩個查詢窗口執行如下語句。
步驟15：
在“Deadlock_Monitor”上的package0.event_file上右鍵選擇“View Target Data…”。選擇對應timestamp的死鎖條目，在Details的xml_report值裏顯示的就是死鎖的XML文件，可雙擊打開。點擊Deadlock即可看到死鎖的圖形化展示。
深入進階
死鎖詳細信息還有幾個步驟可用來配置擴展事件來監控死鎖。
我想去討論另外兩個事件來捕獲到分析死鎖更詳細的信息。
-
Lock: Deadlock事件類
這個事件類可以用來驗證死鎖犧牲品。這個事件說明什麼時候請求需要一個鎖，但被取消作為一個死鎖犧牲品。
-
Lock: Deadlock chain事件類
這個事件類用於監控死鎖狀態。當有一個死鎖時該事件被觸發。通過在實例級別監控這個事件，我們能夠識別那些對象在死鎖中，我們是否在應用程序中有死鎖導致的性能問題。
步驟1：
在之前的“Deadlock_Monitor”會話上右鍵選擇“Properties”。選擇“Events”頁，將lock_deadlock和lock_deadlock_chain事件類添加到右側已選擇事件列表。
步驟2：
運行之前的死鎖示例。
步驟3：
在“Deadlock_Monitor”上的package0.event_file上右鍵選擇“View Target Data…”。選擇對應timestamp的死鎖條目。
如果有用戶反饋說他們在應用程序的錯誤日誌裏發現了輸出了死鎖信息，而且是在深夜。我們就可以知道怎麼監控和獲取死鎖數據了。
使用system_health
默認跟蹤會話監控死鎖
自SQL Server 2008以後，提供了擴展事件（Extended Events）來跟蹤係統分析定位問題。默認的system_health會話一直在運行，可以幫助你更快的定位問題。
運行如下腳本可以看到system_health擴展事件會話：
SELECT * FROM sys.dm_xe_sessions
即便是你沒有啟動任何擴展事件會話，這個查詢也會返回一行system_health會話。
SQL Server 2012版本之前，並不提供管理擴展事件會話的圖形界麵，你可以從這裏下載SQL Server 2008 Extended Events SSMS Addin插件：https://extendedeventmanager.codeplex.com/
安裝好後，可以按如圖方式找到擴展事件管理界麵：
而在SQL Server 2012版本中，則通過如圖方式可以找到該界麵：
我們右鍵點擊“system_health”，生成腳本，我們可以看到該會話的內容。你也可以在SQL Server的安裝目錄：C:\Program Files\Microsoft SQL Server\MSSQL11.\MSSQL\Install下找到腳本u_tables.sql文件。
從定義可以看到，會話的輸出包含callstack、sessionID、TSQL和TSQL Call Stack且當安全等級大於20或者錯誤號為17803等。它們與內存壓力相關、Non-yielding scheduler問題、死鎖和一些類型的等待。
會話輸出被捕獲到遵從FIFO規則的ring_buffer中，ring_buffer是一個內存使用者，它以二進製格式存儲捕獲數據。當事件會話啟用的時候，數據即可被捕獲。當停止會話的時候，分配給ring_buffer的內存被釋放，且數據消失。注意：對於SQL Server 2012之前，system_health的目標隻有ring_buffer，從SQL Server 2012開始，增加了event_file的輸出。
你可以通過關聯sys.dm_xe_session_targets和sys.dm_xe_sessions視圖來查看ring_buffer或event_file的內容，並轉換二進製數據為XML格式。
SELECT name, target_name, CAST(target_data AS XML) target_data
FROM sys.dm_xe_sessions s
INNER JOIN sys.dm_xe_session_targets t
ON s.address = t.event_session_address
WHERE s.name = 'system_health'
GO
注意：event_file的輸出是文件的存儲路徑，而ring_buffer的輸出是捕獲到的數據。
在ring_buffer中，每一個事件元素都有一個數據子集和一個動作子集。這些動作是在會話的定義中。數據元素包含了每個事件的數據類型列的所有值。這些列可通過sys.dm_xe_object_columns視圖輸出。讓我們解析XML格式以表格格式查看內容。因為每個事件返回數據列的不同集合。下麵給一個error_reported事件的例子。
對於system_health最有幫助的用途之一是跟蹤死鎖。對於目標ringbuffer，存儲多少數據依賴於被監控機器上的該目標的容量，以及產生最大數量的設置相關，這些將在每個會話的定義中。你可以在system_health會話的輸出中找到過去的死鎖記錄。
所有查詢都會在system_health輸出中，可以通過運行下麵的代碼獲得一個死鎖報表。
查看process-list的inputbuf子元素，可以看到導致死鎖的代碼片段，process-list顯示所有死鎖參與者的進程ID。process元素包含spid、數據庫id、登錄名、隔離級別、客戶端應用程序名。Resource-list元素包含在死鎖中的資源。查看owner-list和waiter-list元素可以看到這兩個進程如何互相阻塞。
原文發布時間為：2016-11-23
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-11 12:01:41