SQL Q & 答：交易的故事

發行項
08/18/2016

交易記錄檔對於備份作業來說極其重要，但是您必須確保它們已設定妥當，並且在該清除的時候加以清除。

保羅 · Randal

清除這些日誌

**問：**我讀過在完整復原模式下，事務日誌清除只時出現的事務日誌備份。我雖然看到奇怪的行為。有時日誌備份不清除日誌。它似乎這樣的資料備份。這是怎麼回事？

**答：**你是正確：在完整恢復和大容量日誌記錄恢復模型中，僅可以清除事務日誌（日誌部分標記為可重複使用）事務日誌備份時。在簡單復原模式下，它是清除事務日誌的檢查點。日誌記錄和恢復一般的更多背景，請參閱我的 TechNet 文章，"瞭解日誌記錄和在 SQL Server 中的恢復。"

然而，有一個問題。雖然事務日誌清除操作將事務日誌備份的結尾，則實際上將清除任何部分事務日誌沒有保證。已備份事務日誌的一部分的事實並不足以讓它被清除。 SQL Server 必須不需要事務日誌的那的部分用於任何其它目的在任何其它時間。

SQL Server 還可能需要訪問部分事務日誌，因為運行資料的備份（如完整資料庫備份）。資料的備份必須包含事務日誌的一部分。它將至少需要生成是複製資料時從資料庫的事務日誌資料。它甚至可能需要更多。

這意味著在運行時資料的備份，日誌清除不會發生。這是真實的即使發生併發事務日誌備份（並行資料和日誌備份以來可能 SQL Server 2005）。

在這個特殊的情況下，SQL Server"記住"那裡的備份部分已事務日誌備份，並記得哪部分事務日誌備份。並行資料的備份完成後，SQL Server 可以清除事務日誌部分已備份的事務日誌備份（只要東西不需要的事務日誌，如未提交，長時間運行的事務）。

這種行為稱為延遲的日誌截斷。這是您遇到的行為。它看起來像資料的備份清除事務日誌，但它真的是工件的併發事務日誌備份。

鏡像鏡像

**問：**我們正在執行資料庫鏡像防止資料丟失的資料庫之一。你能告訴我我們應監測以確保我們的資料庫鏡像是否正常工作嗎？

**答：**當執行資料庫鏡像，絕對必須監視發送佇列和重做佇列的大小。這些直接涉及資料丟失和停機時間，分別。

發送佇列跟蹤多少從主體資料庫的事務日誌已尚未發送到鏡像伺服器。事務日誌的這一部分描述更改將會丟失，如果發生災難，呈現不可用的主體資料庫。

很多人認為是否您將配置資料庫鏡像的高安全或高可用性（也稱為同步鏡像），發送佇列將始終為零。直到事務的所有事務日誌已都發送到鏡像伺服器，不能提交在主體資料庫上的事務。但是，這不是真的。它是可能的在某些情況下，為主體伺服器和鏡像伺服器失去聯繫對方與主體資料庫保持線上。在這種情況下，發送佇列將開始增長。這會增加資料丟失的風險。同步鏡像狀態時，只會保證零資料丟失。

重做佇列跟蹤多少事務日誌鏡像伺服器上已不還被重播在鏡像資料庫上。它是一種常見的誤解當主體伺服器將事務日誌發送到鏡像伺服器，它也立即重播在鏡像資料庫上。這也是不真實。所需要的是持久存儲寫入事務日誌鏡像伺服器上。

這意味著取決於鏡像伺服器硬體（包括其 I/O 子系統、任何併發工作負荷和其他對性能產生影響的因素），可以在鏡像資料庫還沒還重放相當大的事務日誌佇列。

鏡像容錯移轉時，鏡像資料庫不會到連線狀態，直到被重播事務日誌。這是成比例的 un-replayed 的事務日誌量。它還表示在應用程式和使用者將體驗鏡像容錯移轉發生時的停機時間。

您還應小心重做佇列的大小，如果你要從鏡像資料庫的資料庫快照用於報告的目的。當您創建資料庫快照時，資料庫恢復必須處理的未完成的事務日誌，因此資料庫快照是基礎資料庫的事務一致視圖。越大的重做佇列、更長的時間，它將以創建資料庫快照。它也會影響性能的鏡像伺服器，可以使增長的重做佇列。

有幾個要監視的情況下，例如，主體伺服器和鏡像伺服器之間的網路延遲其他度量。這將轉化的每個事務的執行同步鏡像的開銷。使用效能監視器 (sys.dm_os_performance_counters 動態管理視圖 [DMV]）來跟蹤這些度量。您也可以使用資料庫鏡像監視器工具中描述和內置管理工作室SQL Server 連線叢書。該工具允許您輕鬆地創建基於佇列大小閾值警報。

性能提升

**問：**我應該有多個資料檔案以説明進行性能，和我明白為什麼，我已經被告知。現在我知道我也應該有多個事務日誌檔，因此，SQL Server 可以做更高效 I/O 操作的事務日誌。這樣對嗎？

**答：**不，這不是正確的。不幸的是，我聽到的這項建議每隔一陣子。

多個資料檔案可以説明 I/O 子系統爭用。在某些情況下（通常與 tempdb)，他們還可以説明它們在記憶體中的資料庫分配結構的爭用。有各種各樣的多少的資料檔案，以創建的指引。

