首页 > 代码库 > 转：索引碎片（代码好像不可用，待修改）
转：索引碎片（代码好像不可用，待修改）
创建索引是为了在检索数据时能够减少时间，提高检索效率。创建好了索引，并且所有索引都在工作，但性能却仍然不好，那很可能是产生了索引碎片，你需要进行索引碎片整理。
1、什么是索引碎片?
由于表上有过度地插入、修改和删除操作，索引页被分成多块就形成了索引碎片，如果索引碎片严重，那扫描索引的时间就会变长，甚至导致索引不可用，因此数据检索操作就慢下来了。
有两种类型的索引碎片：内部碎片和外部碎片。
内部碎片：为了有效的利用内存，使内存产生更少的碎片，要对内存分页，内存以页为单位来使用，最后一页往往装不满，于是形成了内部碎片。
外部碎片：为了共享要分段，在段的换入换出时形成外部碎片，比如5K的段换出后，有一个4k的段进来放到原来5k的地方，于是形成1k的外部碎片。
2、 如何知道是否发生了索引碎片?
执行下面的SQL语句就知道了(下面的语句可以在SQL Server 2005及后续版本中运行，用你的数据库名替换掉这里的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
执行后显示AdventureWorks数据库的索引碎片信息。
图 3 索引碎片信息
使用下面的规则分析结果，你就可以找出哪里发生了索引碎片：
1)ExternalFragmentation的值>10表示对应的索引发生了外部碎片;
2)InternalFragmentation的值<75表示对应的索引发生了内部碎片。
如何整理索引碎片?
有两种整理索引碎片的方法：
1)重组有碎片的索引：执行下面的命令
ALTER INDEX ALL ON TableName REORGANIZE
2)重建索引：执行下面的命令
ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON)
也可以使用索引名代替这里的“ALL”关键字，重组或重建单个索引，也可以使用SQL Server管理工作台进行索引碎片的整理。
3、什么时候用重组，什么时候用重建呢?
当对应索引的外部碎片值介于10-15之间，内部碎片值介于60-75之间时使用重组，其它情况就应该使用重建。
值得注意的是重建索引时，索引对应的表会被锁定，但重组不会锁表，因此在生产系统中，对大表重建索引要慎重，因为在大表上创建索引可能会花几个小时，幸运的是，从SQL Server 2005开始，微软提出了一个解决办法，在重建索引时，将ONLINE选项设置为ON，这样可以保证重建索引时表仍然可以正常使用。
虽然索引可以提高查询速度，但如果你的数据库是一个事务型数据库，大多数时候都是更新操作，更新数据也就意味着要更新索引，这个时候就要兼顾查询和更新操作了，因为在OLTP数据库表上创建过多的索引会降低整体数据库性能。
我给大家一个建议：如果你的数据库是事务型的，平均每个表上不能超过5个索引，如果你的数据库是数据仓库型，平均每个表可以创建10个索引都没问题。 可在【选项】对窗口模式进行合并/分离设置。
5、数据库磁盘碎片整理相关脚本
/* 读取磁盘分区信息 */
CREATE PROCEDURE SP_ExtentInfo
AS
DBCC ExtentInfo(0)
GO
/* SQLServer磁盘碎片整理 */
CREATE PROCEDURE SP_ShrinkSpaces
(
@UsagePercent numeric(2,2) = 0.60
--整理小于指定使用率的表空间，1为100%使用率无需整理
)
AS
BEGIN
--创建保存分区信息的临时表
Create Table #ExtentInfo
(
fileid smallint,
pageid int,
pg_alloc int,
ext_size int,
obj_id int,
index_id int,
partition_number int,
partition_id bigint,
iam_chain_type varchar(50),
pfs_bytes varbinary(10)
)
insert into #ExtentInfo exec SP_ExtentInfo
--使用游标，对小于指定空间使用率的表进行整理
declare @Table sysname
declare @Index sysname
declare @IdentityName sysname
declare @sql varchar(1000)
declare cs cursor for
select (select name from sysobjects where id=obj_id and xtype=‘u‘), --xtype=‘u‘的记录为数据表
(select name from sysindexes where id=obj_id and indid=1) --indid=1的记录为聚集索引
from #ExtentInfo group by obj_id
having sum(pg_alloc)*1.0/max(ext_size)/count(*)<=@UsagePercent
open cs
fetch next from cs into @Table,@Index
while @@FETCH_STATUS=0
begin
if @Table is not null
begin
if @Index is not null
begin
--重建聚集索引
set @sql = ‘alter index ‘ + @Index + ‘ on ‘ + @Table + ‘ rebuild‘
print @sql
exec(@sql)
end
else
begin
--对于堆，清空并重新写表或给自增列加聚集索引（128代表自增列）
select @IdentityName=name from syscolumns where id=OBJECT_ID(@Table) and status=128
if @@ROWCOUNT=0
set @sql = ‘select * into #ExtentTable from ‘ + @Table + ‘ truncate table ‘ + @Table + ‘ insert ‘ + @Table + ‘ select * from #ExtentTable‘
else
set @sql = ‘create clustered index ExtentOperaPrimaryKey on ‘ + @Table + ‘(‘ + @IdentityName + ‘) drop index ‘ + @Table + ‘.ExtentOperaPrimaryKey‘
print @sql
exec(@sql)
end
end
fetch next from cs into @Table,@Index
end
close cs
deallocate cs
--收缩当前数据库
DBCC SHRINKDATABASE(0)
--重新获取分区信息
truncate table #ExtentInfo
insert into #ExtentInfo exec SP_ExtentInfo
--显示当前分区信息
select fileid,obj_id,index_id,partition_id,ext_size,
object_name(obj_id) as ‘对象名‘,
count(*) as ‘实际区数‘, sum(pg_alloc) as ‘实际页数‘,
ceiling(sum(pg_alloc)*1.00/ext_size)*ext_size as ‘最大可用页数‘,
ceiling(sum(pg_alloc)*1.00/ext_size*100.00/count(*)) as ‘表空间使用比率‘
from ExtentInfo
group by fileid,obj_id,index_id,partition_id,ext_size
order by partition_id,obj_id,index_id,fileid
--删除临时表
drop table #ExtentInfo
END
GO
转：索引碎片（代码好像不可用，待修改）