摘要：在SQL Server的日常管理中，讓SQL Server高效運(yùn)行，且性能良好，是DBA需要做的事。DBA需要了解數(shù)據(jù)庫的日常運(yùn)行情況，對性能進(jìn)行分析和調(diào)優(yōu)，需要對線上環(huán)境部署監(jiān)控。那我們都需要監(jiān)控哪些方面呢？SQL Server服務(wù)器的CPU、內(nèi)存、IO、網(wǎng)絡(luò)流量、緩存等資源性能怎么樣，各個相關(guān)服務(wù)如SQL Server服務(wù)、SQL Server代理服務(wù)等是否正常運(yùn)行，這些一般使用開源的監(jiān)控軟件

在SQL Server的日常管理中，讓SQL Server高效運(yùn)行，且性能良好，是DBA需要做的事。DBA需要了解數(shù)據(jù)庫的日常運(yùn)行情況，對性能進(jìn)行分析和調(diào)優(yōu)，需要對線上環(huán)境部署監(jiān)控。那我們都需要監(jiān)控哪些方面呢？

SQL Server服務(wù)器的CPU、內(nèi)存、IO、網(wǎng)絡(luò)流量、緩存等資源性能怎么樣，各個相關(guān)服務(wù)如SQL Server服務(wù)、SQL Server代理服務(wù)等是否正常運(yùn)行，這些一般使用開源的監(jiān)控軟件Zabbix來設(shè)置告警，當(dāng)然針對數(shù)據(jù)庫服務(wù)器的特性，添加一些SQL Server數(shù)據(jù)庫引擎的性能計(jì)數(shù)器進(jìn)行收集。

SQL Server各種日志會記錄有用的信息。因此可以監(jiān)控SQL Server錯誤日志、SQL Server代理日志等。

SQL Server數(shù)據(jù)庫避免不了一些異常狀態(tài)，比如錯誤的腳本導(dǎo)致的異常，空間不夠，磁盤掛了，復(fù)制失敗了等。這里我先提提SQL Server事件。這個意味著SQL Server發(fā)生特定錯誤產(chǎn)生的事件，每個事件都有對應(yīng)的數(shù)據(jù)庫、嚴(yán)重級別、錯誤號、錯誤文本?？梢葬槍σ恍O其嚴(yán)重的錯誤如823、824、825、832、855、856等進(jìn)行特定錯誤監(jiān)控，還可以針對嚴(yán)重的錯誤級別進(jìn)行監(jiān)控，如錯誤等級從19到25。

生產(chǎn)環(huán)境都會部署各種高可用技術(shù)，無論是鏡像、日志傳送、復(fù)制還是Alwayson，都需要部署相應(yīng)的監(jiān)控，注意一個是要監(jiān)控是否正常運(yùn)行，再就是性能怎么樣，設(shè)置一定的告警閾值。

上面的監(jiān)控基本能滿足基本生產(chǎn)需求，那么我們還要監(jiān)控哪些方面呢？

SQL Server的連接超時、執(zhí)行超時、死鎖。

SQL Server活動進(jìn)程、慢查詢、阻塞。

等待統(tǒng)計(jì)對于分析SQL Server引擎性能瓶頸非常關(guān)鍵，幫助診斷SQL Server以及特定查詢和批處理的性能問題。

環(huán)形緩沖區(qū)包含了最小的系統(tǒng)輸出，記錄了大量的XML格式信息，用于幫助分析狀態(tài)的變化提供更好的思路?？梢员O(jiān)控連接、異常、調(diào)度、安全、內(nèi)存等。

審核SQL Server數(shù)據(jù)庫引擎實(shí)例或單獨(dú)的數(shù)據(jù)庫，跟蹤和記錄數(shù)據(jù)庫引擎中發(fā)生的事件。

可以結(jié)合Powershell實(shí)現(xiàn)自動化監(jiān)控部署、結(jié)合SSRS實(shí)現(xiàn)平臺化展示。再進(jìn)一步深入到Web端的部署、運(yùn)維、監(jiān)控、性能分析等一體化。

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

我們就拿最常見的死鎖來談?wù)凷QL Server的監(jiān)控。

SQL Server 中如何監(jiān)控死鎖（Deadlock）

什么是死鎖？

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

由于資源占用是互斥的，當(dāng)某個進(jìn)程提出申請資源后，使得有關(guān)進(jìn)程在無外力協(xié)助下，永遠(yuǎn)分配不到必需的資源而無法繼續(xù)運(yùn)行，這就產(chǎn)生了一種特殊現(xiàn)象：死鎖。

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

死鎖監(jiān)控線程

