第三篇——第二部分——第六文 監控SQL Server鏡像

標籤：

原文: 第三篇——第二部分——第六文 監控SQL Server鏡像

 

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

 

要最佳化，首先要監控，看看是否有效能問題，如果有，在哪裡。才能開始真正的最佳化，所以本文以監控為入口，在上一篇已經略微提供了一些監控方面的資訊

針對監控部分，本文將介紹以下內容：

1. 監控組件
2. 警告閾值
3. 資料庫鏡像監視器
4. 關於鏡像的系統預存程序
5. 效能計數器

 

1.1. 監控組件：

• 資料庫鏡像狀態表：

資料庫鏡像狀態儲存在MSDB中的dbm_monitor_data表中，每當資料庫鏡像狀態更改時，會通過sp_dbmmonitorupdate系統預存程序建立這個表。比如在前面的域環境搭建鏡像中，可以看到下面的結果：

首先按照http://blog.csdn.net/dba_huangzj/article/details/27652857 中step 6的方式把兩台機器加入註冊管理器中：

 

 

然後執行下面語句，可以看到有一些有價值的資訊：

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

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

• 資料庫鏡像狀態作業：

預設情況下，每分鐘運行一次，用於更新資料庫鏡像狀態表。運行間隔從1分鐘到120分鐘。這個作業實際上是調用sp_dbmmonitorupdate系統預存程序。如果SQL Server Agent沒有啟用，那麼這個作業也不能運行，這回導致前面說到的鏡像狀態表的資料變得過時。

這個作業在使用SSMS開始資料庫會話是自動建立，但是可以使用下面的系統預存程序自訂作業，詳細內容可以查看聯機叢書：

• sp_dbmmonitoraddmonitoring：建立資料庫鏡像監視器作業，該作業可定期補救伺服器執行個體上每個鏡像庫的鏡像狀態。注意，如果周期太低用戶端的回應時間可能會增加。比如把周期設為1分鐘：EXEC sp_dbmmonitoraddmonitoring 1
• sp_dbmmonitorchangemonitoring：更改資料庫鏡像監視參數的值，比如把更新周期更改為5分鐘：EXEC sp_dbmmonitorchangemonitoring 1,5;
• sp_dbmmonitordropmonitoring：停止並刪除伺服器執行個體上所有資料庫鏡像監視器作業。

警告閾值：

如果你使用過鏡像監視器，會發現有一個警告選項，

 

留意右下角的說明，通過閾值，可以設定一系列的應對措施，下面簡要說明一下各個閾值：

• 未發送日誌（unsent log）：錯誤號碼32042，在主體伺服器上設定，在高效能模式下非常重要，如果資料庫鏡像被暫停，即使是高安全模式下也可以用這個閾值。
• 未還原日誌（unrestored log）：錯誤號碼32043，在鏡像伺服器上設定，過量的未還原日誌意味著鏡像伺服器的I/O子系統可能存在效能問題導致鏡像伺服器無法跟上主體伺服器的進度。
• 最早未發送事務（Oldest Unsent transaction）：錯誤號碼32040，在主體伺服器上設定，在高效能模式下尤其重要。
• 鏡像提交開銷（Mirro commit overhead）：錯誤號碼32044，在鏡像伺服器上配置，設定平均事務延時提交的毫秒數。這個值是主體伺服器等待鏡像伺服器確認已經redo的時間。這個時間在高安全模式下才有意義，因為高效能模式下主體伺服器不需要等待鏡像伺服器的確認資訊。

閾值的使用在本文的【結合其他工具進行監控】和【關於鏡像的系統預存程序】部分詳細介紹。

 

資料庫鏡像監視器：開啟資料庫鏡像監視器：

可以通過下面方式開啟鏡像監視器：

註冊鏡像資料庫：

如果配置好鏡像，開啟監視器之後就有一些相關資訊，但是由於某些原因要配置鏡像庫的資訊，可以使用註冊鏡像資料庫的功能，

然後進行註冊：

預設情況下，SQL Server會優先以Windows 身分識別驗證連結，點擊確定之後，就完成添加工作。

使用監視器進行效能檢查：

鏡像監視器是很好的監控鏡像效能的工具，使用這個工具，我們首先要看鏡像狀態，一般出現【已同步】或者短時間的【正在同步】是允許的，但是其他狀態下，就需要檢查是否有問題。監控視窗沒30秒重新整理一次，如果你不是sysadmin角色而是dbm_monitor資料庫角色，需要等待鏡像監控作業自己更新。如果是sysadmin可以手動重新整理。

通過點擊【記錄】可以查看鏡像的傳輸的曆史資訊。

 

 

我們可以嘗試看看變動，在主體伺服器中寫一個死迴圈不停插入資料，然後看看監視器的結果，記得在查看之後停止死迴圈：

 

 

可以看到插入過程的確產生了日誌傳輸。

 

關於鏡像的系統預存程序：

前面略微提到過一些系統預存程序，實際上，鏡像甚至其他高可用技術都提供了一系列系統預存程序來實現幾乎甚至全部的GUI功能，作為DBA，會用系統預存程序是非常必要的。下面介紹主要的一些系統預存程序，更詳細的資訊請自行查看聯機叢書：

 

