SQLSERVER 索引維護
更新時間：2012-03-06 | 發(fā)布人：本站 | 點擊率：337 次
Pages & Extents（頁和擴展盤區(qū)）
SQL Server 2000最基本的數(shù)據(jù)存儲單元是data page，1個8K的存儲空間。在分配存儲空間時，SQL Server 2000并不是每次分配1個page，基本的存儲空間分配單元是8個page的連續(xù)空間，稱為extent。
關(guān)于SQL Server 2000的page、extents和index結(jié)構(gòu)，參考：MSDN - Pages and Extents, MSDN - Table and Index Architecture。
Page Split（頁切分）
SQL Server在Insert/Update時，如果要更新的page已經(jīng)存儲滿，無法容納下新的數(shù)據(jù)，則SQL Server將這個page的一半數(shù)據(jù)切分出來，重新分配一個page存放，然后再進行Insert/Update操作，將以滿的數(shù)據(jù)頁切分成兩個數(shù)據(jù)頁的操作叫做page split。
不管是data page還是index page，都會發(fā)生page split。在Insert操作時，如果page上的free space小于要插入的記錄大小，將進行page split；在Update時，如果table中存在變寬字段，變寬字段的長度變大導(dǎo)致原page上free space不夠，將進行page split。
Index Fragmentation（索引碎片）
SQL Server的index fragmentation有兩種：external fragmentation和internal fragmentation。
External fragmentation:
Index page的邏輯順序不連續(xù)時，叫做external fragmentation。Index建立時，index page的存儲在邏輯上都是連續(xù)的。在進行insert操作時，可能需要在兩個索引之間插入這個新的索引。如果在索引插入位置的index page還有足夠的空間，則會直接在這個index page中插入新的索引值；如果在這個index page上空間已滿或者不夠新的索引值所需空間，則SQL Server會進行page split，將插入位置的index page一部分數(shù)據(jù)移走，以釋放出空間來插入新的索引，被移走的數(shù)據(jù)在其它位置重新分配新的page存放。這樣，隨著insert操作的增加，index page在邏輯上的連續(xù)程度就越來越低。
下圖示例索引剛剛建立好之后邏輯上是連續(xù)時的索引結(jié)構(gòu)：
假如此時需要插入索引值為2的新索引，則插入之后的索引結(jié)構(gòu)如下圖：
插入之后index page結(jié)構(gòu)在邏輯上變得不連續(xù)。
在通過index返回特定記錄，或者返回不用指定排序的記錄集時，external fragmentation不會對查詢性能產(chǎn)生太大影響。當需要返回指定排序的記錄集時，排序過程中需要對邏輯上非連續(xù)的index page進行額外處理，對于大數(shù)據(jù)量的表，如果index page非常多，external fragmentation很嚴重，就需要消耗高昂的查詢成本。另外，external fragmentation對緩存效率產(chǎn)生影響。
External fragmentation使用兩個方面的指標來描述，page的連續(xù)程度和extent的連續(xù)程度。
Internal fragmentation：
Index page中如果存儲空間未達到最大存儲容量，叫做internal fragmentation。不考慮fill factor因素的影響，index建立時，索引結(jié)構(gòu)邏輯上連續(xù)，并且每個index page都存儲滿，被充分利用。Delete操作會造成index page出現(xiàn)空閑；External fragmentation的示例中，insert操作時的page split也造成index page出現(xiàn)空閑。
嚴重的internal fragmentation，造成index page占用比實際所需大得多的存儲空間。查詢中進行index scan時，增加了logical READS、I/O等操作，產(chǎn)生性能問題。
Internal fragmentation使用頁的平均頁的空閑程度/利用程度作為指標。
Fill factor:
如果有設(shè)置或者是SQL Server自動維護了一個fill factor值，則在創(chuàng)建索引時，每一個index page都不會存儲滿，而根據(jù)fill factor值預(yù)留一部分空閑空間。在external fragmentation的示例中，假如第一個index page沒有存儲滿，則在插入索引值為2的新索引時，就不需要將這個index page進行split，從而可以改善insert操作。
Fill factor用于需要頻繁進行insert/update操作的表中，避免大量的page split出現(xiàn)。顯然，fill factor的使用類似于internal fragmentation，但對大量的insert操作以及各種data page的結(jié)構(gòu)帶來極大的改善。對于fill factor，不太方便準確的評估什么樣的值最佳，Microsoft建議讓SQL Server自動維護。不恰當?shù)膄ill factor設(shè)置，同internal fragmentation一樣，影響SQL Server性能。
DBCC SHOWCONFIG
用于顯示數(shù)據(jù)、索引fragmentation信息。
DBCC SHOWCONTIG (TblUserItem,PK_TblUserItem)
顯示表TblUserItem中索引PK_TblUserItem的fragmentation信息。
DBCC SHOWCONTIG (TblUserItem) WITH ALL_INDEXES
顯示表TblUserItem所有索引的fragmentation信息。
DBCC SHOWCONTIG WITH ALL_INDEXES
顯示當前數(shù)據(jù)庫中所有索引的fragmentation信息。
執(zhí)行的結(jié)果示例如下：
DBCC SHOWCONTIG scanning 'TblUserItem' table...
Table: 'TblUserItem' (1077578877); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 56933
- Extents Scanned..............................: 7563
- Extent Switches..............................: 7565
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 94.07% [7117:7566]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.03%
- Avg. Bytes Free per Page.....................: 114.3
- Avg. Page Density (full).....................: 98.59%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Page Scanned:實際掃描的page數(shù)量�？梢詮拿總�數(shù)據(jù)行的大小、總的行數(shù)大致計算出實際所需的data page數(shù)量，如果Page Scanned數(shù)量遠遠超過計算出的實際data page數(shù)量，則internal fragmentation比較嚴重。
Extents Scanned:理想值為將Page Scanned/8圓整為最小整數(shù)。如果Extents Scanned大于理論值，則存在一定程度的external fragmentation。
Extent Switches:理想值為Extents Scanned減1，超過這個值說明存在external fragmentation。
Avg. Pages per Extent:理想值為8，小于8則存在external fragmentation。
Scan Density [Best Count:Actual Count]:這是DBCC SHOWCONTIG返回的最有意義的一個值，為理想的extents數(shù)量比實際的extents數(shù)量，反應(yīng)external fragmentation的重要統(tǒng)計信息之一。理想值為100%，不能低于60%。
Logical Scan Fragmentation:另外一個非常有意義的值，指示page的非連續(xù)程度，反應(yīng)external fragmentation的重要統(tǒng)計信息之一。應(yīng)當在0%-10%之間，不能高于15%。
Extent Scan Fragmentation:指示extents的非連續(xù)程度，理想值為0%。
Avg. Bytes Free per Page:平均每page上的空閑字節(jié)數(shù)。過高的值表明存在internal fragmentation，但是需要將fill factor因素排除。
Avg. Page Density (full):與Avg. Bytes Free per Page對立的一個百分比參數(shù)，較低的值表明存在internal fragmentation。
另外DBCC SHOWCONTIG還有幾個可選參數(shù)可以使用，具體參考Online Help。
Resolving fragmentation issues
1. Drop原來的索引再重建這些索引
這個過程中索引被drop和rebuild，會使這個期間所有的查詢阻塞；對所有的clustered index和non-clustered index使用該方法，可能會導(dǎo)致non-clustered index重建兩次。
優(yōu)點是索引徹底重建，達到最理想的狀況。如果external和internal fragmentation都相當嚴重，應(yīng)當使用該方法。
2. 使用DROP_EXISTING子句
使用DROP_EXISTING子句，可以避免non-clustered index被重建兩次。
3. DBCC DBREINDEX
可以僅指定Table名字，而無須指定索引名稱，該命令自動將Table的所有索引進行重建，這樣比寫多條DROP INDEX和CREATE INDEX語句進行操作要方便。這個命令同時將Table的PRIMARY KEY和UNIQUE約束、STATISTICS重建，無須額外對這些約束和STATISTICS進行操作。
DBCC DBREINDEX能夠比較充分的利用多CPU進行處理，對數(shù)據(jù)量相當大和fragmentation非常嚴重的表操作時會比較快。
該方法在一個事務(wù)中完成操作，在數(shù)據(jù)文件中需要有足夠的free space來滿足將所有的索引及相關(guān)的一些對象進行重建，否則操作可能失敗，或者是重建的不十分徹底，例如重建完后logical fragmentation可能仍大于0。對于數(shù)據(jù)量非常大的表，所需的free space也更多，應(yīng)當特別注意這一點。
4. DBCC INDEXDEFRAG
DBCC INDEXDEFRAG分兩個步驟進行操作，首先對各個index page進行壓縮，釋放出多余的page；然后重組index page的各個根節(jié)點，使得index page的邏輯順序與物理存儲順序一致，即在物理存儲方向上保證邏輯順序是連續(xù)的。
4種方法中，其它三種都必須在數(shù)據(jù)庫offline情況下進行，因為在操作期間會導(dǎo)致使用這些索引的所有查詢阻塞。DBCC INDEXDEFRAG可以在數(shù)據(jù)庫online的情況下執(zhí)行，但是整理不夠徹底。因為一方面，在執(zhí)行期間會忽略被lock的 index page，另一方面它不會新分配page進行重排序，只是在原來已分配的page空間里進行重組。這個命令的目標也就是使index page的邏輯順序與物理存儲順序一致，如果邏輯上相鄰的兩個page或extent的物理存儲之間存在間隔，DBCC INDEXDEFRAG不會采取操作消除這種物理存儲間隔。因此如果index page所占用的空間非常大時，訪問index page空間可能會增加磁頭定位和移動的開銷，從而在一定程度上增加了I/O操作成本。
專家建議：60%<Scan Density<75%、10<Logical fragmentation<15時，使用DBCC INDEXDEFRAG；Scan Density<60%、Logical fragmentation>15時，使用DBCC DBREINDEX。
另外，在table設(shè)計方面，可以考慮以下幾點：
1. 對于Insert/Update操作頻繁的table，選擇一個合適的fill factor。
2. 將變寬字段設(shè)計為等寬字段。例如物料號、訂單號、客戶代碼、供應(yīng)商代碼等。
3. 設(shè)立刪除標記而不是物理刪除數(shù)據(jù)。
例如有些大型系統(tǒng)，一個對象可能會有幾十個字段。通常情況下的做法是用一個table容納所有這些字段，但是出于系統(tǒng)訪問效率方面考慮，可以使用兩個 table來存儲。主表存放關(guān)鍵性、訪問最頻繁的字段屬性，盡量不使用變寬字段；從表存放附加的、描述性的、訪問比較少的字段屬性。這樣雖然是一對一關(guān)聯(lián)的表，但是對于大多數(shù)情況下對于只需要訪問主表屬性的查詢，可以做到極大的提高訪問性能。
渝公網(wǎng)安備 50024202000196號
域名注冊知識
虛擬主機知識
網(wǎng)站建設(shè)知識
網(wǎng)絡(luò)推廣知識
網(wǎng)絡(luò)營銷知識
常見技術(shù)問題
QQ空間
新浪微博
開心網(wǎng)
人人網(wǎng)