SQL Server監(jiān)控全解析

 

創(chuàng)新互聯(lián)建站提供網(wǎng)站建設(shè)、成都網(wǎng)站制作、網(wǎng)頁(yè)設(shè)計(jì)，成都品牌網(wǎng)站建設(shè)，廣告投放等致力于企業(yè)網(wǎng)站建設(shè)與公司網(wǎng)站制作，十載的網(wǎng)站開發(fā)和建站經(jīng)驗(yàn),助力企業(yè)信息化建設(shè)，成功案例突破近千家,是您實(shí)現(xiàn)網(wǎng)站建設(shè)的好選擇.

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

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

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

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

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

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

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

6. SQL Server活動(dòng)進(jìn)程、慢查詢、阻塞。

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

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

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

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

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

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

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

什么是死鎖？

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

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

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

死鎖監(jiān)控線程

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

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

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

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

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

方法一：

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

Object: SQLServer:Locks

Counter: Number of Deadlocks/sec

Instance: _Total

下面的查詢提供了自從上次重啟以來(lái)在本服務(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)識(shí)（Trace Flags）1204和1222

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

方法三：

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

Trace Event Class: Locks Event Name: Deadlock Graph

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

方法四：

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

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

方法五：

System Health

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

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

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

步驟1：

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

步驟2：

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

步驟3：

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

步驟4：

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

步驟5：

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

步驟6：

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

步驟7：

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

步驟8：

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

步驟9：

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

步驟10：

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

步驟11：

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

步驟12：

在剛才創(chuàng)建會(huì)話“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：

在會(huì)話“Deadlock_Monitor”上右鍵選擇啟動(dòng)會(huì)話。

步驟14：

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

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

深入進(jìn)階

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

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

1.Lock: Deadlock事件類

這個(gè)事件類可以用來(lái)驗(yàn)證死鎖犧牲品。這個(gè)事件說(shuō)明什么時(shí)候請(qǐng)求需要一個(gè)鎖，但被取消作為一個(gè)死鎖犧牲品。

2.Lock: Deadlock chain事件類

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

步驟1：

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

步驟2：

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

步驟3：

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

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

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

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

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

SELECT * FROM sys.dm_xe_sessions

即便是你沒(méi)有啟動(dòng)任何擴(kuò)展事件會(huì)話，這個(gè)查詢也會(huì)返回一行system_health會(huì)話。

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

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

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

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

下找到腳本u_tables.sql文件。

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

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

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

你可以通過(guò)關(guān)聯(lián)sys.dm_xe_session_targets和sys.dm_xe_sessions視圖來(lái)查看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的輸出是文件的存儲(chǔ)路徑，而ring_buffer的輸出是捕獲到的數(shù)據(jù)。

在ring_buffer中，每一個(gè)事件元素都有一個(gè)數(shù)據(jù)子集和一個(gè)動(dòng)作子集。這些動(dòng)作是在會(huì)話的定義中。數(shù)據(jù)元素包含了每個(gè)事件的數(shù)據(jù)類型列的所有值。這些列可通過(guò)sys.dm_xe_object_columns視圖輸出。讓我們解析XML格式以表格格式查看內(nèi)容。因?yàn)槊總€(gè)事件返回?cái)?shù)據(jù)列的不同集合。下面給一個(gè)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'

對(duì)于system_health最有幫助的用途之一是跟蹤死鎖。對(duì)于目標(biāo)ringbuffer，存儲(chǔ)多少數(shù)據(jù)依賴于被監(jiān)控機(jī)器上的該目標(biāo)的容量，以及產(chǎn)生最大數(shù)量的設(shè)置相關(guān)，這些將在每個(gè)會(huì)話的定義中。你可以在system_health會(huì)話的輸出中找到過(guò)去的死鎖記錄。

所有查詢都會(huì)在system_health輸出中，可以通過(guò)運(yùn)行下面的代碼獲得一個(gè)死鎖報(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ù)庫(kù)id、登錄名、隔離級(jí)別、客戶端應(yīng)用程序名。Resource-list元素包含在死鎖中的資源。查看owner-list和waiter-list元素可以看到這兩個(gè)進(jìn)程如何互相阻塞。

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

分享名稱：SQLServer監(jiān)控全解析
路徑分享：http://jinyejixie.com/article6/jjepog.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供云服務(wù)器、企業(yè)建站、移動(dòng)網(wǎng)站建設(shè)、定制網(wǎng)站、企業(yè)網(wǎng)站制作、網(wǎng)頁(yè)設(shè)計(jì)公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)