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

事務(wù)日志寫(xiě)等待時(shí)間

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

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

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

未被使用的nonclustered indexes

索引碎片的增多

未被使用的索引

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

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

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

索引碎片

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

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

一個(gè)新的索引被分配和格式化

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

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

新的記錄被插入到頁(yè)面中

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

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

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

總結(jié)

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

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

關(guān)于作者

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

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

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