SQLServer2005索引碎片分析和解決方法

1、SQLServer2005 索引碎片分析和解決方法毫無疑問，給表添加索引是有好處的，你要做的大部分工作就是維護索引，在數(shù)據(jù)更改期間索引可能產(chǎn)生碎片，所以一些維護是必要的。碎片可能是你查詢產(chǎn)生性能問題的來源。怎樣確定索引是否有碎片？SQLServer 提供了一個數(shù)據(jù)庫命令：DBCCSHOWCONTIG,來確定一個指定的表或索引是否有碎片。下面舉一個例子：Xt_exam表執(zhí)行 DBCCSHOWCONTIG，結果如下：-掃描頁數(shù):20229- 掃描擴展盤區(qū)數(shù):2543- 擴展盤區(qū)開關數(shù):15328- 每個擴展盤區(qū)上的平均頁數(shù):8.0- 掃描密度最佳值：實際值：16.50%2529:15329【如果小

2、于 100,則存在碎片。16.50%說明有很多碎片】- 邏輯掃描碎片:46.23%【如果為 0 是最好）】- 擴展盤區(qū)掃描碎片:45.10%- 每頁上的平均可用字節(jié)數(shù):3240.1- 平均頁密度（完整）:59.97%【如果為 100%是最好】以上結果顯示：邏輯掃描碎片和擴展盤區(qū)掃描碎片都非常大，需要對索引碎片進行處理。DBCCDBREINDEX 和 DBCCINDEXDEFRAG 命令常用來整理索引碎片。這里需要注意的是，非常低的碎片級別（小于 5%）不應通過這些命令來解決，因為刪除如此少量的碎片所獲得的收益始終遠低于重新組織或重新生成索引的開銷。1、DBCCDBREINDEXDBCCDBRE

3、INDEX 用于在指定的表上物理地重建一個或多個索引。DBCCDBREINDEX 是離線操作方式。當該操作運行時，涉及到的表就無法被用戶訪問。DBCCDBREINDEX 動態(tài)地重建索引。沒有必要知道參與重建的表結構到底如何，是否用主鍵或者唯一性約束等信息；重建的時候會自動管理的。DBCCDBREINDEX 完全重建索引，就是此過程中將刪除碎片，通過使用指定的或現(xiàn)有的填充因子設置壓縮頁來回收磁盤空間，并在連續(xù)頁中對索引行重新排序（根據(jù)需要分配新頁）。這樣可以減少獲取所請求數(shù)據(jù)所需的頁讀取數(shù)，從而提高磁盤性能。從內(nèi)部運行看,DBCCDBREINDEX和手工用 T-SQL 語句來運行刪除然后重新創(chuàng)建

4、索引十分相似。下面兩點是 DBCCDBREINDEX 比 DBCCINDEXDEFRAG 優(yōu)越的地方：DBCCDBREINDEX 在重建索引過程中，自動重建統(tǒng)計；這將顯著提高工作性能。DBCCDBREINDEX 可以運行在多處理器環(huán)境下，利用多處理器的優(yōu)勢，當重建較大和碎片厲害的索引時速度可以十分快。DBCCDBREINDEX 的所有工作是一個單一的，原子事務。必須完成創(chuàng)建新的索引并替換舊索引，然后舊索引頁被釋放。完成重建需要數(shù)據(jù)文件中有足夠的空余空間。如果空余空間不夠,DBCCDBREINDEX 要么無法重建索引，要么會產(chǎn)生大于 0 的邏輯碎片。所需空余空間視情況而定，取決于事務中要創(chuàng)建的索

5、引數(shù)目。2 、DBCCINDEXDEFRAGDBCCINDEXDEFRAG 用于對指定的索引進行重建。和 DBCCDBREINDEX 類似,也不需顧及表的基礎結構;不過,DBCCINDEXDEFRAG 無法用一個語句對所有的索引進行重建。對于每個希望進行碎片整理的索弓 I,者 B 必須運行一次 DBCCINDEXDEFRAG。無論是 DBCCDBREINDEX 還是 DBCCINDEXDEFRAG，都可以有效地整理索引碎片，并將頁密度恢復到初始填充因子規(guī)定的頁密度附近。基于這些結果，下面需要決定什么時候應用哪種整理方式。如果允 許有一段時間 進行離線索 引重建，DBCCDBREINDEX 一般

6、來 說比 DBCCINDEXDEFRAG 要快。DBCCDBREINDEX 可以充分利用多處理器系統(tǒng)的平行性能。DBCCINDEXDEFRAG 用于對生產(chǎn)環(huán)境干擾不大，對工作性能影響不大的場合。測試顯示，即使同時幾個 DBCCINDEXDEFRAG 并行工作，對性能下降的影響也從來不會超出 10%。但是，這也同樣使得 DBCCINDEXDEFRAG 針對較大的索引整理時，需要很長的時間才能完成。而且，工作時間的長短還依賴于當時在服務器上運行的訪問工作。3 結論對于不同的工作類型，索引碎片整理具有十分不同的影響。某些應用可以從碎片整理中獲取很大的性能提升。理解應用特征，系統(tǒng)性能和 SQLServ

