SQL 線上問答：復原損害
探究一下查詢計畫的選擇，何以過度拖緩備份及修復複寫資料庫的程序。
Paul S. Randal
計畫您的查詢
**Q。**SQL Server選擇時要執行我的查詢的查詢計劃，它不會考慮到當前在記憶體中的資料？
**答：**答案很簡單查詢最佳化工具從不認為緩衝區的池內容時選擇的查詢計劃。 查詢最佳化工具會評估各項計畫它窄的可能選擇。 它會查找它可以確定在合理的時間框架內的最佳計畫。 查詢最佳化工具不會總是找出絕對最佳計畫。 查詢最佳化工具不能計畫彙編上花費過多的時間，但它總是選擇一個"足夠好"的計畫。
查詢最佳化工具需要瞭解查詢所涉及的各個表。 為表關係中繼資料中找到此資訊。 關係中繼資料描述表中的列、 索引和約束，以及統計有關的各列中的值分佈 （如果已創建這些統計數字）。 存儲中繼資料描述了如何在表和索引實際存儲在資料檔案中。 查詢最佳化工具不會使用此資訊確定一項計畫。
SQL Server不會跟蹤的表中的哪些部分，其索引的任何時候都在記憶體中。 緩衝集區跟蹤從記憶體中的資料庫的資料檔案頁。 然而，什麼SQL Server中沒有任何一種自動化的聚合。 例如，它不能確定表 X 的兩個索引的 50%是在記憶體中，而只有 5%的表 X 的三個索引是在記憶體中。
查詢最佳化工具假定沒有在記憶體中，因此很有可能選擇的查詢計劃，其中涉及到的物理 I/O 的最低量。 與很多物理 I/O 的計畫既費時又費錢。 考慮具有能滿足一個選取查詢的兩個非叢集索引的表。 第一個索引具有所需的所有列。 第二個索引了所有所需的列，再加上幾個額外的列。
第一個索引將具有較小的索引記錄，因此它會有更多的索引行，每個資料檔案頁。 查詢最佳化工具都將選擇此索引。 訪問所需的索引記錄以滿足查詢需要更少的資料檔案頁讀入記憶體使用物理 I/O，相比使用第二個索引。 第二個有較大的記錄和較少的記錄，每個資料檔案頁。 這理由稱為基於成本的優化，而且它是SQL Server的查詢處理器如何設計。
但是，如果有一大批的第二次、 更廣泛已在記憶體和的第一個索引都不是索引的在記憶體中？ 該查詢將需要物理 I/O 來讀取到記憶體中的選定的索引。 這將是比使用更廣泛的索引已經在記憶體中的慢好多。 在這種情況下，查詢計劃是其實最理想的。 然而，查詢最佳化工具已沒有辦法知道什麼是存儲在記憶體中，這只是一個示例。
即使有考慮到這一點，不過，如果查詢最佳化工具將認識到什麼是在記憶體中，並生成一個計畫，使用效率較低的指數因為它已經是在記憶體中？ 只要這種情況持續下去，該計畫將是最優。 如果更高效的索引讀取到記憶體中的另一個查詢，該查詢將然後有一個次優的計畫。 如何將這項計畫將失效以便可以再次重新編譯？
作為對SQL Server團隊前軟體工程師，我知道工程複雜性保持彙總檢視的表和索引是在記憶體中為了説明查詢計劃選擇和失效的是極具挑戰性。 這將有可能添加的不良性能系統開銷只是偶爾的受益，和它將有可能永遠不會發生。
如果你感興趣的緩衝集區，簽出動態管理檢視 (DMV) sys.dm_os_buffer_descriptors 和已經彙集關於各種查詢緩衝集區條我的 SQLskills 博客。
備份時間長
**Q。**我們使用記錄傳送次要資料庫提供為報告的目的。 每隔一陣子，我們遇到問題將記錄備份應用到次要資料庫發生遠比平常更長。 你有任何想法可能會導致此問題什麼？
**答：**是的我見過這種情況有幾次。 如果您要使用記錄傳送次要資料庫報告，這意味著還原上次要資料庫的記錄備份時，您正在使用與待機選項。 該工程分三個步驟：
- 從備份寫入資料庫日誌檔的所有日誌記錄。
- 執行重做部分的恢復 （確保從已提交的事務的所有操作都都在資料庫中）。
- 執行撤銷部分的恢復 （確保來自未提交的事務的所有操作在資料庫中沒有）。
第 3 步寫入到一個特殊的檔的撤銷操作生成的所有日誌記錄調用恢復檔案。 這意味著資料庫處於唯讀模式。 因此，使用者可以進行訪問，這也是事務上一致。 日誌記錄會被寫到恢復檔案的原因是資料庫的這樣的事務日誌不會以任何方式修改。 這允許您還原後續的記錄備份。
還原過程開始時對次要資料庫，如果恢復檔案存在，另一步就是之前的前三個步驟執行。 這第一步需要將恢復檔案中的所有日誌記錄和撤銷對他們的影響。 這基本上是置於資料庫返回的狀態正是在末尾的第 2 步。 因為如果使用 WITH NORECOVERY，而不與待機狀態已恢復以前的記錄備份此資料庫狀態是相同的。
您遇到的問題是，當被還原的記錄備份包含長期運行的事務記錄備份的結尾之前不會犯下的。 這意味著它要把一部分的還原記錄備份完全撤銷。 此結果在大型恢復檔案中，這本身就可以使還原記錄備份需要較長時間。 如果還正在還原的記錄備份有未提交的長時間運行的事務，然後它是完美的風暴。 第 3 步還將需要很長時間。
這可能發生在主體資料庫正在進行索引維護和記錄備份完成附近的長時間運行的大型的叢集索引的索引重建操作結束時。 對次要資料庫的記錄備份的還原初始長得多比往常來完成，因為在還原過程中的步驟 3。
在主體資料庫上的下一個記錄備份還只是在重新生成索引之前完成。 當它二次恢復時，整個恢復檔案，再次被撤銷。 然後在日誌還原發生，並生成另一個大型恢復檔案若要撤銷第二次未提交的索引重建。
如果次要資料庫需要報告的 24 x 7 訪問，您必須知道這種可能性。 在這種情況下，仔細地增加在主資料庫上的索引維護操作的記錄備份。 這將確保只完整的、 致力於索引重建目前在正在恢復對次要資料庫的記錄備份。
另一種方法是將記錄傳送到資料庫鏡像從移動。 在這裡，日誌記錄不斷被發送從主體到鏡像資料庫。 沒有任何額外的步驟，涉及撤銷日誌操作多次。 有複雜性權衡參與，因為這種方法的缺點是報告將不得不使用資料庫快照。
複製修復
**Q。**每隔一陣子我們結束與損壞的檔。 我們的備份也最終會損壞，所以我們要運行修復操作。 上周，我不得不修復的資料庫之一是複製發佈資料庫。 Microsoft 連線叢書中，它表示必須修復發佈資料庫後重新初始化所有訂閱伺服器。 你能解釋為什麼嗎？
**答：**如果您正在考慮使用 REPAIR_ALLOW_DATA_LOSS 選項的資料庫一致性檢查 (DBCC) CHECKDB (我得說"修復"從這裡），您必須三思如果你要修復的資料庫是一個複製發佈資料庫。 如果可能，使用您的備份，而不是運行修復。
如果您正在使用合併複製，資料操作語言 (DML) 觸發捕獲對發佈資料庫的更改，並將它們轉換為邏輯操作。 如果您正在使用事務性複製，資料庫事務日誌分析捕獲出版物資料庫更改。 然後登錄、 物理操作會轉換為邏輯操作。 在這兩種情況下，然後到複製訂閱資料庫應用的邏輯操作。
兩個機制可以將捕獲的維修業務。 修復操作始終是直接對資料庫結構的物理更改。 這些是需要修復不一致的結構，如資料庫頁，表記錄或兩頁之間的聯繫。 他們不同的物理資料庫更改由於在表上執行插入、 更新或刪除的查詢。
這些操作不能轉化為邏輯操作，您可以應用到訂閱伺服器複製的修復。 可以修復正在執行的直接的結構變化，相當於使用Transact-SQL表示比沒有邏輯操作。 想像一下一次修復操作被迫從唯一的叢集索引 （基本上刪去某些表記錄） 刪除一個資料頁。 不會重新初始化訂閱。 這些記錄將仍然存在於表的複製副本。
如果後繼的插入操作與群集金鑰值對應的修復操作刪除的記錄插入記錄，分發代理程式會失敗時嘗試複製拷貝到應用插入。 修復操作不應該應用到訂閱資料庫，所以嘗試應用到複製的表的插入時，會發生重複的鍵值衝突錯誤。 這意味著如果修復操作更改是複製發佈的一部分的任何表，複製訂閱不再有效。 它將不得不重新進行初始化。
如果修復操作影響任何複製中繼資料的表，整個複製出版物是處於不一致狀態。 必須完全刪除並重新配置複製。 很明顯，這是一個更多創的過程比剛剛重新初始化訂閱。 底線是如果可能，您想避免因修復複製發佈資料庫。
.jpg)
Paul S. Randal 為 SQLskills.com、 微軟區域主任和SQL ServerMVP 的董事總經理。 他在微軟從 1999 年到 2007 年的SQL Server儲存引擎團隊。 他為SQL Server2005年寫的 DBCC CHECKDB/修復並負責核心儲存引擎在SQL Server2008年開發過程。 Randal是災害復原、 高可用性和資料庫維護方面的專家，經常在世界各地的會議簡報者。 他在 SQLskills.com/blogs/paul，和你的博客可以找到他在 Twitter 上 twitter.com/PaulRandal。