通過DBCC整理Sqlserver資料庫表索引片段

昨天檢查了一張效率極慢的表，兩年多沒有維護，邏輯掃描片段高達99.81%，於是開始對這個表進行重點跟蹤和記錄日誌。今天用DBCC SHOWCONTIG WITH TABLERESULTS  命令檢查了一下所有表的概況，然後參照著MSDN對欄位的說明，發現問題比較嚴重。ScanDensity（這是“最佳計數”與“實際計數”的比率。如果所有內容都是連續的，則該值為 100；如果該值小於 100，則存在一些片段）有的甚至在16.6666666666667，其他掃描密度等指標也特別不理想。

• 檢查：

隨便貼出一個表的掃描結果：

--------------------------------------------------------------------------------------------------------------------------

DBCC SHOWCONTIG 正在掃描 'UserInfo' 表...
表: 'UserInfo' (1646628909)；索引 ID: 1，資料庫 ID: 7
已執行 TABLE 層級的掃描。
- 掃描頁數................................: 367
- 掃描區數..............................: 48
- 區切換次數..............................: 355
- 每個區的平均頁數........................: 7.6
- 掃描密度 [最佳計數:實際計數].......: 12.92% [46:356]
- 邏輯掃描片段 ..................: 95.37%
- 區掃描片段 ..................: 47.92%
- 每頁的平均可用位元組數.....................: 2996.8
- 平均頁密度(滿).....................: 62.98%
DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。--------------------------------------------------------------------------------------------------------------------------

解釋如下（ 查看解釋來源點此 ，MSDN解釋太官方，還是用簡單點的話來解釋）：

掃描頁數：如果你知道行的近似尺寸和表或索引裡的行數，那麼你可以估計出索引裡的頁數。看看掃描頁數，如果明顯比你估計的頁數要高，說明存在內部片段。 

掃描擴充盤區數：用掃描頁數除以8,四捨五入到下一個最高值。該值應該和DBCC SHOWCONTIG返回的掃描擴充盤區數一致。如果DBCC SHOWCONTIG返回的數高，說明存在外部片段。片段的嚴重程度依賴於剛才顯示的值比估計值高多少。 

擴充盤區開關數：該數應該等於掃描擴充盤區數減1。高了則說明有外部片段。 

每個擴充盤區上的平均頁數：該數是掃描頁數除以掃描擴充盤區數，一般是8。小於8說明有外部片段。 

掃描密度［最佳值:實際值］：DBCC SHOWCONTIG返回最有用的一個百分比。這是擴充盤區的最佳值和實際值的比率。該百分比應該儘可能靠近100％。低了則說明有外部片段。

邏輯掃描片段：無序頁的百分比。該百分比應該在0％到10％之間，高了則說明有外部片段。 

擴充盤區掃描片段：無序擴充盤區在掃描索引葉級頁中所佔的百分比。該百分比應該是0％，高了則說明有外部片段。 

每頁上的平均可用位元組數：所掃描的頁上的平均可用位元組數。越高說明有內部片段，不過在你用這個數字決定是否有內部片段之前，應該考慮fill factor（填滿因數）。 

平均頁密度（完整）：每頁上的平均可用位元組數的百分比的相反數。低的百分比說明有內部片段。

• 整理資料庫片段索引

看到如此不對勁，果斷去MSDN尋找相關資料，找到了MSDN有對資料庫索引進行磁碟重組的T-SQL，執行了一下，發現效果還不錯。

使用 DBCC SHOWCONTIG 和 DBCC INDEXDEFRAG 對資料庫中的索引進行磁碟重組：（以下樣本將展示一種簡單的方法，對資料庫中片段數量在聲明的閾值之上的所有索引進行磁碟重組。）

