重组索引

第三步：重组索引

http://www.cnblogs.com/treeyh/archive/2009/07/22/1528604.html

http://blog.csdn.net/seusoftware/archive/2009/12/17/5027848.aspx

好，在你的表中你创建了所有合适的索引。或，多半，索引已经被创建在数据库表中。但是，你或许没有根据您的期望获得比较好的效果。

有可能，索引发生了碎片。

什么是索引碎片？

索引碎片是在索引页上进行插入，更新，删除操作造成的索引页分裂。如果索引有很多的碎片，扫描和查询索引需要的时间将更多。所以取数据操作执行很慢。

两种类型的碎片会出现：

1 、内部碎片

内部指的是页内，即页面的空闲空间。其实填充因子就是一种碎片，为了减少页拆分，宁愿适当地去制造这种碎片。但在大量内部碎片一直处于无法被数据填充的情况下，是没有益处的，它会导致扫描过程中读取额外的页面。

对于 LOB 和 ROW_OVERFLOW_DATA 页面，这是唯一的碎片形式，因为在这两种列上无法建立 B 树索引。

 

2 、外部碎片

（ 1 ）逻辑碎片

索引叶子节点页的逻辑顺序与物理顺序不一致，比如：有页号 1 ， 2 的两个页面，此时 1 页面发生页拆分，这时新申请的页面页号为 3 ，此时逻辑顺序为 1-3-2 ，但物理顺序是 1-2-3 ， 1 页面没有直接指向磁盘的下一个物理页，这就造成了不一致，即逻辑碎片；

 

（ 2 ）扩展碎片

SQL SERVER 通常给表或索引分配新的空间是以 EXTENT （区或扩展）的形式，一个区是 8 个页面，所以区的第一个页号应该是 8 的倍数，比如：一个包含有序区的表，第一个页面的页号应该是 8-16-24 ，这样下去，如果是 8-24 ，那么说明第一个页面页号为 16 的区被分配给了另一个表，那么 8-24 的表在物理上就存在一个间隙，即扩展碎片；

怎么知道是否发生了索引碎片？

在你的数据库中执行下列sql。（sql2005或较早的数据库，替换数据库名“AdventureWorks）

SELECT object_name(dt.object_id) Tablename,si.name

IndexName,dt.avg_fragmentation_in_percent AS

ExternalFragmentation,dt.avg_page_space_used_in_percent AS

InternalFragmentation

FROM

(

    SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent

    FROM sys.dm_db_index_physical_stats (db_id(' AdventureWorks '),null,null,null,'DETAILED'

)

WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id

AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10

AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC 

根据结果，你会发现索引碎片发生在哪里，使用以下标准。

1、InternalFragmentation 值 > 10 表明对应的内部碎片发生了，

2、 ExternalFragmentation 值 < 75 表明出现了外部碎片。

怎么管理这些碎片？

你有两条路：

1、重新整理索引，执行以下语句：

ALTER INDEX ALL ON TableName REORGANIZE

2、重新创建索引，执行以下语句：

ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90, ONLINE=ON ) 

在表里你也能重新创建或整理个别索引，使用索引名称代替“ALL”关键字。或者，使用sql管理器重建索引。

什么时候使用整理索引，什么时候使用重新创建索引？

外部碎片值在10-15以及内部碎片在60-75之间可以选择整理，其他情况选择重建索引。

重建索引时，有个重要的事情，当在一个特别的表上重建索引时，整个表将锁住，因此在一个大的产品数据库，锁住是不被允许的，因为，重建索引需要 数小时。幸运的是，在SQL Server 2005中有一个办法。你能使用ONLINE操作在重建索引时，可以重建索引，且不锁表。