在大多數SQL Server的工作環境中，尤其是在OLTP環境中，數據庫的事務日志性能出現瓶頸時往往會導致事務完成需要更多的時間，此時許多人把原因都歸結于I/O子系統，理由是它不能夠支撐工作負載產生的的大量的事務日志，然而實際情況卻都未必如此。

事務日志寫等待時間

對 于事務日志來講，寫操作等待的時間可以使用sys.dm_id_virtual_file_stats和系統中的事件writelog等待進行監視。如果 寫等待時間比你期望的I/O子系統較高，那麼I/O子系統就不能夠支撐，這是一般的假設，但不意味著需要升級你的I/O子系統。

在許多系統你是你會發現有相當比例的多余的日志記錄的產生，如果能夠減少這些不需要的日志記錄，相應的也就減少了寫入磁盤的事務日志的數量，也相應的轉化為寫等待時間的減少，因此也就減少了事務完成的時間。

引起多余日志記錄的產生有兩個主要的原因：

未被使用的nonclustered indexes

索引碎片的增多

未被使用的索引

無 論在任何時候向表中插入記錄時，同時也會在該表上定義的每一個noncluster index插入一條記錄(注意，filetered index有可能會例外 )，這就意味著多余的日志記錄的產生;在表中刪除記錄也是同樣的，在noncluster index相應的記錄也必須被刪除，而更新數據也會同樣的對noncluster index中的記錄進行修改。要保持每一個noncluster index和相關的表之間的正確關系(真實反映)，這些操作是必要的，但是如果noncluster index在查詢計劃中未必使用，但為維護他們所產生的操作和日志記錄也會是多余的費用，隨著noncluster index碎片的增長，就需要定期的對他們進行維護，維護同樣也會產生更多的日志記錄也是完全不需要的。

未被 使用的索引有可能是你錯誤的在表上創建了一個索引，或者是按照SQL Server的丟失索引的DMV的建議創建的，或者是按照數據庫的優化顧問創建的，也有可能是業務的改變導致原先使用的索引不再被使用。

無論如何，這些未被使用的索引都應該被清除以便減少負荷，首先要確定哪些索引是未被使用過的，可以通過sys.dm_db_index_usage_stats這個DMV來查看。

索引碎片

在許多人看來，索引碎片會導致要求讀取更多的數據頁，實際上索引碎片也會導致多余日志記錄的產生而原因就在于產生碎片的原因。

碎片是由于頁拆分page split這種現象的發生而導致的，簡單的解釋就是當插入記錄而空間不足導致了頁拆分，這種過程是這樣子的：

一個新的索引被分配和格式化

從裝滿數據的頁中移出一半的記錄到新頁

新頁鏈接到索引結構中

新的記錄被插入到頁面中

這些所有的操作都會產生日志記錄，你可以想象的到，要遠比你插入一條記錄所產生的日志記錄要多。

減 少額外耗費的***步就是清除未被使用的索引，目的就是杜絕其再產生頁拆分，所以要找出那些被分割成碎片的索引，第二步決定使用哪種碎片整理方法的是分析索 引以確定碎片程度。通過使用系統函數 sys.dm_db_index_physical_stats，您可以檢測特定索引、表或索引視圖的所有索引、數據庫中所有索引或所有數據庫中所有索引 中的碎片。對于已分區索引，sys.dm_db_index_physical_stats 還提供每個分區的碎片信息。SQL Server 2005 中計算碎片的算法比 SQL Server 2000 中的算法更精確。因此，碎片值顯得更高。例如，在 SQL Server 2000 中，如果某表的頁 11 和頁 13 在同一區，而頁 12 不在該區，則不會將該表視為碎片。但若要訪問這兩頁，卻需要兩個物理 I/O 操作，因此在 SQL Server 2005 中，此表被計為碎片。使用索引填充因子重建或重新組織索引，以便在索引中保留部分空的空間為后續插入的記錄使用，這樣就減少了頁拆分現象的發生，因而也就 減少了額外的日志記錄的產生。(請參考另一篇文章：發現那些未被使用的數據庫索引)

當 然，天下沒有免費的午餐，任何對一方有利的東西對另一方可能就會有害。當使用填充因子fillfactors時會降低頁面密度，過低的頁面密度同樣也會帶 來一些性能問題，當然過高會帶來頁拆分，所以這是一個需要權衡的問題，具體要參考你的環境，比如說是OLTP還是OLAP等。

總結

減少事務日志的寫等待時間不總是要升級你的I/O子系統，在數據庫中使用簡單的索引分析，就能顯著的減少大量的事務日志記錄的產生，也就同樣的減少寫等待時間。

當然，這僅僅是影響事務日志性能的一個方面，只有對事務日志的機制有更深入的了解，你才會發現，和事務日志性能方面的問題的更多方面。

關于作者

姜傳華，長期從事數據庫的教學、設計、開發和應用管理工作，有著20年以上的IT工作經歷，深刻理解關系數據庫原理及SQL Server體系架構。同時也活躍于Microsoft的各大論壇網站。

我們一直都在努力堅持原創.......請不要一聲不吭，就悄悄拿走。

我原創，你原創，我們的內容世界才會更加精彩!