7、er 提供的碎片統(tǒng)計信息，是正確決定何時進行碎片整理的關鍵。SQLServer 提供一些命令來完成索引碎片整理。而在 SQLServer2005 中，DBCCDBREINDEX 和 DBCCINDEXDEFRAG 已經(jīng)被作為維護計劃中的兩個步驟： 重新生成索引和重新組織索引， 方便了數(shù)據(jù)庫管理的數(shù)據(jù)庫維護工作。本文可以幫助我們來決定何時以及如何整理索引碎片，從而使性能得到最大的改善。掃描頁數(shù)表或系引的貝數(shù)。掃描擴展盤區(qū)數(shù)表或索引中的獷展盤區(qū)數(shù)。擴展盤區(qū)開關數(shù)遍歷索引或表的貝時，C C 電句從一午力展盤區(qū)移動到其它擴展盤區(qū)的次數(shù)。平均擴展盤區(qū)上的平均頁數(shù)頁鏈中每個擴展盤區(qū)的頁數(shù)。掃描密度最佳值：

8、實際值最佳值是指在一切都連續(xù)地鏈接的情況卜，擴展盤區(qū)更改的理想數(shù)目。實際值是指擴展盤區(qū)更改的實際次數(shù)。如果一切都連續(xù)，則掃描密度數(shù)為 100;100;如果小于 100,100,則存在碎片。掃描密度為百分比值。邏輯掃描碎片對索引的葉級貞掃描所返回的無序貞的百分比。 該數(shù)與堆集和文本索引尤夫。 無序貝是指在 IAMIAM 中所指示的下一直不 1 1 司十由葉級頁中的下一頁指針所指向的頁。擴展盤區(qū)掃描碎片無序擴展盤區(qū)在掃描索引葉級頁中所占的百分比。 該數(shù)與堆集無關。無序擴展盤區(qū)是指：含有索引的當前頁的擴展盤區(qū)不是物理上的含有索引的前一頁的擴展盤區(qū)后的下一個擴展盤區(qū)。平均每頁上的平均可用字節(jié)數(shù)所掃描的

9、頁上的平均可用字節(jié)數(shù)。數(shù)字越大，頁的填滿程度越低。數(shù)字越小越好。該數(shù)還受行大小影響：行大小越人，數(shù)字就越人。平均頁密度（完整）平均頁密度（為百分比）。該俏考慮行大小，所以它是頁的填滿程度的更準確表示。百分比越大越好。DBCCSHOWCONTIG 正在掃描MSmergetombstone表.表：MSmerge_tombstone2132202646ID:1ID:6已執(zhí)行 TABLE 級別的掃描。- 掃描頁數(shù):1061343- 掃描擴展盤區(qū)數(shù):139332- 擴展盤區(qū)開關數(shù):1060915- 每個擴展盤區(qū)上的平均頁數(shù)：7.6- 掃描密度最佳值：實際值 I:12.51%132668:10609161

10、- 邏輯掃描碎片：50.28%- 擴展盤區(qū)掃描碎片:51.61%- 每頁上的平均可用字節(jié)數(shù):7640.1- 平均頁密度（完整）：5.61%DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯誤信息，請與系統(tǒng)管理員聯(lián)系。DBCCSHOWCONTIG 正在掃描MSmergecontents表.表：MSmerge_contents（719055）;索引 ID:1,數(shù)據(jù)庫 ID:6已執(zhí)行 TABLE 級別的掃描。- 掃描頁數(shù):511344- 掃描擴展盤區(qū)數(shù):79309- 擴展盤區(qū)開關數(shù):511113- 每個擴展盤區(qū)上的平均頁數(shù):6.4- 掃描密度最佳值：實際值 I:12.51%63918:5111141- 邏

11、輯掃描碎片:50.02%- 擴展盤區(qū)掃描碎片:64.25%- 每頁上的平均可用字節(jié)數(shù):5880.7- 平均頁密度（完整）：27.35%DBCCDBCC 輸出了錯誤信息，請與系統(tǒng)管理員聯(lián)系。DBCCSHOWCONTIG 正在掃苗MSmergegenhistory表.表：MSmerge_genhistory（16719112）;索引 ID:1,數(shù)據(jù)庫 ID:6已執(zhí)行 TABLE 級別的掃描。- 掃描頁數(shù)：74- 掃描擴展盤區(qū)數(shù):15- 擴展盤區(qū)開關數(shù):20- 每個擴展盤區(qū)上的平均頁數(shù):4.9- 掃描密度最佳侑：實際侑:47.62%1。21- 邏輯掃描碎片：9.46%- 擴展盤區(qū)掃描碎片:93.33%- 每頁上的平均可用字節(jié)數(shù):1900.3- 平均頁密度（完整）：76.52%DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯誤信息，請與系統(tǒng)管理員聯(lián)系。DBCCSHOWCONTIG 正在掃描I_KANBAN表.表:衣 KANBAN(286624064);索引 ID:1,數(shù)據(jù)庫 ID:6已執(zhí)行 TABLE 級別的掃描。- 掃描頁數(shù):24763-