原文: 第三篇——第二部分——第六文 監(jiān)控SQL Server鏡像

?

原文出處：http://blog.csdn.net/dba_huangzj/article/details/26846203

?

要優(yōu)化，首先要監(jiān)控，看看是否有性能問(wèn)題，如果有，在哪里。才能開始真正的優(yōu)化，所以本文以監(jiān)控為入口，在上一篇已經(jīng)略微提供了一些監(jiān)控方面的信息

針對(duì)監(jiān)控部分，本文將介紹以下內(nèi)容：

監(jiān)控組件

警告閾值

數(shù)據(jù)庫(kù)鏡像監(jiān)視器

關(guān)于鏡像的系統(tǒng)存儲(chǔ)過(guò)程

性能計(jì)數(shù)器

?

1.1. 監(jiān)控組件：

• 數(shù)據(jù)庫(kù)鏡像狀態(tài)表：

數(shù)據(jù)庫(kù)鏡像狀態(tài)存儲(chǔ)在MSDB中的dbm_monitor_data表中，每當(dāng)數(shù)據(jù)庫(kù)鏡像狀態(tài)更改時(shí)，會(huì)通過(guò)sp_dbmmonitorupdate系統(tǒng)存儲(chǔ)過(guò)程創(chuàng)建這個(gè)表。比如在前面的域環(huán)境搭建鏡像中，可以看到下面的結(jié)果：

首先按照http://blog.csdn.net/dba_huangzj/article/details/27652857 中step 6的方式把兩臺(tái)機(jī)器加入注冊(cè)管理器中：

?

?

然后執(zhí)行下面語(yǔ)句，可以看到有一些有價(jià)值的信息：

SELECT * FROM msdb.dbo.dbm_monitor_data ORDER BY local_time DESC

其中比較有價(jià)值的是role、各種rate等。

• 數(shù)據(jù)庫(kù)鏡像狀態(tài)作業(yè)：

默認(rèn)情況下，每分鐘運(yùn)行一次，用于更新數(shù)據(jù)庫(kù)鏡像狀態(tài)表。運(yùn)行間隔從1分鐘到120分鐘。這個(gè)作業(yè)實(shí)際上是調(diào)用sp_dbmmonitorupdate系統(tǒng)存儲(chǔ)過(guò)程。如果SQL Server Agent沒(méi)有啟用，那么這個(gè)作業(yè)也不能運(yùn)行，這回導(dǎo)致前面說(shuō)到的鏡像狀態(tài)表的數(shù)據(jù)變得過(guò)時(shí)。

這個(gè)作業(yè)在使用SSMS開始數(shù)據(jù)庫(kù)會(huì)話是自動(dòng)創(chuàng)建，但是可以使用下面的系統(tǒng)存儲(chǔ)過(guò)程自定義作業(yè)，詳細(xì)內(nèi)容可以查看聯(lián)機(jī)叢書：

• • sp_dbmmonitoraddmonitoring：創(chuàng)建數(shù)據(jù)庫(kù)鏡像監(jiān)視器作業(yè)，該作業(yè)可定期更新服務(wù)器實(shí)例上每個(gè)鏡像庫(kù)的鏡像狀態(tài)。注意，如果周期太低客戶端的響應(yīng)時(shí)間可能會(huì)增加。比如把周期設(shè)為1分鐘：EXEC sp_dbmmonitoraddmonitoring 1
  • sp_dbmmonitorchangemonitoring：更改數(shù)據(jù)庫(kù)鏡像監(jiān)視參數(shù)的值，比如把更新周期更改為5分鐘：EXEC sp_dbmmonitorchangemonitoring 1,5;
  • sp_dbmmonitordropmonitoring：停止并刪除服務(wù)器實(shí)例上所有數(shù)據(jù)庫(kù)鏡像監(jiān)視器作業(yè)。

警告閾值：

如果你使用過(guò)鏡像監(jiān)視器，會(huì)發(fā)現(xiàn)有一個(gè)警告選項(xiàng)，如圖：

?