/*Perform a 'USE <database name>' to select the database in which to run the script.*/-- Declare variablesSET NOCOUNT ON;DECLARE @tablename varchar(255);DECLARE @execstr   varchar(400);DECLARE @objectid  int;DECLARE @indexid   int;DECLARE @frag      decimal;DECLARE @maxfrag   decimal;-- Decide on the maximum fragmentation to allow for.SELECT @maxfrag = 30.0;-- Declare a cursor.DECLARE tables CURSOR FOR   SELECT TABLE_SCHEMA + '.' + TABLE_NAME   FROM INFORMATION_SCHEMA.TABLES   WHERE TABLE_TYPE = 'BASE TABLE';-- Create the table.CREATE TABLE #fraglist (   ObjectName char(255),   ObjectId int,   IndexName char(255),   IndexId int,   Lvl int,   CountPages int,   CountRows int,   MinRecSize int,   MaxRecSize int,   AvgRecSize int,   ForRecCount int,   Extents int,   ExtentSwitches int,   AvgFreeBytes int,   AvgPageDensity int,   ScanDensity decimal,   BestCount int,   ActualCount int,   LogicalFrag decimal,   ExtentFrag decimal);-- Open the cursor.OPEN tables;-- Loop through all the tables in the database.FETCH NEXT   FROM tables   INTO @tablename;WHILE @@FETCH_STATUS = 0BEGIN;-- Do the showcontig of all indexes of the table   INSERT INTO #fraglist    EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')       WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');   FETCH NEXT      FROM tables      INTO @tablename;END;-- Close and deallocate the cursor.CLOSE tables;DEALLOCATE tables;-- Declare the cursor for the list of indexes to be defragged.DECLARE indexes CURSOR FOR   SELECT ObjectName, ObjectId, IndexId, LogicalFrag   FROM #fraglist   WHERE LogicalFrag >= @maxfrag      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;-- Open the cursor.OPEN indexes;-- Loop through the indexes.FETCH NEXT   FROM indexes   INTO @tablename, @objectid, @indexid, @frag;WHILE @@FETCH_STATUS = 0BEGIN;   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',      ' + RTRIM(@indexid) + ') - fragmentation currently '       + RTRIM(CONVERT(varchar(15),@frag)) + '%';   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',       ' + RTRIM(@indexid) + ')';   EXEC (@execstr);   FETCH NEXT      FROM indexes      INTO @tablename, @objectid, @indexid, @frag;END;-- Close and deallocate the cursor.CLOSE indexes;DEALLOCATE indexes;-- Delete the temporary table.DROP TABLE #fraglist;GO

執行後會返回索引掃描數、移動數、刪除數（Pages Scanned、Pages Moved、Pages Removed）。效果還是很明顯的，然後再把掃描結果進行比對：

-----------------------------------------------------------------------------------------------------------------

DBCC SHOWCONTIG 正在掃描 'UserInfo' 表...
表: 'UserInfo' (1646628909)；索引 ID: 1，資料庫 ID: 7
已執行 TABLE 層級的掃描。
- 掃描頁數................................: 237
- 掃描區數..............................: 31
- 區切換次數..............................: 30
- 每個區的平均頁數........................: 7.6
- 掃描密度 [最佳計數:實際計數].......: 96.77% [30:31]
- 邏輯掃描片段 ..................: 2.95%
- 區掃描片段 ..................: 29.03%
- 每頁的平均可用位元組數.....................: 200.3
- 平均頁密度(滿).....................: 97.52%
DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡

------------------------------------------------------------------------------------------------------------

結果很有對比性，片段大幅降低，每頁的平均可用位元組數大幅降低，掃描密度提高，平均頁密度達到理想中的近飽和數值。看來DBCC的一些命令和MSDN的聯機叢書還是很不錯滴。雖然暫時降低了一些片段指標，但只要有操作肯定也會有片段產生，通過一段的時間跟蹤，才能對整體情況進行主觀的判斷。

相關連結：http://msdn.microsoft.com/zh-cn/library/ms188796.aspx

  http://msdn.microsoft.com/zh-cn/library/ms175008(v=sql.90).aspx