在大多數SQL Server的工作環(huán)境中，尤其是在OLTP環(huán)境中，數據庫的事務(wù)日志性能出現瓶頸時(shí)往往會(huì )導致事務(wù)完成需要更多的時(shí)間，此時(shí)許多人把原因都歸結于I/O子系統，理由是它不能夠支撐工作負載產(chǎn)生的的大量的事務(wù)日志，然而實(shí)際情況卻都未必如此。
事務(wù)日志寫(xiě)等待時(shí)間 對 于事務(wù)日志來(lái)講，寫(xiě)操作等待的時(shí)間可以使用sys.dm_id_virtual_file_stats和系統中的事件writelog等待進(jìn)行監視。如果 寫(xiě)等待時(shí)間比你期望的I/O子系統較高，那麼I/O子系統就不能夠支撐，這是一般的假設，但不意味著(zhù)需要升級你的I/O子系統。
在許多系統你是你會(huì )發(fā)現有相當比例的多余的日志記錄的產(chǎn)生，如果能夠減少這些不需要的日志記錄，相應的也就減少了寫(xiě)入磁盤(pán)的事務(wù)日志的數量，也相應的轉化為寫(xiě)等待時(shí)間的減少，因此也就減少了事務(wù)完成的時(shí)間。
引起多余日志記錄的產(chǎn)生有兩個(gè)主要的原因：
未被使用的nonclustered indexes
索引碎片的增多
未被使用的索引
無(wú) 論在任何時(shí)候向表中插入記錄時(shí)，同時(shí)也會(huì )在該表上定義的每一個(gè)noncluster index插入一條記錄(注意，filetered index有可能會(huì )例外 )，這就意味著(zhù)多余的日志記錄的產(chǎn)生;在表中刪除記錄也是同樣的，在noncluster index相應的記錄也必須被刪除，而更新數據也會(huì )同樣的對noncluster index中的記錄進(jìn)行修改。要保持每一個(gè)noncluster index和相關(guān)的表之間的正確關(guān)系(真實(shí)反映)，這些操作是必要的，但是如果noncluster index在查詢(xún)計劃中未必使用，但為維護他們所產(chǎn)生的操作和日志記錄也會(huì )是多余的費用，隨著(zhù)noncluster index碎片的增長(cháng)，就需要定期的對他們進(jìn)行維護，維護同樣也會(huì )產(chǎn)生更多的日志記錄也是完全不需要的。
未被 使用的索引有可能是你錯誤的在表上創(chuàng )建了一個(gè)索引，或者是按照SQL Server的丟失索引的DMV的建議創(chuàng )建的，或者是按照數據庫的優(yōu)化顧問(wèn)創(chuàng )建的，也有可能是業(yè)務(wù)的改變導致原先使用的索引不再被使用。
無(wú)論如何，這些未被使用的索引都應該被清除以便減少負荷，首先要確定哪些索引是未被使用過(guò)的，可以通過(guò)sys.dm_db_index_usage_stats這個(gè)DMV來(lái)查看。
索引碎片 在許多人看來(lái)，索引碎片會(huì )導致要求讀取更多的數據頁(yè)，實(shí)際上索引碎片也會(huì )導致多余日志記錄的產(chǎn)生而原因就在于產(chǎn)生碎片的原因。
碎片是由于頁(yè)拆分page split這種現象的發(fā)生而導致的，簡(jiǎn)單的解釋就是當插入記錄而空間不足導致了頁(yè)拆分，這種過(guò)程是這樣子的：
一個(gè)新的索引被分配和格式化
從裝滿(mǎn)數據的頁(yè)中移出一半的記錄到新頁(yè)
新頁(yè)鏈接到索引結構中
新的記錄被插入到頁(yè)面中
這些所有的操作都會(huì )產(chǎn)生日志記錄，你可以想象的到，要遠比你插入一條記錄所產(chǎn)生的日志記錄要多。
減 少額外耗費的第一步就是清除未被使用的索引，目的就是杜絕其再產(chǎn)生頁(yè)拆分，所以要找出那些被分割成碎片的索引，第二步?jīng)Q定使用哪種碎片整理方法的是分析索 引以確定碎片程度。通過(guò)使用系統函數 sys.dm_db_index_physical_stats，您可以檢測特定索引、表或索引視圖的所有索引、數據庫中所有索引或所有數據庫中所有索引 中的碎片。對于已分區索引，sys.dm_db_index_physical_stats 還提供每個(gè)分區的碎片信息。SQL Server 2005 中計算碎片的算法比 SQL Server 2000 中的算法更精確。因此，碎片值顯得更高。例如，在 SQL Server 2000 中，如果某表的頁(yè) 11 和頁(yè) 13 在同一區，而頁(yè) 12 不在該區，則不會(huì )將該表視為碎片。但若要訪(fǎng)問(wèn)這兩頁(yè)，卻需要兩個(gè)物理 I/O 操作，因此在 SQL Server 2005 中，此表被計為碎片。使用索引填充因子重建或重新組織索引，以便在索引中保留部分空的空間為后續插入的記錄使用，這樣就減少了頁(yè)拆分現象的發(fā)生，因而也就 減少了額外的日志記錄的產(chǎn)生。(請參考另一篇文章：發(fā)現那些未被使用的數據庫索引)
當 然，天下沒(méi)有免費的午餐，任何對一方有利的東西對另一方可能就會(huì )有害。當使用填充因子fillfactors時(shí)會(huì )降低頁(yè)面密度，過(guò)低的頁(yè)面密度同樣也會(huì )帶 來(lái)一些性能問(wèn)題，當然過(guò)高會(huì )帶來(lái)頁(yè)拆分，所以這是一個(gè)需要權衡的問(wèn)題，具體要參考你的環(huán)境，比如說(shuō)是OLTP還是OLAP等。
總結 減少事務(wù)日志的寫(xiě)等待時(shí)間不總是要升級你的I/O子系統，在數據庫中使用簡(jiǎn)單的索引分析，就能顯著(zhù)的減少大量的事務(wù)日志記錄的產(chǎn)生，也就同樣的減少寫(xiě)等待時(shí)間。
當然，這僅僅是影響事務(wù)日志性能的一個(gè)方面，只有對事務(wù)日志的機制有更深入的了解，你才會(huì )發(fā)現，和事務(wù)日志性能方面的問(wèn)題的更多方面。
