標籤：style blog http io ar os 使用 sp for
SQLSERVER 索引維護
2012-03-08 00:30:09| 分類： 預設分類 | 標籤：sql sqlserver 索引 |舉報|字型大小 訂閱
Pages & Extents（頁和擴充盤區）
SQL Server 2000最基本的資料存放區單元是data page，1個8K的儲存空間。在分配儲存空間時，SQL Server 2000並不是每次分配1個page，基本的儲存空間配置單位是8個page的連續空間，稱為extent。
關於SQL Server 2000的page、extents和index結構，參考：MSDN - Pages and Extents, MSDN - Table and Index Architecture。
Page Split（頁切分）
SQL Server在Insert/Update時，如果要更新的page已經儲存滿，無法容納下新的資料，則SQL Server將這個page的一半資料切分出來，重新分配一個page存放，然後再進行Insert/Update操作，將以滿的資料頁切分成兩個資料頁的操作叫做page split。
不管是data page還是index page，都會發生page split。在Insert操作時，如果page上的free space小於要插入的記錄大小，將進行page split；在Update時，如果table中存在變寬欄位，變寬欄位的長度變大導致原page上free space不夠，將進行page split。
Index Fragmentation（索引片段）
SQL Server的index fragmentation有兩種：external fragmentation和internal fragmentation。
External fragmentation:
Index page的邏輯順序不連續時，叫做external fragmentation。Index建立時，index page的儲存在邏輯上都是連續的。在進行insert操作時，可能需要在兩個索引之間插入這個新的索引。如果在索引插入位置的index page還有足夠的空間，則會直接在這個index page中插入新的索引值；如果在這個index page上空間已滿或者不夠新的索引值所需空間，則SQL Server會進行page split，將插入位置的index page一部分資料移走，以釋放出空間來插入新的索引，被移走的資料在其它位置重新分配新的page存放。這樣，隨著insert操作的增加，index page在邏輯上的連續程度就越來越低。
樣本索引剛剛建立好之後邏輯上是連續時的索引結構：
假如此時需要插入索引值為2的新索引，則插入之後的索引結構如：
插入之後index page結構在邏輯上變得不連續。
在通過index返回特定記錄，或者返回不用指定排序的記錄集時，external fragmentation不會對查詢效能產生太大影響。當需要返回指定排序的記錄集時，排序過程中需要對邏輯上非連續的index page進行額外處理，對於大資料量的表，如果index page非常多，external fragmentation很嚴重，就需要消耗高昂的查詢成本。另外，external fragmentation對緩衝效率產生影響。
External fragmentation使用兩個方面的指標來描述，page的連續程度和extent的連續程度。
Internal fragmentation：
Index page中如果儲存空間未達到最大儲存容量，叫做internal fragmentation。不考慮fill factor因素的影響，index建立時，索引結構邏輯上連續，並且每個index page都儲存滿，被充分利用。Delete操作會造成index page出現空閑；External fragmentation的樣本中，insert操作時的page split也造成index page出現空閑。
嚴重的internal fragmentation，造成index page佔用比實際所需大得多的儲存空間。查詢中進行index scan時，增加了logical READS、I/O等操作，產生效能問題。
Internal fragmentation使用頁的平均頁的空閑程度/利用程度作為指標。
Fill factor:
如果有設定或者是SQL Server自動維護了一個fill factor值，則在建立索引時，每一個index page都不會儲存滿，而根據fill factor值預留一部分空閑空間。在external fragmentation的樣本中，假如第一個index page沒有儲存滿，則在插入索引值為2的新索引時，就不需要將這個index page進行split，從而可以改善insert操作。
Fill factor用於需要頻繁進行insert/update操作的表中，避免大量的page split出現。顯然，fill factor的使用類似於internal fragmentation，但對大量的insert操作以及各種data page的結構帶來極大的改善。對於fill factor，不太方便準確的評估什麼樣的值最佳，Microsoft建議讓SQL Server自動維護。不恰當的fill factor設定，同internal fragmentation一樣，影響SQL Server效能。
DBCC SHOWCONFIG
用於顯示資料、索引fragmentation資訊。
DBCC SHOWCONTIG (TblUserItem,PK_TblUserItem)
顯示表TblUserItem中索引PK_TblUserItem的fragmentation資訊。
DBCC SHOWCONTIG (TblUserItem) WITH ALL_INDEXES
顯示表TblUserItem所有索引的fragmentation資訊。
DBCC SHOWCONTIG WITH ALL_INDEXES
顯示當前資料庫中所有索引的fragmentation資訊。
執行的結果樣本如下：
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數量。可以從每個資料行的大小、總的行數大致計算出實際所需的data page數量，如果Page Scanned數量遠遠超過計算出的實際data page數量，則internal fragmentation比較嚴重。
Extents Scanned:理想值為將Page Scanned/8圓整為最小整數。如果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數量比實際的extents數量，反應external fragmentation的重要統計資訊之一。理想值為100%，不能低於60%。
Logical Scan Fragmentation:另外一個非常有意義的值，指示page的非連續程度，反應external fragmentation的重要統計資訊之一。應當在0%-10%之間，不能高於15%。
Extent Scan Fragmentation:指示extents的非連續程度，理想值為0%。
Avg. Bytes Free per Page:平均每page上的空閑位元組數。過高的值表明存在internal fragmentation，但是需要將fill factor因素排除。
Avg. Page Density (full):與Avg. Bytes Free per Page對立的一個百分比參數，較低的值表明存在internal fragmentation。
另外DBCC SHOWCONTIG還有幾個選擇性參數可以使用，具體參考Online Help。
Resolving fragmentation issues
1. Drop原來的索引再重建這些索引
這個過程中索引被drop和rebuild，會使這個期間所有的查詢阻塞；對所有的clustered index和non-clustered index使用該方法，可能會導致non-clustered index重建兩次。
優點是索引徹底重建，達到最理想的狀況。如果external和internal fragmentation都相當嚴重，應當使用該方法。
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進行處理，對資料量相當大和fragmentation非常嚴重的表操作時會比較快。
該方法在一個事務中完成操作，在資料檔案中需要有足夠的free space來滿足將所有的索引及相關的一些對象進行重建，否則操作可能失敗，或者是重建的不十分徹底，例如重建完後logical fragmentation可能仍大於0。對於資料量非常大的表，所需的free space也更多，應當特別注意這一點。
4. DBCC INDEXDEFRAG
DBCC INDEXDEFRAG分兩個步驟進行操作，首先對各個index page進行壓縮，釋放出多餘的page；然後重組index page的各個根節點，使得index page的邏輯順序與實體儲存體順序一致，即在實體儲存體方向上保證邏輯順序是連續的。
4種方法中，其它三種都必須在資料庫offline情況下進行，因為在操作期間會導致使用這些索引的所有查詢阻塞。DBCC INDEXDEFRAG可以在資料庫online的情況下執行，但是整理不夠徹底。因為一方面，在執行期間會忽略被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設計方面，可以考慮以下幾點：
1. 對於Insert/Update操作頻繁的table，選擇一個合適的fill factor。
2. 將變寬欄位設計為等寬欄位。例如物料號、訂單號、客戶代碼、供應商代碼等。
3. 設立刪除標記而不是物理刪除資料。
例如有些大型系統，一個對象可能會有幾十個欄位。通常情況下的做法是用一個table容納所有這些欄位，但是出於系統訪問效率方面考慮，可以使用兩個 table來儲存。主表存放關鍵性、訪問最頻繁的欄位屬性，盡量不使用變寬欄位；從表存放附加的、描述性的、訪問比較少的欄位屬性。這樣雖然是一對一關聯的表，但是對於大多數情況下對於只需要訪問主表屬性的查詢，可以做到極大的提高訪問效能。
Reference:Randy Dyess - SQL Server Index Fragmentation and Its Resolution。
SQLSERVER 索引維護
