在大多數(shù)SQL Server的工作環(huán)境中，尤其是在OLTP環(huán)境中，數(shù)據(jù)庫的事務(wù)日志性能出現(xiàn)瓶頸時往往會導(dǎo)致事務(wù)完成需要更多的時間，此時許多人把原因都?xì)w結(jié)于I/O子系統(tǒng)，理由是它不能夠支撐工作負(fù)載產(chǎn)生的的大量的事務(wù)日志，然而實(shí)際情況卻都未必如此。

事務(wù)日志寫等待時間

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

在許多系統(tǒng)你是你會發(fā)現(xiàn)有相當(dāng)比例的多余的日志記錄的產(chǎn)生，如果能夠減少這些不需要的日志記錄，相應(yīng)的也就減少了寫入磁盤的事務(wù)日志的數(shù)量，也相應(yīng)的轉(zhuǎn)化為寫等待時間的減少，因此也就減少了事務(wù)完成的時間。

引起多余日志記錄的產(chǎn)生有兩個主要的原因：

未被使用的nonclustered indexes

索引碎片的增多

未被使用的索引

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

未被 使用的索引有可能是你錯誤的在表上創(chuàng)建了一個索引，或者是按照SQL Server的丟失索引的DMV的建議創(chuàng)建的，或者是按照數(shù)據(jù)庫的優(yōu)化顧問創(chuàng)建的，也有可能是業(yè)務(wù)的改變導(dǎo)致原先使用的索引不再被使用。

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

索引碎片

在許多人看來，索引碎片會導(dǎo)致要求讀取更多的數(shù)據(jù)頁，實(shí)際上索引碎片也會導(dǎo)致多余日志記錄的產(chǎn)生而原因就在于產(chǎn)生碎片的原因。

碎片是由于頁拆分page split這種現(xiàn)象的發(fā)生而導(dǎo)致的，簡單的解釋就是當(dāng)插入記錄而空間不足導(dǎo)致了頁拆分，這種過程是這樣子的：

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

從裝滿數(shù)據(jù)的頁中移出一半的記錄到新頁

新頁鏈接到索引結(jié)構(gòu)中

新的記錄被插入到頁面中

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

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

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

總結(jié)

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

當(dāng)然，這僅僅是影響事務(wù)日志性能的一個方面，只有對事務(wù)日志的機(jī)制有更深入的了解，你才會發(fā)現(xiàn)，和事務(wù)日志性能方面的問題的更多方面。

關(guān)于作者

姜傳華，長期從事數(shù)據(jù)庫的教學(xué)、設(shè)計(jì)、開發(fā)和應(yīng)用管理工作，有著20年以上的IT工作經(jīng)歷，深刻理解關(guān)系數(shù)據(jù)庫原理及SQL Server體系架構(gòu)。同時也活躍于Microsoft的各大論壇網(wǎng)站。

我們一直都在努力堅(jiān)持原創(chuàng).......請不要一聲不吭，就悄悄拿走。

我原創(chuàng)，你原創(chuàng)，我們的內(nèi)容世界才會更加精彩!