• sp_dbmmonitorresults：通過返回資料庫鏡像狀態表的資訊監控，必須在MSDB庫中執行。

 

在這個結果中，有些列要說明一下：

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：用於添加或修改特定的警告閾值。下面語句是建立一個閾值，當AdventureWorks2008R2庫中【最早未發送事務】超過20分鐘即發出警告。

  USE msdb GO EXEC sp_dbmmonitorchangealert        @database_name = N‘AdventureWorks2008R2‘,       @alert_id = 1,       @threshold = 20,       @enabled = 1

  
  
  

• sp_dbmmonitorhelpalert：返回特定庫中的閾值資訊。如：

  USE msdb GO EXEC sp_dbmmonitorhelpalert        @database_name = N‘AdventureWorks2008R2‘,       @alert_id = 1

  
  
  

• sp_dbmmonitordropalert：通過把閾值設為NULL來移除警告閾值，如：

  USE msdb GO EXEC sp_dbmmonitordropalert        @database_name = N‘AdventureWorks2008R2‘,       @alert_id = 1 

  
  
  

 

效能計數器

效能計數器在可見的將來（個人估計也有2020年之前）都是很重要的監控工具，並且依託強大的功能，可以進行效能分析、警告發送等等，別大量DBA用於日常管理。關於資料庫鏡像，計數器主要集中在：SQLServer:Database Mirroring 對象中，下面列出一些常用的計數器：

 

計數器名稱	解釋
Bytes Received/sec	每秒收到的位元組數。
Bytes Sent/sec	每秒發送的位元組數。
Log Bytes Received/sec	每秒收到的日誌位元組數。
Log Bytes Redone from Cache/sec	在上一秒鐘內從鏡像日誌緩衝中獲得的重做日誌位元組數。 此計數器只在鏡像伺服器上使用。 在主體伺服器上此值始終是 0。
Log Bytes Sent from Cache/sec	在上一秒鐘內從鏡像日誌緩衝中獲得的發送日誌位元組數。 此計數器只在主體伺服器上使用。 在鏡像伺服器上此值始終是 0。
Log Bytes Sent/sec	每秒發送的日誌位元組數。
Log Compressed Bytes Rcvd/sec	在上一秒鐘內所接收日誌的壓縮位元組數。
Log Compressed Bytes Sent/sec	在上一秒鐘內所發送日誌的壓縮位元組數。
Log Harden Time (ms)	日誌塊在上一秒鐘內等待強制寫入磁碟的時間（毫秒）。
Log Remaining for Undo KB	在容錯移轉之後等待由新的鏡像伺服器掃描的日誌總位元組數 (KB)。 此計數器僅可在復原階段在鏡像伺服器上使用。 撤銷階段完成後，計數器會重設為 0。 在主體伺服器上此值始終是 0。
Log Scanned for Undo KB	自容錯移轉開始已由新的鏡像伺服器掃描的日誌總位元組數 (KB)。 此計數器僅可在復原階段在鏡像伺服器上使用。 撤銷階段完成後，計數器會重設為 0。 在主體伺服器上此值始終是 0。
Log Send Flow Control Time (ms)	日誌流訊息在上一秒鐘內等待發送流量控制的時間（毫秒）。 在資料庫鏡像中，將日誌資料和中繼資料發送到鏡像夥伴是資料量最密集的操作，並可能獨佔資料庫鏡像和 Service Broker 發送緩衝區。 使用此計數器可監視資料庫鏡像會話使用此緩衝區的情況。
Log Send Queue KB	尚未發送到鏡像伺服器的日誌總位元組數 (KB)。
Mirrored Write Transactions/sec	在上一秒鐘內寫入鏡像資料庫並等待日誌發送到鏡像資料庫以進行提交的事務數。 僅當主體伺服器正在向鏡像伺服器發送日誌記錄時，此計數器才會增加。
Pages Sent/sec	每秒發送的頁數。
Receives/sec	每秒收到的鏡像訊息數。
Redo Bytes/sec	每秒在鏡像資料庫中前滾的日誌位元組數。
Redo Queue KB	當前仍應用於鏡像資料庫以進行前滾操作的鏡像日誌的總位元組數 (KB)。 此資料將從鏡像資料庫發送到主體資料庫。
Send/Receive Ack Time	在上一秒鐘內訊息等待夥伴確認的時間（毫秒）。 在解決可能由網路瓶頸導致的問題（例如莫名其妙的容錯移轉、發送隊列很大或事務延隔時間較長）時，此計數器非常有用。 在這些情況下，可以分析此計數器的值來確定是否是由於網路而導致出現上述問題。
Sends/sec	每秒發送的鏡像訊息數。
Transaction Delay	等待未終止的提交確認的延遲時間。

 

也可以在SSMS中通過sys.dm_os_performance_counters 這個DMV查詢，如：

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

通過上面介紹的方法，對資料庫鏡像進行狀態、閾值等方面的監控，可以儘可能快地響應鏡像出現的問題。

第三篇——第二部分——第六文 監控SQL Server鏡像