SQL SERVER AlwaysOn 可讀次要對主要複本效能的影響

問：AlwaysOn次要節點設為「可讀」會對主要節點效能產生影響嗎？

答：會 （但不算太嚴重）

有人可能覺得奇怪，次要節點在邏輯上而言是另一台獨立主機，在它上面的讀取的動作應該消耗的是它自己主機的資源，跟主要節點有什麼關係呢？

場景

AlwaysOn同步認可流程示意

我們知道在讀取資料時會在相關的row/page或table上獲取共享鎖（share lock），如果次節點正在select，而主節點也在update，那這update的動作傳送到次節點並寫入次要節點的交易紀錄檔後，即算是同步完成(同步認可模式)，接著傳回ACK通知主節點已同步完成。在這一階段它並不會因次節點正在select而被block，因為資料還沒有真正的寫進data file。

接著當Redo Thread去讀取交易紀錄並重做時，正常模式下由於資料正在select，update就必需等待。這時候在次要節點就會產生block。這將導致後續其它的異動全都要排隊，無法即時同步到次要節點。如果次要節點在執行的是一個長時間的報表查詢，那麼同步延遲的時間將不斷拉大。若有其它使用者去查詢別的資料，他查到的可能是數分鐘甚至數小時前的資料，這跟我們所認知的「同步認可」差異太大，顯然不是一個好的同步運作方式。

AlwaysOn為了避免上述的情況，所有在可讀次要節點執行的Queries，都將自動設為 read-committed snapshot isolation，

在這個隔離層級下，Queries不會在相關的資料上獲取共享鎖，因此不會發生block而影響主節點的異動，也避免增加資料的延遲。

這看起來不錯啊！也確實能解決block的問題….

影響

有興趣的朋友可以去瞭解一下read-committed snapshot isolation，它會在tempdb中儲存修改前的資料，並且在自身的實體資料尾部增加一個14 bytes的指標來儲存資料版本訊息。

那麼問題來了~~

在AlwaysOn中是採用Log Redo來達成同步，並不是以SQL Statement的方式同步（如replication）。所以在主要及次節要點上，兩者的資料在實體上必需是一致。

如果次要節點設為可讀，它必需在實體資料尾部增加14 bytes來儲存版本資訊，這樣一來主次要的實體資料就不一致了。所以，這個14 bytes的空間及版本資訊，必需由主節點增加，並寫入交易紀錄透過同步機制同步過去，才能讓次要可讀節點去使用及判斷。

因此，如果你將AlwaysOn的次要節點設為可讀，當你在主節點執行update/insert/delete時，會在原本的資料行尾部多出14 bytes的空間以紀錄版本資訊。

這代表著，假設你去update資料時(加入AlwaysOn後未曾update過)，受影響的每筆資料長度將額外多出14 bytes：

一、   容易造成頁面分割（page split），增加索引頁面的破碎。

二、   資料及交易紀錄容量的增加。

結語

以上簡單的描述了AlwaysOn可讀次要對主節點效能的影響。有興趣的朋友可以自行去做個測試。其實它主要是索引破碎率增加，這個問題並不算嚴重或許也因此很少被high-light出來。

當一個現有DB被加入AlwaysOn後（次要可讀），DBA需知道可能會造成碎片的增加並增加碎片檢查的頻率，再用一般維護時相同的方式去處理碎片問題即可。