SQL Q & A:瓶頸與交易記錄
確定性能瓶頸的問題所在有時會很容易，但並非總是如此。 設定交易記錄也是如此。
Paul S. Randal
存儲瓶頸
Q: 我在設法確定系統性能瓶頸的問題所在。 我相信這是存儲層的問題，因為我看到磁片佇列長度超過 2。 我以前瞭解到，這能夠證明 SQL Server 使得存儲超載。 真的是這樣嗎？ 如果是這樣，我該怎麼辦呢？
A: 很遺憾，您瞭解到的只是一種不可信的傳聞。 SQL Server 被設計為採用非同步 IO，它能夠妥善地將磁片佇列長度增加到 2 以上。 每個發出 IO 的執行緒都可以繼續執行其他任務（如果有），直到 IO 完成。 SQL Server 會通過發出併發的非同步 IO，力圖使 IO 子系統的輸送量最大化。 此外，它還會在掃描大量資料時，執行預讀之類的操作。
實際上，某些操作（如 DBCC CHECKDB）將使 IO 子系統處於滿負荷狀態。 因此，磁片佇列長度達到數百都不奇怪。 有關磁片佇列長度傳聞的更多資訊，請閱讀此博客文章。
這樣，問題就出現了：“如何判斷 IO 子系統 確實 遇到瓶頸了？”在物理磁片效能物件中，有兩個性能計數器。 您應該注意觀察以下資料：
- Avg. Disk sec/Read
- Avg. Disk sec/Write
這些資料顯示了完成 IO 所花的時間（以毫秒計）。 如果這些數值持續高於（或者定期出現較高峰值）正常值（5 毫秒至 12 毫秒之間），那就說明物理磁片是 IO 瓶頸所在。 當然，也可能物理磁片是 SAN LUN，而您無法從 Windows 系統深入挖掘它的存儲能力。
如果您的物理磁片上存在多個 SQL Server 資料和日誌檔，您可能就需要確定是哪些檔產生了 IO 負載。 請利用動態管理視圖 (DMV) sys.dm_io_virtual_file_stats，並對結果執行一些簡單的時間系列分析。
如果 DMV 結果顯示該物理磁片上不存在很高的負載，則可能是存儲管理員在該部分 IO 子系統中放置了其他應用程式的檔。 工作負載可能是由獨佔 IO 頻寬的檔產生的。 在這種情況下，您需要請相關管理員將這些 SQL Server 檔移到專門的 IO 子系統部分。
如果僅僅是子系統上的 SQL Server 檔，並且您能夠找出那些造成過多 IO 的檔，則可以考慮採取以下措施：
- 查看資料庫查詢工作負載，確定是否由於錯誤的索引策略而導致執行過度資料表掃描，或者是否由於過期的統計資料而導致執行不當查詢計畫。
- 將一些檔移到 IO 子系統的其他部分。
- 在伺服器中再添加一些記憶體，以獲得更大的 SQL Server 緩衝集區（記憶體中的資料檔案頁緩存），避免過多的讀 IO。
如果上述措施不起作用，那就說明工作負載確實超出了 IO 子系統的承載量，請將其轉移到承載量更大的 IO 子系統上。 您也可以考慮使用企業級快閃記憶體存儲系統，例如 Fusion-io。
大小問題
Q: 我在規劃幾台新伺服器的存儲需求，在確定事務日誌的大小時遇到了困難。 過去，我嘗試過用事務大小作為估算依據。 但有時會比實際需求量小一半。 請問如何估計出適當的大小？
A: 並沒有簡單的公式，可以計算出最佳事務日誌大小。 遺憾的是，也很難阻止事務日誌增長，除非關閉日誌檔的自動增長選項。 但我們不建議您關閉自動增長選項。
首先是底層 RAID 級別。 涉及到性能與冗余性問題時，各種 RAID 級別的權衡互不相同。 例如，仍能提供一定冗余性的最便宜的 RAID 配置為 RAID-5，但此配置只能用於處理單驅動器故障（除非採用 RAID-6 或配置了熱備用驅動器），並且根據陣列中驅動器的數量，它有時會削弱大量寫入工作負荷的性能。
事務日誌應始終能夠自動增長。 尤其是在發生日誌檔大小監視失敗這種緊急狀況時。 例如，假設您收到 SQL Server 代理警報：Percent Log Used 效能計數器的數值高於 90%，但是通過電子郵件/尋呼機通知的緊急連絡人休病假了。 如果日誌不能增長，當前正在運行的要修改資料庫的所有事務都會停止並回滾。 也就是說會停止處理工作負載。
然而，說沒有簡單的公式有一定的誤導性。 許多操作都會佔用事務日誌的空間。 您可以根據這些操作的大小來估計事務日誌大小需求。 這些操作包括產生日常工作負載的操作，以及一些不常執行的操作，如資料庫維護。 所有這些都需要考慮在內。 具體的操作包括：
- 您的工作負載執行的最大單項插入/更新/刪除事務（無論是影響數百萬表行的單一語句隱式事務，還是執行多項操作的顯式事務）。
- 您的工作負載執行的最大批次處理操作，例如 BULK INSERT。 如果您正在採用 FULL 復原模式，也許能通過採用 BULK_LOGGED 復原模式來減小生成的事務日誌量。 當您採用 BULK_LOGGED 復原模式對某些操作進行最低限度的記錄時，可能會影響災難恢復能力。 請參閱我的博客文章“SQL Server DBA 每日一題：BULK_LOGGED 復原模式”。
- 最大群集索引的索引重建。 您也可以在這種情況下採用 BULK_LOGGED。
對於所有這些操作，您不僅需要考慮事務日誌量，還要考慮為事務日誌管理系統保留一定的空間，以容納適當的交易復原。 如果某項事務生成 100MB 的事務日誌記錄，系統會在事務日誌中保留大約 100MB 的空白空間，以保證能夠中止事務並適當回滾。 阻止資料庫發生不一致是一種安全機制。 因此，即便您認為自己為最大的事務留出了足夠的空間，事務日誌仍然可能會增長。
另一個要考慮的問題是，為什麼事務日誌記錄必須保留在日誌中，其原因何在？ 這可能會導致事務日誌不斷增長。 部分原因可能如下：
- 資料庫採用的是 FULL 或 BULK_LOGGED 復原模式，而不是執行事務日誌備份（或者不定期執行備份）。 您在丟棄日誌記錄之前必須將其備份。
- 有一項執行時間超長的事務。 在這種情況下，就不能丟棄這個長時間運行的事務從啟動以來所生成的全部事務日誌記錄。
- 正在進行資料庫鏡像，某些事務日誌記錄尚未從主體伺服器發送到鏡像伺服器。 在這些記錄發送完畢之前，您不能丟棄它們。
- 正在進行異動複寫（或對等複製），日誌讀取器代理作業尚未處理完所有事務日誌記錄。
如果您發現事務日誌在增長而不確定是什麼原因引起的，請詢問 SQL Server。 發出以下查詢：
SELECT [log_reuse_wait_desc] FROM sys.databases
WHERE [name] = 'dbmaint2008';
GO
查詢的結果就是您不能丟棄某些日誌記錄並重用日誌空間（稱為“清除”或“截斷”日誌）的原因。
正如您所看到的，影響事務日誌大小的因素有很多，如果將 tempdb 資料庫考慮在內，影響因素還會更多。 有關詳細資訊，請參閱我的一篇有關此主題的博客文章“控制適當事務日誌大小的重要性”，以及發表在 TechNet 雜誌 上的文章“瞭解 SQL Server 中的日誌記錄和恢復功能”。
日誌記錄強制性
**Q:**請問為什麼我無法使 SQL Server 操作不記錄到日誌中？ 我瞭解到表截斷操作不會記錄，那為什麼不能設置為所有操作都不記錄，以便 SQL Server 運行得更快？ 如果我不需要考慮災難恢復的問題，可不可以這樣設置？ 我特別希望能夠忽略 tempdb 事務日誌。
A: 您瞭解到的關於 TRUNCATE TABLE 操作的傳聞有誤。 所有資料庫上進行的所有操作都會在一定程度上記錄到日誌中。 只不過諸如表截斷之類的操作進行的是最低限度的記錄。 簡單來說，最低限度記錄操作是指僅記錄資料檔案頁的分配和取消分配資訊， 而不記錄對頁上的表/索引記錄進行的操作。 這會加快操作速度，並意味著生成的事務日誌較少，但仍會記錄一些資訊。
在所有復原模式下，都會對表截斷操作進行最低限度的記錄。 而僅在採用 SIMPLE 或 BULK_LOGGED 復原模式時，才會對其他一些操作（例如索引構建/重建和批量載入）進行最低限度的記錄。
在 SQL Server 中，真正不記錄的只有那些會影響 tempdb 中的版本存儲的操作，而版本存儲用來支援快照隔離和連線索引操作等功能。 不記錄這些操作是因為永遠不需要對版本存儲操作進行回滾，也不需要對 tempdb 資料庫執行崩潰恢復。
至此就看到了問題的關鍵所在。 為什麼必須記錄 SQL Server 操作？ 因為 SQL Server 必須保證在發生故障時能夠回滾操作。 如果沒有對於操作過程的描述（如事務日誌記錄），SQL Server 如何知道怎樣回滾呢？ 只有在進行了記錄的情況下才能完成回滾。
即便您不關心能否進行崩潰恢復，在資料庫空間耗盡或出現損壞的磁區時，或者在查詢耗盡記憶體時，SQL Server 也必須能夠回滾操作。 如果 SQL Server 無法回滾某項操作，資料庫就會不可用，工作負載就會停止處理。
這同樣也適用于 tempdb 資料庫。 基於同樣的原因，儘管 tempdb 中的日誌記錄已經簡化，數量已經減少，但您絕不能將記錄全部刪除。 同時，SQL Server 還必須在每次發生崩潰之後能夠執行崩潰恢復，以確保每個資料庫的一致性。 否則資料庫將不可用。 底線：在 SQL Server 中絕不能不對操作進行日誌記錄，我想這一點絕不會改變。
.jpg)
Paul S. Randal 是 SQLskills.com 的常務董事、Microsoft 區域總監和 SQL Server MVP。 從 1999 年到 2007 年，他一直在 Microsoft 的 SQL Server 存儲引擎團隊工作。 他曾編寫過 DBCC CHECKDB/repair for SQL Server 2005，並在 SQL Server 2008 的開發過程中負責核心存儲引擎部分的工作。 Randal 是災難恢復、高可用性和資料庫維護方面的專家，經常在全球出席一些會議。 您可以訪問他的博客 SQLskills.com/blogs/paul，也可以通過 Twitter (Twitter.com/PaulRandal) 與他聯繫。