SQL Server 2000索引重構方法

一、必要性：

大多數
SQL Server
表需要索引來提高資料的訪問速度，如果沒有索引，
SQL Server
要進行表格掃描讀取表中的每一個記錄才能找到索要的資料。索引可以分為簇索引和非簇索引，簇索引通過重排表中的資料來提高資料的訪問速度，而非簇索引則通過維護表中的資料指標來提高資料的索引。

在資料庫中建立索引時，查詢所使用的索引資訊儲存在索引頁中。連續索引頁由從一個頁到下一個頁的指標連結在一起。當對資料的更改影響到索引時，索引中的資訊可能會在資料庫中分散開來。重建索引可以重新組織索引資料（對於叢集索引還包括表資料
)
的儲存，清除片段。這可通過減少獲得請求資料所需的頁讀取數來提高磁碟效能。

 

二、

 

何時需要重構索引，如何檢測？

 

為了克服資料分塊帶來的負面影響，需要重構表的索引，這是非常費時的，因此只能在需要時進行。可以通過
DBCC SHOWCONTIG
來確定是否需要重構表的索引。

dbcc showcontig
（
’
表名
’
）
;

 

以一個測試表為例，輸出結果；

- Pages Scanned....................................: 197214 - Extents Scanned...............................: 24659 　　 - Extent Switches DBCC...............................: 24658 　　 - Avg. Pages per Exten.....................: 8.0 　　 - Scan DensityBest Coun....................: 99.97%[24652:24659] 　　 - Extent Scan Fragmentation.............................: 15.46% 　　 - Avg. Bytes Free per Page.......................: 374.6 　　 - Avg. Page Density (full)....................: 95.37%

 

通過分析這些結果可以知道該表的索引是否需要重構。下邊描述了每一行的意義描述

Pages Scanned
表或索引中的長頁數

　　
Extents Scanned
表或索引中的長區頁數

　　
Extent Switches DBCC
遍曆頁時從一個地區到另一個地區的次數

　　
Avg. Pages per Extent
相關地區中的頁數

　　
Scan DensityBest Count
是連續連結時的理想區

[Best Count:Actual Count]
域改變數，
Actual Count
是實際地區改變數，
Scan Density
為
100%,
表示沒有分塊。

Logical Scan Fragmentation
掃描索引頁中失序頁的百分比

　　
Extent Scan Fragmentation
不實際相鄰和包含鏈路中所有連結頁的地區數

　　
Avg. Bytes Free per Page
掃描頁面中平均自由位元組數

　　
Avg. Page Density (full)
平均頁密度，表示頁有多滿

從上面命令的執行結果可以看的出來，
Best count
為
3
而
Actual Count
為
5
這表明
orders
表有分塊需要重構表索引。

三、重構索引的方法

      
<1>
重構單個表，

      
DBCC   DBREINDEX  
重建指定資料庫中表的一個或多個索引。
 
   
 
文法
 
  DBCC   DBREINDEX  
          (         [   'database.owner.table_name'          
                          [   ,   index_name  
                                  [   ,   fillfactor   ]  
                          ]    
                  ]    
          )         [   WITH   NO_INFOMSGS   ]  

 

      

文法見
SQL Server
協助。

 

      

比如：重構一個表，執行
DBCC   DBREINDEX
（
’
表名
’
）

 

      

以
northwind
庫的
orders
表為例，

 
dbcc dbreindex('northwind.dbo.orders')

      
 
dbcc showcontig('northwind.dbo.orders'),
顯示結果如下，

DBCC SHOWCONTIG scanning 'Orders' table... 　　 Table: 'Orders' (21575115); index ID: 1 ， database ID: 6 　　 TABLE level scan performed. 　　 - Pages Scanned................................: 22 　　 - Extents Scanned..............................: 3 　　 - Extent Switches..............................: 2 　　 - Avg. Pages per Extent........................: 7.3 　　 - Scan Density [Best Count:Actual Count].......: 100.00% [3:3] 　　 - Logical Scan Fragmentation ..................: 0.00% 　　 - Extent Scan Fragmentation ...................: 33.33% 　　 - Avg. Bytes Free per Page.....................: 869.2 　　 - Avg. Page Density (full).....................: 89.26%

通過結果我們可以看到
Scan Denity
為
100%
表沒有分塊不需要重構表索引了。

 

      
<2>
重構一個庫中的所有表，

use
庫名

sp_msforeachtable 'dbcc DBREINDEX("?")'

sp_msforeachtable
表示對庫中每一個表執行某一條命令，相當於一個迴圈，執行完後，所有的表的索引都被重構；

 

<3>
整體重構
DBCC   CHECKDB
；

DBCC   CHECKDB  
（
'pubs',repair_rebuild
）
  

 

DBCC   CHECKDB  
不僅僅會修複索引，檢查指定資料庫中的所有對象的分配和結構完整性。
 

 

對於資料庫中每個表，
DBCC   CHECKDB  
檢查其：
   

1.
索引和資料頁是否已正確連結。
 

2.
索引是否按照正確的順序排列。
 

3.
各指標是否一致。
 

4.
每頁上的資料是否均合理。
 

5.
頁面位移量是否合理。
     

    

組合命令如下：

<1>
將資料庫置為單一使用者模式；

sp_dboption  

庫名
, single, true

<2>
對整個庫進行重構；

 
DBCC CHECKDB('
庫名
',repair_rebuild) 

 

附註：如果想對單個表進行重構，

DBCC CHECKTABLE(Authors, REPAIR_REBUILD )

<3>
將資料庫置為多使用者模式；

  sp_dboption zrb, single, false

 

注意：

DBCC CHECKDB
是大量佔用
CPU
和磁碟的操作。每一個需要檢查的資料頁都必須首先從磁碟讀入記憶體。另外，
DBCC CHECKDB
使用
tempdb
排序。

如果在
DBCC CHECKDB
運行時動態執行事務，那麼交易記錄會繼續增長，因為
DBCC
命令在完成日誌的讀取之前阻塞日誌截斷。

建議在伺服器負荷較少的時候運行
DBCC CHECKDB
。如果在負荷高峰期運行
DBCC CHECKDB
，那麼事務輸送量效能和
DBCC CHECKDB
完成時間效能都會受到影響。

四、定時重構，

    

如果資料庫訪問非常頻繁的話，非常容易出現資料分塊的現象，因此可以利用作業來系統相對閒置時候重構索引。