SQL問題與解答：行溢出、差異備份及更多內容

問 我最近升級了一個應用程式，使其可以在 SQL Server 2005 上運行。我利用了允許行長度超出 8,060 個位元組這項功能，以便使用者可以建立較長的資料欄位而不會收到從 SQL Server 返回的錯誤。現在，將這個應用程式應用到實際環境之後，一些掃描查詢開始出現效能問題，在架構更改之前，這些查詢運行正常。我也檢查過各種索引的片段，一切正常。那為什麼查詢在 SQL Server 2005 上運行時速度比較慢呢？

答 您所利用的“行溢出”功能，對於在特定情況下允許行長度大於 8,060 個位元組效果很好，但卻不適合大多數長度過大的行，而且可能使查詢效能大打折扣，正如您所遇到的情況那樣。

發生這種情況的原因是，當某行的長度開始變得過大時，該行中的其中一個可變長度列會被“推出行”。這意味著該列會在資料或索引頁上從行中移到文本頁中。至於原來列中的值，會由指標取代，指向該列中的值在資料檔案中的新位置。

這與用來儲存 XML、文本、映像或 varchar(max) 等常規 LOB大型物件）列的機制完全相同。請注意，如果表架構包含多個可變長度列，就無法保證在多個行的長度變得過大時推出的會是同一列。

這種機制可能會產生效能問題。如果查詢從一個表格行中檢索的可變長度列已被推出該行，可能突然之間需要額外的 I/O 來讀取內含行外位置的值的文本頁。如果有多個行的長度過大，從多個行中檢索相同的可變長度列的查詢，可能產生無法預料的效能問題，嚴重程度取決於被推出行的值的數量。

在您遇到的情況中，對包含可變長度列的挑選清單執行範圍掃描或表掃描的查詢，正是因行溢出及其影響而導致效能下降。這與索引是否執行過完全的磁碟重組無關，當可變長度列被推出行時，因為必須使用隨機 I/O 讀取內含行外的值的文本頁，所以之前有效掃描工作已基本中斷。

雖然行溢出在特定的情況下對於長度過大的行仍然很有用，但如果查詢的效能至關重要，則不應該在您的設計裡面過度利用。

問 我們剛在兩個容錯移轉叢集之間引入了資料庫鏡像，作為以低於存放區域網路 (SAN) 複製的成本獲得地理冗餘的方法。因為資料中心位於同一個城市，所以我們能夠使用同步鏡像。問題在於當本地群集上發生容錯移轉時，鏡像資料庫會容錯移轉到遠程群集，而這並不是我們希望發生的情況。我們該如何避免出現這種情況？我們只希望在本地群集無法使用的時才進行容錯移轉。

答 為了提高可用性，鏡像會安裝一個見證伺服器，以便在主體伺服器無法使用時自動發生容錯移轉。其理論基礎是：如果整個本地群集出現故障，資料庫鏡像將容錯移轉到第二個群集，這樣應用程式就可以繼續執行了。

此問題出現在群集容錯移轉期間。容錯移轉所花的時間超過了資料庫鏡像的預設逾時設定，而見證伺服器和鏡像伺服器即第二個群集上活動的 SQL Server 執行個體）均認為它們看不到主體伺服器，於是鏡像伺服器便開始將鏡像容錯移轉到第二個群集。

預防這種現象最簡單的方法是刪除見證伺服器，以便資料庫鏡像在本地群集出現故障時不會自動進行容錯移轉。當然，這種做法會降低可用性，因為這樣一來就需要人為啟動容錯移轉。

第二種方法是更改資料庫鏡像的預設逾時設定，也就是更改確定主體伺服器不可用之前，它響應“ping”資訊每秒一次）失敗的次數。這種設定稱為“夥伴逾時”(Parnter Timeout)，預設值為 10。可使用下列代碼找到資料庫當前的逾時值：

 

  
SELECT mirroring_connection_timeout  
  
  FROM master.sys.database_mirroring   
  
  WHERE database_id = DB_ID ('mydbname');  
  
GO 
 

可使用下列代碼更改逾時值：

 

  
ALTER DATABASE mydbname   
  
  SET PARTNER TIMEOUT <timeoutvalue>;  
  
GO 
 

對於這種情況，設定的夥伴逾時值必須大於在本地群集上進行群集容錯移轉的常規時間值。在鏡像資料庫上進行群集容錯移轉時確定運行恢複所需的時間變化，可能有些困難，不過您應該可以判斷出上限。這種方法的缺點在於逾時值可能必須以分鐘為單位，不適合在發生真正的災難時使用。

問 我使用的備份策略包括完整備份和記錄備份，但有人建議我應該加入差異備份來縮短還原時間。我每周進行一次完整備份，每個小時進行一次記錄備份。我試過每天添加差異備份，但我注意到一個異常現象：每個星期結束時的差異備份與每周的完整備份大小差不多。我記得差異備份與記錄備份一樣都屬於增量備份啊！難道是我記錯了嗎？
答 這是對差異備份的本質有所誤解造成的。差異備份與記錄備份不同，不屬於增量備份。差異備份包含自上次完整備份後所有更改的資料檔案範圍這適用於資料庫、檔案組和檔案層級備份）。

