怎么為數(shù)據(jù)庫(kù)事務(wù)日志減肥

這篇文章主要講解了“怎么為數(shù)據(jù)庫(kù)事務(wù)日志減肥”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“怎么為數(shù)據(jù)庫(kù)事務(wù)日志減肥”吧！

在大多數(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ù)日志寫等待時(shí)間

對(duì) 于事務(wù)日志來講，寫操作等待的時(shí)間可以使用sys.dm_id_virtual_file_stats和系統(tǒng)中的事件writelog等待進(jìn)行監(jiān)視。如果 寫等待時(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)的也就減少了寫入磁盤的事務(wù)日志的數(shù)量，也相應(yīng)的轉(zhuǎn)化為寫等待時(shí)間的減少，因此也就減少了事務(wù)完成的時(shí)間。

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

未被使用的nonclustered indexes

索引碎片的增多

未被使用的索引

無 論在任何時(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)化顧問創(chuàng)建的，也有可能是業(yè)務(wù)的改變導(dǎo)致原先使用的索引不再被使用。

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

索引碎片

在許多人看來，索引碎片會(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è)拆分，這種過程是這樣子的：

一個(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定使用哪種碎片整理方法的是分析索 引以確定碎片程度。通過使用系統(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ì)將該表視為碎片。但若要訪問這兩頁(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) 然，天下沒有免費(fèi)的午餐，任何對(duì)一方有利的東西對(duì)另一方可能就會(huì)有害。當(dāng)使用填充因子fillfactors時(shí)會(huì)降低頁(yè)面密度，過低的頁(yè)面密度同樣也會(huì)帶 來一些性能問題，當(dāng)然過高會(huì)帶來頁(yè)拆分，所以這是一個(gè)需要權(quán)衡的問題，具體要參考你的環(huán)境，比如說是OLTP還是OLAP等。

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

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

感謝各位的閱讀，以上就是“怎么為數(shù)據(jù)庫(kù)事務(wù)日志減肥”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)怎么為數(shù)據(jù)庫(kù)事務(wù)日志減肥這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！