重建索引提高SQL Server效能

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

1. 索引的體繫結構        SQL Server 2005在硬碟中用8KB頁面在資料庫檔案記憶體放資料。預設情況下這些頁面及其包含的資料是無組織的。為了使混亂變為有序，就要產生索引。產生索引後，就有了索引頁和資料頁之分：資料頁用來儲存使用者寫入的資料資訊；索引頁存放用於檢索列的資料值清單（關鍵字）和索引表中該值所在紀錄的地址指標。索引分為簇索引和非簇索引，簇索引實質上是將表中的資料排序，就好像是字典的索引目錄。非簇索引不對資料排序，它只儲存了資料的地址。向一個帶簇索引的表中插入資料，當資料頁達到100%時，由於頁面沒有空間插入新的的紀錄，這時就會發生分頁，SQL Server 將大約一半的資料從滿頁中移到空頁中，從而產生兩個1/2滿頁。這樣就有大量的空的資料空間。簇索引是雙向鏈表，在每一頁的頭部儲存了前一頁、後一頁以及分頁後資料移出的地址。由於新頁可能在資料庫檔案中的任何地方，因此頁面的連結不一定指向磁碟的下一個物理頁。連結可能指向了另一個地區，這就形成了分塊，從而減慢了系統的速度。對於帶簇索引和非簇索引的表來說，非簇索引的關鍵字是指向簇索引的，而不是指向資料頁的本身。        為了克服資料分塊帶來的負面影響，需要重構表的索引，這是非常費時的，因此只能在需要時進行。可以通過DBCC SHOWCONTIG來確定是否需要重構表的索引。

2. DBCC SHOWCONTIG 用法        下面舉例來說明DBCC SHOWCONTIG和DBCC REDBINDEX的使用方法。以應用程式中的Employee資料表作為例子，在 SQL Server的Query analyzer輸入命令：

use database_name
declare @table_id int
set @table_id=object_id('Employee')
dbcc showcontig(@table_id)

 

 輸出結果： 

DBCC SHOWCONTIG scanning 'Employee' table...
Table: 'Employee' (1195151303); index ID: 1, database ID: 53
TABLE level scan performed.
- Pages Scanned................................: 179
- Extents Scanned..............................: 24
- Extent Switches..............................: 24
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 92.00% [23:25]
- Logical Scan Fragmentation ..................: 0.56%
- Extent Scan Fragmentation ...................: 12.50%
- Avg. Bytes Free per Page.....................: 552.3
- Avg. Page Density (full).....................: 93.18%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

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

資訊                                                          描述Pages Scanned                           表或索引中的長頁數Extents Scanned                         表或索引中的長區頁數Extent Switches                          DBCC遍曆頁時從一個地區到另一個地區的次數Avg. Pages per Extent              相關地區中的頁數Scan Density[Best Count:Actual Count]                Best Count是連續連結時的理想地區改變數，Actual Count是實際地區改變，        Scan Density為100%表示沒有分塊。Logical Scan Fragmentation   掃描索引頁中失序頁的百分比Extent Scan Fragmentation    不實際相鄰和包含鏈路中所有連結頁的地區數Avg. Bytes Free per Page       掃描頁面中平均自由位元組數Avg. Page Density (full)         平均頁密度，表示頁有多滿

 

         從上面命令的執行結果可以看的出來，Best count為23 而Actual Count為25。這表明orders表有分塊，需要重構表索引。下面通過DBCC DBREINDEX來重構表的簇索引。 3 . DBCC DBREINDEX 用法        重建指定資料庫中表的一個或多個索引。文法 

DBCC DBREINDEX
(    
    [ 'database.owner.table_name'    
        [ , index_name
            [ , fillfactor ]
        ]
    ]  
)      

 

 參數 'database.owner.table_name'        是要重建其指定的索引的表名。資料庫、所有者和表名必須符合標識符的規則。有關更多資訊，請參見使用標識符。如果提供 database 或 owner 部分，則必須使用單引號 (') 將整個 database.owner.table_name 括起來。如果只指定table_name，則不需要單引號。 index_name        是要重建的索引名。索引名必須符合標識符的規則。如果未指定 index_name 或指定為 ' '，就要對錶的所有索引進行重建。 fillfactor        是建立索引時每個索引頁上要用於儲存資料的空間百分比。fillfactor 替換起始填滿因數以作為索引或任何其它重建的非叢集索引（因為已重建叢集索引）的新預設值。如果 fillfactor 為 0，DBCC DBREINDEX 在建立索引時將使用指定的起始fillfactor。        同樣在Query Analyzer中輸入命令：        dbcc dbreindex('database_name.dbo.Employee','',90)        然後再用DBCC SHOWCONTIG查看重構索引後的結果： 

DBCC SHOWCONTIG scanning 'Employee' table...
Table: 'Employee' (1195151303); index ID: 1, database ID: 53
TABLE level scan performed.
- Pages Scanned................................: 178
- Extents Scanned..............................: 23
- Extent Switches..............................: 22
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 100.00% [23:23]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 509.5
- Avg. Page Density (full).....................: 93.70%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

         通過結果我們可以看到Scan Denity為100%。

原文地址:http://fly3118.blog.51cto.com/361030/83836