有多個事務日誌檔的建議推斷出了相同的建議，對於資料檔案，但不正確。多個事務日誌檔不提供任何增益的性能、可用性、可擴展性或任何其他可衡量的指標。

事務日誌是，並且必須是順序的性質，使 SQL Server 不會執行並行 I/o 事務日誌，如果有多個事務日誌檔。第一個檔會等使用的全貌，然後第二個檔，然後第三次。這會發生直到事務日誌環繞和重新開始，在第一個事務日誌檔。所以這些額外的日誌檔提供沒有收穫。

有可能需要第二個事務日誌檔的情況。如果第一個事務日誌檔已滿，事務日誌不能清除（請參閱附加說明的第一個問題的答案）。第一個日誌檔不能會增長以容納更多的事務日誌記錄。在這種情況下，添加第二個事務日誌檔將暫時允許繼續直到第一個事務日誌檔可以清除資料庫修改。

減少 I/O，提高性能

**問：**我們一直在調查使用固態硬碟（策略性污水排放計畫) 儘量減少一些我們 I/O 性能問題，但我們困惑的穿上他們的資料庫。你能給我們一些指導如何最好地使用策略性污水排放計畫？

**答：**這是一個有趣的問題。有各種各樣的"權威性"的答案，您將看到對互聯網説明論壇，如，"總是放 tempdb 您 SSD"或者"始終把事務日誌放你 SSD。"這些都不是適當的。

有一些因素來牢記一點，當考慮如何最有效地使用 SQL Server 的策略性污水排放計畫：

策略性污水排放計畫很貴，所以你要確保你要從他們最佳的投資回報率。
策略性污水排放計畫提供大多數的性能增益，對於隨機 I/O 工作負載，不連續的 I/O 工作負荷。
重載的 I/O 子系統的任何部分，SSD 將提供的性能提升 — — 不管的 I/O 模式 — — 由於對其性質的大大減少讀寫延遲。
直連策略性污水排放計畫應提供比任何一種通信織物通過訪問更深刻的性能提升。
事務日誌是按順序寫入，大多是按順序讀取（和一些隨機讀取，如果有很多潛在的交易復原）。
Tempdb，可能很輕 SQL Server 上的使用。即使他們習慣溫和了，他們不可能會遇到大量資料檔案寫入活動。

當您考慮這些因素時，你意識到 tempdb 或事務日誌放你策略性污水排放計畫可能不會使用它們的最好方法。確定是最大的性能瓶頸，為您的工作負荷的 I/O 子系統的某些部分。這些可能是揮發性的連線交易處理 (OLTP) 資料庫的資料檔案或重插入工作量與資料庫的事務日誌 — — 或他們很可能是 tempdb。這些候選人放在你的策略性污水排放計畫，而不是只挑選部分 SQL Server 存儲而不執行任何調查。

有關策略性污水排放計畫的壞建議另一件是您可以停止使用策略性污水排放計畫存儲資料檔案時關注索引碎片。這絕對不是這樣。它是真正會緩解未來從碎片較有效讀取的影響程度上是因為大大降低讀取延遲時間，但仍有更多的 I/o，不必要的成本。您可以減少，解決碎片。

大多數人不考慮是碎片不只是讀取未來。副作用的碎片（操作稱為"頁拆分"）的原因之一是未使用的空間資料檔案頁面上的百分比可以大大增加 — — 從 40%到 50%（這稱為"頁密度"）。

如果大部分資料庫索引的碎片，然後不定期的索引維護資料檔案頁將含有大量的空白空間。除了減少 I/o 和記憶體使用情況，這也意味著效率使用昂貴的策略性污水排放計畫存儲空的空間。這不是任何人的書很好的投資回報。

Paul Randal

**保羅 · Randal**是的 SQLskills.com、微軟區域主任和 SQL 伺服器 MVP 的董事總經理。他工作過的 SQL Server 存儲引擎小組微軟從 1999 年到 2007 年。他寫 DBCC CHECKDB/修復了 SQL Server 2005，並在 SQL Server 2008 的開發過程中負責核心存儲引擎。 Randal 災難恢復、高可用性和資料庫維護方面的專家，是經常在世界範圍內的會議簡報者。他在 SQLskills.com/blogs/paul，和你的博客能找到他在 Twitter 上Twitter.com/PaulRandal。