留意右下角的說(shuō)明，通過(guò)閾值，可以設(shè)置一系列的應(yīng)對(duì)措施，下面簡(jiǎn)要說(shuō)明一下各個(gè)閾值：

• 未發(fā)送日志（unsent log）：錯(cuò)誤號(hào)32042，在主體服務(wù)器上設(shè)置，在高性能模式下非常重要，如果數(shù)據(jù)庫(kù)鏡像被暫停，即使是高安全模式下也可以用這個(gè)閾值。
• 未還原日志（unrestored log）：錯(cuò)誤號(hào)32043，在鏡像服務(wù)器上設(shè)置，過(guò)量的未還原日志意味著鏡像服務(wù)器的I/O子系統(tǒng)可能存在性能問(wèn)題導(dǎo)致鏡像服務(wù)器無(wú)法跟上主體服務(wù)器的進(jìn)度。
• 最早未發(fā)送事務(wù)（Oldest Unsent transaction）：錯(cuò)誤號(hào)32040，在主體服務(wù)器上設(shè)置，在高性能模式下尤其重要。
• 鏡像提交開銷（Mirro commit overhead）：錯(cuò)誤號(hào)32044，在鏡像服務(wù)器上配置，設(shè)置平均事務(wù)延時(shí)提交的毫秒數(shù)。這個(gè)值是主體服務(wù)器等待鏡像服務(wù)器確認(rèn)已經(jīng)redo的時(shí)間。這個(gè)時(shí)間在高安全模式下才有意義，因?yàn)楦咝阅苣Ｊ较轮黧w服務(wù)器不需要等待鏡像服務(wù)器的確認(rèn)信息。

閾值的使用在本文的【結(jié)合其他工具進(jìn)行監(jiān)控】和【關(guān)于鏡像的系統(tǒng)存儲(chǔ)過(guò)程】部分詳細(xì)介紹。

?

數(shù)據(jù)庫(kù)鏡像監(jiān)視器：

打開數(shù)據(jù)庫(kù)鏡像監(jiān)視器：

可以通過(guò)下面方式打開鏡像監(jiān)視器：

注冊(cè)鏡像數(shù)據(jù)庫(kù)：

如果配置好鏡像，打開監(jiān)視器之后就有一些相關(guān)信息，但是由于某些原因要配置鏡像庫(kù)的信息，可以使用注冊(cè)鏡像數(shù)據(jù)庫(kù)的功能，如圖：

然后進(jìn)行注冊(cè)：

默認(rèn)情況下，SQL Server會(huì)優(yōu)先以Windows 身份驗(yàn)證鏈接，點(diǎn)擊確定之后，就完成添加工作。

使用監(jiān)視器進(jìn)行性能檢查：

鏡像監(jiān)視器是很好的監(jiān)控鏡像性能的工具，使用這個(gè)工具，我們首先要看鏡像狀態(tài)，一般出現(xiàn)【已同步】或者短時(shí)間的【正在同步】是允許的，但是其他狀態(tài)下，就需要檢查是否有問(wèn)題。監(jiān)控窗口沒(méi)30秒刷新一次，如果你不是sysadmin角色而是dbm_monitor數(shù)據(jù)庫(kù)角色，需要等待鏡像監(jiān)控作業(yè)自己更新。如果是sysadmin可以手動(dòng)刷新。

通過(guò)點(diǎn)擊【歷史記錄】可以查看鏡像的傳輸?shù)臍v史信息。

?

?

我們可以嘗試看看變動(dòng)，在主體服務(wù)器中寫一個(gè)死循環(huán)不停插入數(shù)據(jù)，然后看看監(jiān)視器的結(jié)果，記得在查看之后停止死循環(huán)：

?

?

可以看到插入過(guò)程的確產(chǎn)生了日志傳輸。

?

關(guān)于鏡像的系統(tǒng)存儲(chǔ)過(guò)程：