如果範圍包含八個連續資料檔案頁的邏輯組）有任何更改，都會標記在稱為差異圖的特殊位元影像頁中。每個資料檔案的每 4GB 就有一個差異圖。進行差異備份時，備份子系統會掃描所有差異圖，並複製所有已更改的範圍，但不會重設差異圖。這表示連續的差異備份之間更改的範圍越大，後者的備份會越大。只有在執行完整備份時才會重設差異圖。

如果應用程式工作負載太大，以至於資料庫內容在短時間假設在一個星期）內進行了大量更改，那麼每周的完整備份大小几乎會與在下一個完整備份前進行的差異備份的大小相同。這也解釋了您看到的現象。

另外，差異備份確實提供了一種在災難恢複的情況下縮短還原時間的方法。如果您採用的備份策略是每周進行一次完整備份，每小時進行一次記錄備份，那麼您必須執行下列操作才能最迅速地實現還原：

運行尾記錄備份自最近的記錄備份後產生的所有日誌）。

還原最近的完整Database Backup。

按順序還原自最近的完整Database Backup後的所有記錄備份。

還原尾記錄備份。

可能需要還原大量記錄備份，尤其是在災難剛好發生在進行下次完整備份之前。最糟的情況是需要還原 24 + 24 + 24 + 24 + 24 + 24 + 23 個記錄備份！）在此策略中每天添加差異備份，還原的順序會變成這樣：

運行尾記錄備份自最近的記錄備份後產生的所有日誌）。

還原最近的完整Database Backup。

還原最近的差異備份。

按順序還原自最近的差異備份後的所有記錄備份。

還原尾記錄備份。

這樣就不必還原大量的記錄備份了，因為還原差異備份與還原差異備份涵蓋期間內的所有記錄備份基本相同。

在每天執行差異備份的情況下，即使是在該周的最後一天，最糟的情況也不過是 23 個記錄備份。差異備份不屬於增量備份，它的一個缺點是它們可能會佔用更多的空間，但與縮短還原時間相比，這是值得的。

問 我有一個兩節點的容錯移轉叢集，每個節點都運行一個 SQL Server 2005 執行個體。我按照通常的要求，將每個執行個體設定為只使用 50% 的可用記憶體。現在我遇到了一些問題，因為兩個執行個體上的工作負載都需要更多的記憶體才能維持相同的效能層級。如果我刪除記憶體限制，或是增加記憶體，我想我會碰到這樣的問題：其中一個執行個體容錯移轉，然後兩個執行個體都只在一個節點上運行。您有什麼建議？

答 我會針對兩節點、雙執行個體的情況來解答這個問題，但下列內容也適用於其他多執行個體設定N-1 容錯移轉叢集，其中有 N 個節點和 N-1 個 SQL Server 執行個體）。

許多人在兩個執行個體上都遇到過高工作負載的情況佔用的伺服器記憶體超過 50%），而沒有考慮到兩個執行個體在發生容錯移轉後最後會在一個節點上運行對工作負載的影響。如果沒有特殊的配置，執行個體之間的記憶體配置很可能會不成比例，結果一個工作負載正常運行，而另一個卻慢得不行。

對於 SQL Server 2000，建議將每個執行個體限制為最多使用 50% 的叢集節點記憶體。這是因為 SQL Server 2000 中的記憶體管理器並不會對記憶體不足做出響應 — 假如 SQL Server 佔用了節點 80% 的記憶體，它並不會降低記憶體使用量量。這表示在容錯移轉的情況下，另一個剛啟動的執行個體只有 20% 的記憶體可用。通過將兩個執行個體限制為最多使用節點 50% 的記憶體，可保證每個容錯移轉執行個體有 50% 的記憶體。不過，這種方法產生的問題是每個執行個體上的工作負載也會限制為使用 50% 的記憶體。

而對於 SQL Server 2005和 SQL Server 2008），記憶體管理器可以響應記憶體不足，因此 50% 的上限不再適用。但是沒有這類限制，如果兩個執行個體都在一個叢集節點上運行，它們可能會爭用記憶體直到產生不成比例的記憶體配置。

答案是將每個執行個體設定為最低記憶體量，這樣一來，它們就不會被迫釋放過多的記憶體。對於兩節點、雙執行個體的情況，最常見的設定是為每個執行個體至少配置 40% 的記憶體。這表示當每個執行個體在不同的節點上運行時，它們可以佔用任意記憶體量。而當發生容錯移轉時，會保證每個執行個體有特定的記憶體量，以保持固定的工作負載效能層級，並留一些記憶體在兩者之間共用。雖然這意味著兩個工作負載的效能在發生容錯移轉時可能會下降在意料之中），但是每個執行個體在不同的叢集節點上啟動並執行大多數時間完全不會受到限制。

原文地址

本文來源：微軟TechNet中文站 

編輯精選】