如何避免SHRINKDATABASE & SHRINKFILE 產生索引碎片（轉載）

weixin_33816946發表於2018-10-09
Database索引
1. TRUNCATEONLY引數的使用
我們在建立的Job中通常使用如下的語法
DBCC SHRINKDATABASE (N'DB', 10,TruncateOnly)
其中TruncateOnly的用處是把：“將檔案末尾的所有可用空間釋放給作業系統，但不在檔案內部執行任何頁移動”，
所以此時前面指定的“10”（target_percent），將不起作用，由於刪除資料等作業導致的大量的空閒的資料頁，將不會被回收，上面的語句的作用，只能把檔案結尾部分有限的空閒資料頁回收。也就不能完全達到資料庫收縮的作用了。
 
建議的做法如下：
先通過DBCC SHRINKDATABASE (N'DB', 10) WITH NO_INFOMSGS 對資料檔案中的資料頁進行整理，
然後再通過dbcc shrinkfile(DB_Data, truncateonly)
          dbcc shrinkfile(DB_Log, truncateonly)
分別對資料庫資料檔案和Log檔案收縮，這樣可以真正達到資料庫收縮的目的
 
2. Index的重建
通常我們對資料庫進行收縮後會增加Index的碎片的產生，同時也就降低了資料的查詢速度。
 
我們可以通過下面的Script，檢視Table的索引的狀況
DBCC SHOWCONTIG(ipprhm) WITH ALL_INDEXES
 
DBCC SHOWCONTIG scanning 'ipprhm' table...
Table: 'ipprhm' (1009438670); index ID: 1, database ID: 15
TABLE level scan performed.
- Pages Scanned................................: 13746
- Extents Scanned..............................: 1732
- Extent Switches..............................: 3179
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 54.06% [1719:3180]
- Logical Scan Fragmentation ..................: 67.92%
- Extent Scan Fragmentation ...................: 57.16%
- Avg. Bytes Free per Page.....................: 2177.7
- Avg. Page Density (full).....................: 73.09%
DBCC SHOWCONTIG scanning 'ipprhm' table...
Table: 'ipprhm' (1009438670); index ID: 15, database ID: 15
LEAF level scan performed.
- Pages Scanned................................: 79
- Extents Scanned..............................: 18
- Extent Switches..............................: 21
- Avg. Pages per Extent........................: 4.4
- Scan Density [Best Count:Actual Count].......: 45.45% [10:22]
- Logical Scan Fragmentation ..................: 7.59%
- Extent Scan Fragmentation ...................: 83.33%
- Avg. Bytes Free per Page.....................: 1397.4
- Avg. Page Density (full).....................: 82.73%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
從上面的結果可以看出，掃描密度比較低和邏輯掃描碎片比較高，所以需要進行Index碎片整理。
整理Index的方式有兩種：
DBCC INDEXDEFRAG(DB, TABLE, INDEX) WITH NO_INFOMSGS 和
DBCC DBREINDEX(TABLE, '', 0)
 
INDEXDEFRAG是線上重整Index，不會對Table鎖定，但是由於INDEXDEFRAG是對Index的重組，所以Index的資料頁不一定是連續的。
DBREINDEX會對Table進行鎖定，重建索引。
 

相關文章