前面略微提到過(guò)一些系統(tǒng)存儲(chǔ)過(guò)程，實(shí)際上，鏡像甚至其他高可用技術(shù)都提供了一系列系統(tǒng)存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)幾乎甚至全部的GUI功能，作為DBA，會(huì)用系統(tǒng)存儲(chǔ)過(guò)程是非常必要的。下面介紹主要的一些系統(tǒng)存儲(chǔ)過(guò)程，更詳細(xì)的信息請(qǐng)自行查看聯(lián)機(jī)叢書：

?

• sp_dbmmonitorresults：通過(guò)返回?cái)?shù)據(jù)庫(kù)鏡像狀態(tài)表的信息監(jiān)控，必須在MSDB庫(kù)中執(zhí)行。

?

在這個(gè)結(jié)果中，有些列要說(shuō)明一下：

role

? 1: Principal
? 2: Mirror

mirroring_state

? 0: Suspended
? 1: Disconnected
? 2: Synchronizing
? 3: Pending Failover
? 4: Synchronized

witness_state

? 0: Unknown
? 1: Connected
? 2: Disconnected

• sp_dbmmonitorchangealert：用于添加或修改特定的警告閾值。下面語(yǔ)句是創(chuàng)建一個(gè)閾值，當(dāng)AdventureWorks2008R2庫(kù)中【最早未發(fā)送事務(wù)】超過(guò)20分鐘即發(fā)出警告。
  USE msdb GO EXEC sp_dbmmonitorchangealert @database_name = N'AdventureWorks2008R2', @alert_id = 1, @threshold = 20, @enabled = 1
  
  
• sp_dbmmonitorhelpalert：返回特定庫(kù)中的閾值信息。如：
  USE msdb GO EXEC sp_dbmmonitorhelpalert @database_name = N'AdventureWorks2008R2', @alert_id = 1
  
  
• sp_dbmmonitordropalert：通過(guò)把閾值設(shè)為NULL來(lái)移除警告閾值，如：
  USE msdb GO EXEC sp_dbmmonitordropalert @database_name = N'AdventureWorks2008R2', @alert_id = 1
  
  

?

性能計(jì)數(shù)器

性能計(jì)數(shù)器在可見的將來(lái)（個(gè)人估計(jì)也有2020年之前）都是很重要的監(jiān)控工具，并且依托強(qiáng)大的功能，可以進(jìn)行性能分析、警告發(fā)送等等，別大量DBA用于日常管理。關(guān)于數(shù)據(jù)庫(kù)鏡像，計(jì)數(shù)器主要集中在：SQLServer:Database Mirroring 對(duì)象中，下面列出一些常用的計(jì)數(shù)器：

?