如果我們查看sys.dm_os_waiting_tasks，我們可以發(fā)現(xiàn)一個系統(tǒng)任務(wù)一直處于等待狀態(tài)：REQUEST_FOR_DEADLOCK_SEARCH。該線程每五秒鐘被喚醒，來查看是否有死鎖。如果發(fā)現(xiàn)死鎖，它將結(jié)束一個會話。它會殺掉兩個會話中的一個，讓另一個會話擁有需要的所有資源。

SQL Server會判斷，要確保殺掉的是最容易回滾的會話。因?yàn)槿绻鸖QL Server殺掉一個事務(wù)，它所做的任何工作必須回滾到數(shù)據(jù)庫的同步狀態(tài)。它由LOG USED的值來決定。

我們可以看到上例圖殺掉了會話75而不是192，因?yàn)闀?5使用了648字節(jié)日志而會話192使用了944字節(jié)。

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

如何監(jiān)控死鎖？

方法一：

Windows性能監(jiān)控器（Performance Monitor）

Object: SQLServer:Locks

Counter: Number of Deadlocks/sec

Instance: _Total

下面的查詢提供了自從上次重啟以來在本服務(wù)器上發(fā)生的所有死鎖：

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'

方法二：

跟蹤標(biāo)識（Trace Flags）1204和1222

Trace Flag 1204至少從SQL Server 2000開始存在。Trace Flag 1222從SQL Server 2005被包含進(jìn)來。兩者的死鎖信息被記錄到SQL Server錯誤日志（ERRORLOG）。

方法三：

SQL Server Profiler和服務(wù)端的SQL Trace

Trace Event Class: Locks Event Name: Deadlock Graph

像上面示例一樣給出一個XML圖示。非常容易閱讀并找出當(dāng)前正在進(jìn)行什么動作。

方法四：

擴(kuò)展事件（Extended Events）

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

方法五：

System Health

一個新的默認(rèn)跟蹤，但它不像SQL Server默認(rèn)跟蹤（Default Trace）那樣有有限數(shù)量的跟蹤信息且不能修改。我們可以修改system health的定義，它內(nèi)置于擴(kuò)展事件中。不像默認(rèn)跟蹤，system health可以跟蹤到剛才已經(jīng)發(fā)生過的死鎖信息。我們可以從system health獲取這些信息用來分析而不用部署我們自己的擴(kuò)展事件監(jiān)控。

使用擴(kuò)展事件跟蹤監(jiān)控死鎖

我們通過SQL Server 2012圖形界面來部署一個擴(kuò)展事件跟蹤會話。然后可以生成SQL腳本，在2008或2008 R2版本下運(yùn)行類似的跟蹤。

步驟1：

通過“Object Explorer”連接到實(shí)例，展開“Management”、“Extended Events”、“Sessions”。

步驟2：

右鍵點(diǎn)擊“Sessions”，創(chuàng)建一個新的會話向?qū)А?/p>

步驟3：

輸入會話名稱“Deadlock_Monitor”，點(diǎn)擊下一步。

步驟4：

選擇不使用模板（像SQL Server Profiler模板一樣，預(yù)設(shè)了一些默認(rèn)選項(xiàng)一起啟動，但沒有一個滿足我們需求的模板），點(diǎn)擊下一步

步驟5：

選擇要捕獲的事件，在“Event library”輸入deadlock，可看到如下圖所示：

步驟6：

選擇“xml_deadlock_report”，添加到右側(cè)選擇的事件列表中。再單擊下一步。

步驟7：

選擇要捕獲的列，這里我們選擇下一步。

步驟8：

定義過濾條件，這里我們忽略這個設(shè)置，點(diǎn)擊下一步。

步驟9：

選擇保存數(shù)據(jù)到文件，設(shè)置文件路徑和最大值等。點(diǎn)擊下一步。

步驟10：

檢查所有的配置，點(diǎn)擊完成來安裝和啟用會話。

步驟11：

現(xiàn)在我們可以啟動捕獲，并查看活動數(shù)據(jù)。

步驟12：

在剛才創(chuàng)建會話“Deadlock_Monitor”上右鍵點(diǎn)擊生成腳本。

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：

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

--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…”。選擇對應(yīng)timestamp的死鎖條目，在Details的xml_report值里顯示的就是死鎖的XML文件，可雙擊打開。點(diǎn)擊Deadlock即可看到死鎖的圖形化展示。

深入進(jìn)階

死鎖詳細(xì)信息還有幾個步驟可用來配置擴(kuò)展事件來監(jiān)控死鎖。

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

1. Lock: Deadlock事件類

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

