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 完成时间性能都会受到影响。

四、定时重构，    

如果数据库访问非常频繁的话，非常容易出现数据分块的现象，因此可以利用作业来系统相对空闲的时候重构索引。