計(jì)數(shù)器名稱	解釋
Bytes Received/sec	每秒收到的字節(jié)數(shù)。
Bytes Sent/sec	每秒發(fā)送的字節(jié)數(shù)。
Log Bytes Received/sec	每秒收到的日志字節(jié)數(shù)。
Log Bytes Redone from Cache/sec	在上一秒鐘內(nèi)從鏡像日志緩存中獲得的重做日志字節(jié)數(shù)。 此計(jì)數(shù)器只在鏡像服務(wù)器上使用。 在主體服務(wù)器上此值始終是 0。
Log Bytes Sent from Cache/sec	在上一秒鐘內(nèi)從鏡像日志緩存中獲得的發(fā)送日志字節(jié)數(shù)。 此計(jì)數(shù)器只在主體服務(wù)器上使用。 在鏡像服務(wù)器上此值始終是 0。
Log Bytes Sent/sec	每秒發(fā)送的日志字節(jié)數(shù)。
Log Compressed Bytes Rcvd/sec	在上一秒鐘內(nèi)所接收日志的壓縮字節(jié)數(shù)。
Log Compressed Bytes Sent/sec	在上一秒鐘內(nèi)所發(fā)送日志的壓縮字節(jié)數(shù)。
Log Harden Time (ms)	日志塊在上一秒鐘內(nèi)等待強(qiáng)制寫入磁盤的時(shí)間（毫秒）。
Log Remaining for Undo KB	在故障轉(zhuǎn)移之后等待由新的鏡像服務(wù)器掃描的日志總字節(jié)數(shù) (KB)。 此計(jì)數(shù)器僅可在撤消階段在鏡像服務(wù)器上使用。 撤銷階段完成后，計(jì)數(shù)器會(huì)重置為 0。 在主體服務(wù)器上此值始終是 0。
Log Scanned for Undo KB	? 自故障轉(zhuǎn)移開始已由新的鏡像服務(wù)器掃描的日志總字節(jié)數(shù) (KB)。 此計(jì)數(shù)器僅可在撤消階段在鏡像服務(wù)器上使用。 撤銷階段完成后，計(jì)數(shù)器會(huì)重置為 0。 在主體服務(wù)器上此值始終是 0。
Log Send Flow Control Time (ms)	? 日志流消息在上一秒鐘內(nèi)等待發(fā)送流控制的時(shí)間（毫秒）。 在數(shù)據(jù)庫(kù)鏡像中，將日志數(shù)據(jù)和元數(shù)據(jù)發(fā)送到鏡像伙伴是數(shù)據(jù)量最密集的操作，并可能獨(dú)占數(shù)據(jù)庫(kù)鏡像和 Service Broker 發(fā)送緩沖區(qū)。 使用此計(jì)數(shù)器可監(jiān)視數(shù)據(jù)庫(kù)鏡像會(huì)話使用此緩沖區(qū)的情況。
Log Send Queue KB	尚未發(fā)送到鏡像服務(wù)器的日志總字節(jié)數(shù) (KB)。
Mirrored Write Transactions/sec	在上一秒鐘內(nèi)寫入鏡像數(shù)據(jù)庫(kù)并等待日志發(fā)送到鏡像數(shù)據(jù)庫(kù)以進(jìn)行提交的事務(wù)數(shù)。 僅當(dāng)主體服務(wù)器正在向鏡像服務(wù)器發(fā)送日志記錄時(shí)，此計(jì)數(shù)器才會(huì)增加。
Pages Sent/sec	每秒發(fā)送的頁(yè)數(shù)。
Receives/sec	每秒收到的鏡像消息數(shù)。
Redo Bytes/sec	每秒在鏡像數(shù)據(jù)庫(kù)中前滾的日志字節(jié)數(shù)。
Redo Queue KB	當(dāng)前仍應(yīng)用于鏡像數(shù)據(jù)庫(kù)以進(jìn)行前滾操作的鏡像日志的總字節(jié)數(shù) (KB)。 此數(shù)據(jù)將從鏡像數(shù)據(jù)庫(kù)發(fā)送到主體數(shù)據(jù)庫(kù)。
Send/Receive Ack Time	在上一秒鐘內(nèi)消息等待伙伴確認(rèn)的時(shí)間（毫秒）。 在解決可能由網(wǎng)絡(luò)瓶頸導(dǎo)致的問(wèn)題（例如莫名其妙的故障轉(zhuǎn)移、發(fā)送隊(duì)列很大或事務(wù)滯后時(shí)間較長(zhǎng)）時(shí)，此計(jì)數(shù)器非常有用。 在這些情況下，可以分析此計(jì)數(shù)器的值來(lái)確定是否是由于網(wǎng)絡(luò)而導(dǎo)致出現(xiàn)上述問(wèn)題。
Sends/sec	每秒發(fā)送的鏡像消息數(shù)。
Transaction Delay	等待未終止的提交確認(rèn)的延遲時(shí)間。

?

也可以在SSMS中通過(guò)sys.dm_os_performance_counters 這個(gè)DMV查詢，如：

SELECT object_name, counter_name, instance_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name like '%mirror%'

通過(guò)上面介紹的方法，對(duì)數(shù)據(jù)庫(kù)鏡像進(jìn)行狀態(tài)、閾值等方面的監(jiān)控，可以盡可能快地響應(yīng)鏡像出現(xiàn)的問(wèn)題。

總結(jié)

以上是生活随笔為你收集整理的第三篇——第二部分——第六文 监控SQL Server镜像的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。