2. Lock: Deadlock chain事件類

這個事件類用于監(jiān)控死鎖狀態(tài)。當(dāng)有一個死鎖時該事件被觸發(fā)。通過在實(shí)例級別監(jiān)控這個事件，我們能夠識別那些對象在死鎖中，我們是否在應(yīng)用程序中有死鎖導(dǎo)致的性能問題。

步驟1：

在之前的“Deadlock_Monitor”會話上右鍵選擇“Properties”。選擇“Events”頁，將lock_deadlock和lock_deadlock_chain事件類添加到右側(cè)已選擇事件列表。

步驟2：

運(yùn)行之前的死鎖示例。

步驟3：

在“Deadlock_Monitor”上的package0.event_file上右鍵選擇“View Target Data…”。選擇對應(yīng)timestamp的死鎖條目。

如果有用戶反饋說他們在應(yīng)用程序的錯誤日志里發(fā)現(xiàn)了輸出了死鎖信息，而且是在深夜。我們就可以知道怎么監(jiān)控和獲取死鎖數(shù)據(jù)了。

使用system_health默認(rèn)跟蹤會話監(jiān)控死鎖

自SQL Server 2008以后，提供了擴(kuò)展事件（Extended Events）來跟蹤系統(tǒng)分析定位問題。默認(rèn)的system_health會話一直在運(yùn)行，可以幫助你更快的定位問題。

運(yùn)行如下腳本可以看到system_health擴(kuò)展事件會話：

SELECT * FROM sys.dm_xe_sessions

 即便是你沒有啟動任何擴(kuò)展事件會話，這個查詢也會返回一行system_health會話。

SQL Server 2012版本之前，并不提供管理擴(kuò)展事件會話的圖形界面，你可以從這里下載SQL Server 2008 Extended Events SSMS Addin插件：http://extendedeventmanager.codeplex.com/

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

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

我們右鍵點(diǎn)擊“system_health”，生成腳本，我們可以看到該會話的內(nèi)容。你也可以在SQL Server的安裝目錄：C:\Program Files\Microsoft SQL Server\MSSQL11.<instanceid>\MSSQL\Install

下找到腳本u_tables.sql文件。

從定義可以看到，會話的輸出包含callstack、sessionID、TSQL和TSQL Call Stack

且當(dāng)安全等級大于20或者錯誤號為17803等。它們與內(nèi)存壓力相關(guān)、Non-yielding scheduler問題、死鎖和一些類型的等待。

會話輸出被捕獲到遵從FIFO規(guī)則的ring_buffer中，ring_buffer是一個內(nèi)存使用者，它以二進(jìn)制格式存儲捕獲數(shù)據(jù)。當(dāng)事件會話啟用的時候，數(shù)據(jù)即可被捕獲。當(dāng)停止會話的時候，分配給ring_buffer的內(nèi)存被釋放，且數(shù)據(jù)消失。注意：對于SQL Server 2012之前，system_health的目標(biāo)只有ring_buffer，從SQL Server 2012開始，增加了event_file的輸出。

你可以通過關(guān)聯(lián)sys.dm_xe_session_targets和sys.dm_xe_sessions視圖來查看ring_buffer或event_file的內(nèi)容，并轉(zhuǎn)換二進(jìn)制數(shù)據(jù)為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的輸出是捕獲到的數(shù)據(jù)。

在ring_buffer中，每一個事件元素都有一個數(shù)據(jù)子集和一個動作子集。這些動作是在會話的定義中。數(shù)據(jù)元素包含了每個事件的數(shù)據(jù)類型列的所有值。這些列可通過sys.dm_xe_object_columns視圖輸出。讓我們解析XML格式以表格格式查看內(nèi)容。因?yàn)槊總€事件返回?cái)?shù)據(jù)列的不同集合。下面給一個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最有幫助的用途之一是跟蹤死鎖。對于目標(biāo)ringbuffer，存儲多少數(shù)據(jù)依賴于被監(jiān)控機(jī)器上的該目標(biāo)的容量，以及產(chǎn)生最大數(shù)量的設(shè)置相關(guān)，這些將在每個會話的定義中。你可以在system_health會話的輸出中找到過去的死鎖記錄。

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

-- 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子元素，可以看到導(dǎo)致死鎖的代碼片段，process-list顯示所有死鎖參與者的進(jìn)程ID。process元素包含spid、數(shù)據(jù)庫id、登錄名、隔離級別、客戶端應(yīng)用程序名。Resource-list元素包含在死鎖中的資源。查看owner-list和waiter-list元素可以看到這兩個進(jìn)程如何互相阻塞。

嘗試將該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/