SQL Server監控全解析

SQL Server監控全解析

在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實現自動化監控部署、結合×××S實現平臺化展示。再進一步深入到Web端的部署、運維、監控、性能分析等一體化。

監控是SQL Server數據庫引擎的一大主題，了解整個數據庫引擎的監控架構，并做好全面的監控，是很必要的。當然，具體使用什么方法最合適，如何去設計和部署，看完筆者的《SQL Server監控和診斷》一書自有答案，甚至提供有大量實際解決方案的代碼，直接拿來可用。

我們就拿最常見的死鎖來談談SQL Server的監控。

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

什么是死鎖？

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

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

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

死鎖監控線程

如果我們查看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次處理死鎖。

如何監控死鎖？

方法一：

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”上右鍵點擊生成腳本。

CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'D:\MSSQL\DATA\MSSQL11.MSSQLSERVER\MSSQL\Log\Deadlock_Monitor.xel',max_file_size=(256),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

步驟13：

在會話“Deadlock_Monitor”上右鍵選擇啟動會話。

步驟14：

分別在兩個查詢窗口執行如下語句。

--Window1
USE AdventureWorks2012
BEGIN TRAN
UPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 20
WAITFOR DELAY '0:0:10'
SELECT * FROM Person.Address WHERE AddressID = 25
--Window 2
USE AdventureWorks2012
BEGIN TRAN
UPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 25
WAITFOR DELAY '0:0:10'
SELECT * FROM Person.Address WHERE AddressID = 20

步驟15：

在“Deadlock_Monitor”上的package0.event_file上右鍵選擇“View Target Data…”。選擇對應timestamp的死鎖條目，在Details的xml_report值里顯示的就是死鎖的XML文件，可雙擊打開。點擊Deadlock即可看到死鎖的圖形化展示。

深入進階

死鎖詳細信息還有幾個步驟可用來配置擴展事件來監控死鎖。

我想去討論另外兩個事件來捕獲到分析死鎖更詳細的信息。

1. Lock: Deadlock事件類

這個事件類可以用來驗證死鎖犧牲品。這個事件說明什么時候請求需要一個鎖，但被取消作為一個死鎖犧牲品。

2. 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插件：http://extendedeventmanager.codeplex.com/

安裝好后，可以按如圖方式找到擴展事件管理界面：

而在SQL Server 2012版本中，則通過如圖方式可以找到該界面：

我們右鍵點擊“system_health”，生成腳本，我們可以看到該會話的內容。你也可以在SQL Server的安裝目錄：C:\Program Files\Microsoft SQL Server\MSSQL11.<instanceid>\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事件的例子。

DECLARE @x XML =
(SELECT CAST(target_data AS XML)
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' and t.target_name = 'ring_buffer')
SELECT t.e.value('@name', 'varchar(50)') AS EventName
,t.e.value('@timestamp', 'datetime') AS DateAndTime
,t.e.value('(data[@name="error"]/value)[1]', 'int') AS ErrNo
,t.e.value('(data[@name="severity"]/value)[1]', 'int') AS Severity
,t.e.value('(data[@name="message"]/value)[1]', 'varchar(max)') AS ErrMsg
,t.e.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
FROM @x.nodes('//RingBufferTarget/event') AS t(e)
WHERE t.e.value('@name', 'varchar(50)') = 'error_reported'

對于system_health最有幫助的用途之一是跟蹤死鎖。對于目標ringbuffer，存儲多少數據依賴于被監控機器上的該目標的容量，以及產生最大數量的設置相關，這些將在每個會話的定義中。你可以在system_health會話的輸出中找到過去的死鎖記錄。

所有查詢都會在system_health輸出中，可以通過運行下面的代碼獲得一個死鎖報表。

-- SQL Server 2008 R2
WITH SystemHealth
AS (
SELECT CAST(target_data as xml) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE name = 'system_health'
AND st.target_name = 'ring_buffer')
SELECT XEventData.XEvent.value('@timestamp','datetime')as Creation_Date,CAST(XEventData.XEvent.value('(data/value)[1]','VARCHAR(MAX)') AS XML) AS DeadLockGraph
FROM SystemHealth
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'
ORDER BY Creation_Date DESC

-- SQL Server 2012
WITH SystemHealth
AS (
SELECT CAST(target_data as xml) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE name = 'system_health'
AND st.target_name = 'ring_buffer')
SELECT XEventData.XEvent.value('@timestamp','datetime')as Creation_Date, XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadLockGraph
FROM SystemHealth
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report'
ORDER BY Creation_Date DESC

查看process-list的inputbuf子元素，可以看到導致死鎖的代碼片段，process-list顯示所有死鎖參與者的進程ID。process元素包含spid、數據庫id、登錄名、隔離級別、客戶端應用程序名。Resource-list元素包含在死鎖中的資源。查看owner-list和waiter-list元素可以看到這兩個進程如何互相阻塞。

嘗試將該XML的輸出保存為XDL文檔，用SSMS打開異常。目前有兩個選擇可以以圖形方式打開死鎖圖表：SQL Sentry Plan Explorer Pro 和 SQL Server 2012 Management Studio，詳見：https://www.sqlskills.com/blogs/jonathan/graphically-viewing-extended-events-deadlock-graphs/