要讓 SQL Server 持續有效執行，其中一個重要方法就是保留記錄備份以及妥善維護索引。
Paul S. Randal
切勿破壞這個結構鏈
Q:我已經為數據庫定義了備份策略。其中也包含事務日誌備份，在這樣的情況下執行災難恢復時幾乎不會丟失資料。 我研究了可能會遇到的一些問題，但多次讀到需要注意不能破壞日誌備份鏈。 您能解釋一下這是什麼意思以及在何種情況下會破壞它嗎？
A:問得好，許多人都忽略了這個問題。 記錄備份鏈（有時簡稱為記錄鏈）指的是一系列不間斷的紀錄檔備份，覆蓋的時間段從最近的資料備份（完整備份或差異備份）到要還原該備份時。 還原序列的示例如下：
- 最近的完整資料庫備份
- 然後是最近的差異資料庫備份
- 最後是所有事務日誌備份
大多數人會保留更多紀錄備份，一旦某個備份被破壞，您必須還原最近的資料備份。 在我去年撰寫的兩篇《TechNet 雜誌》文章“瞭解 SQL Server 備份”和“利用備份進行災難恢復”中，可獲得有關備份和還原的更多資訊。
如果任何必要的日誌備份被損壞或不能按照所選序列還原，則日誌備份鏈會被破壞並且無法還原被破壞的時間之前的備份。 如果只損壞了其中一個日誌備份，您可能會使用 WITH CONTINUE_AFTER_ERROR 選項強制其進行還原。 這會強制還原已損壞的事務日誌記錄，從而導致資料庫損壞。 我對是否強制進行此類還原也拿不定主意。
可能會導致必要的日誌備份不可用的一項操作是 “out of band” 日誌備份，該操作不能確保會保留日誌備份。 例如，您可能通過這種方式向開發人員提供副本。 該日誌備份是日誌備份鏈的一部分，因為它是唯一包含在前一個日誌備份後生成的日誌記錄的日誌備份。
也就是說，除非您使用 WITH COPY_ONLY 選項執行日誌備份，並允許下一個 日誌備份有效地備份同一組日誌記錄才行。 請參見我的博客文章“BACKUP WITH COPY_ONLY”，瞭解有關如何避免損壞備份鏈的詳細資訊。
損壞日誌備份鏈的操作的一個更常見示例是阻止您在日常操作期間執行事務日誌備份。 這些類型的操作包括：
- 切換到 SIMPLE 復原模式，然後返回 FULL 或 BULK_LOGGED
- 使用 BACKUP LOG … WITH NO_LOG 或 TRUNCATE_ONLY 選項將日誌轉儲到 SQL Server 2005 和早期版本中
- 從資料庫快照還原資料庫
您需要在上述任何操作後執行資料備份（完整備份或差異備份）以允許日誌備份繼續。 這稱為“重啟日誌備份鏈”。
最後一件事：與一般認識相反，執行完整備份或差異備份不會 損壞日誌備份鏈，事實上，不會對日誌備份產生任何影響。
群集這些索引
Q: SQL Server 2008 資料庫中的許多表不包含群集索引。 我聽說會因此 "Forwarded Records" 進而造成額外 IO 產生，造成效能問題。 您是否能告訴我如何檢查此問題以及應該採取什麼措施？
A:Heap 是一個不包含群集索引的表格。 它在本質上是無排序的。 不了解 Heap 中的"Forwarded Records" 及其用法的讀者，請參見我的博客文章“轉發和Forwarded Records以及後向指標大小”瞭解詳細資訊。 在查詢處理期間，Heap 中的前推記錄可能會導致額外的隨機 IO 操作，而這會導致性能下降。
檢查您的查詢是否正在處理前推記錄的最簡便方法是查看存取方法效能物件中的每秒前推記錄效能計數器。 然後，對資料庫中的一些表使用帶有 DETAILED 模式的 sys.dm_db_index_physical_stats 動態管理函數，該函數將在輸出的 forwarded_record_count 列中返回每個表的前推記錄數。 有關詳細資訊，請參閱連線叢書中的此主題。
刪除前推記錄的最糟糕的方法是創建群集索引，然後再刪除該索引。 這會導致表中的所有非群集索引自動重新生成兩次，浪費大量資源。 有關詳細資訊，請參閱我的博客文章：“What happens to non-clustered indexes when the table structure is changed??
永久刪除和阻止堆中的前推記錄的最簡便方法是創建群集索引。 在此，我不希望討論為什麼在大多數情況下應當使用群集索引而不是堆，從而陷入“群集索引與堆”的爭論 。 有關此問題的詳細資訊，請參閱我妻子 Kimberly Tripp 的“群集鍵”博客文章系列。 我鼓勵您使用群集索引進行評估。
表記錄大小增加時，如果空間不足，會引起"Forwarded Records"。 因此，阻止"Forwarded Records"的另一個方法是防止更改記錄大小。 例如，這意味著使用可變長度列的預設值。
在 SQL Server 2008 中，有一個新的 ALTER TABLE … REBUILD 語句允許您重新生成Heap。 此語句的工作原理與允許您重新生成索引的 ALTER INDEX … REBUILD 語句的工作原理相同。 Microsoft 添加此語句是為了支援資料壓縮功能，但這裡我們將其用於其他目的。 有關詳細資訊，請參閱連線叢書中的此主題。
索引維護
Q: 我已將索引維護的作業更改為使用線上索引重建，但有時在維護作業運行時仍會出現阻塞問題。 為什麼會這樣？ 我認為連線索引操作不使用鎖定，所以不應該出現任何阻塞才對。 這是正常現象，還是我的操作有問題？
A: 您看到的是正常現象。 在操作開始時有一個必需的共用表鎖定，同時操作初始化（這個過程非常快）。 此過程轉瞬即逝。 此鎖定的排隊方式必須與任何其他鎖定類似，並且它將阻止任何新查詢對該表進行修改，直到您再次同意並釋放此鎖定。
在您完成所有當前正在運行的修改查詢後，才可以獲取此鎖定。 這可能需要相當長一段時間，具體取決於您的工作負荷。 這意味著在連線索引操作開始時可能會出現阻塞。
在操作結束時，必須對鎖定進行架構修改，將該鎖定視為獨佔鎖定以完成修改。 此過程同樣相當快。 然後立即解除獨佔該鎖定。 此鎖定將阻止對表進行任何類型的新查詢（讀取或寫入），直到您同意並釋放此鎖定。
再次重申，在 SQL 完成所有當前正在運行的讀取或寫入查詢後，才可以獲取此鎖定。 這同樣意味著可能出現阻塞。
總而言之，儘管此功能名為線上索引操作，但它的確還需要兩個可能導致阻塞問題的短期鎖定。 與傳統的離線索引操作相比，這種方法的優點在於絕大多數索引操作沒有使用鎖定，因此整體併發程度有所提高。 “SQL Server 2005 中的線上索引操作”白皮書對這些操作的工作原理進行了更為詳細的介紹。
縮短索引維護時間
Q: 我承接了一些系統，在這些系統中，需定期的進行維護索引操作，執行時間很長而且也會造成大量 IO，但因為索引不能是零碎的，所以我沒有重新生成任何索引。 我希望減少工作負荷，因為效能沒有得到任何改進。 您能為我建議一個有説明的策略嗎？
A:這個問題普遍存在。 原因在於維護索引操作決定要重新產生或重新組織哪些索引的方式。
大多數人針對資料庫中的所有索引運行 sys.dm_db_index_physical_stats 動態管理函數（以前提到過），然後選擇是重新產生、重新組織還是不執行任何操作。 他們使用輸出中的 WHERE 子句根據 avg_fragmentation_in_percent、page_count 和 avg_page_space_used_in_percent 值做出決定。
問題是索引碎片不像其他統計資訊一樣存儲在記憶體中。 此函數必須讀取並處理各個索引才能確定其零碎程度。 如果資料庫中的大多數索引一成不變或緩慢變化（就碎片來說），將不會重新生成或重新組織這些索引。 每次執行維護索引操作時檢查它們的碎片根本就是在浪費時間。
大多數動態管理視圖支援 “predicate push-down”，即只處理與 WHERE 子句中的謂詞匹配的資料。 但是，sys.dm_db_index_physical_stats 是一個函數而不是Views，因此它無法做到這一點。 這就意味著您必須手動篩選函數，並要求函數只處理您知道可能成為碎片且可能需要重新生成或重新組織的那些索引。
我建議監視幾周內出現的碎片。 這樣您就可以瞭解哪些索引需要檢查碎片，而不用檢查所有索引。 有了這些索引的清單後，創建一個包含表名稱、索引名稱和碎片閾值的表以便採取措施。 您可能會發現，一些索引包含較多的碎片才會影響性能，而另一些則不然。 這將是您稍後用來推動索引維護操作的 “driver table”。 系統將迴圈處理該表並列出所有索引，並只對它們運行 sys.dm_db_index_physical_stats 函數。
我已對多個客戶應用了此方法。 在某些情況下，索引維護操作的執行時間會從幾小時縮短到 15 分鐘或更短。 這完全是因為沒有對靜態索引運行此函數。 您還可以進一步跟蹤索引重新生成以及可能自動更改索引的 FILLFACTOR 設置的頻率，希望這可以進一步減少索引維護操作所執行的工作。
有關執行索引維護的各種方法的詳細資訊，請參閱我的博客文章“索引維護的重要性”，有關函數實際操作的詳細說明，另請參閱我的博客文章“深入瞭解 sys.dm_db_index_physical_stats”。
相關文章：
維護記錄檔與索引的技巧
http://caryhsu.blogspot.com/2011/05/blog-post.html
SQL Server 上大型資料表的索引效能調整與維護
http://caryhsu.blogspot.com/2011/08/sql-server.html
效能調整 - 填滿因數對資料庫的影響
http://caryhsu.blogspot.com/2012/02/blog-post.html
索引分析與維護建議
http://caryhsu.blogspot.com/2012/02/blog-post_14.html
參考連結：
關鍵字：SQL Server、Log Files、Index Maintenance
沒有留言:
張貼留言