SQL Server: 索引碎片产生及修复

索引碎片的产生：

索引能够加快对表的访问速度，然而任何事物都有两面性，索引在带给我们便利的同时也会占用额外的磁盘空间，并且我们在对表进行增删改的操作时也要消耗额外的时间来更新索引。而在我们对包含索引的表进行增删改时，也会造成索引碎片，久而久之，索引碎片程度越来越高，反而会降低我们对表的访问速度。因此作为数据库管理员，要定期维护索引，修复索引碎片。

查询索引碎片：

通过sys.dm_db_index_physical_stats这个存储过程我们可以对索引的碎片信息进行查询，此存储过程适用于SQL 2005/2008/2008R2/2012。

简单的模拟以下索引碎片产生的过程：

1）首先创建一个带索引的表，并且向表中插入一些记录：

--CREATE DATABSE 
create database testDB
GO

Use testDB
GO

--CREATE TABLE
CREATE TABLE testTB (C1 INT, C2 DATE, C3 VARCHAR(50))
GO

--CREATE INDEX
CREATE clustered INDEX index01 on testTB(C1)
GO

--INSERT 100 RECORDS INTO THE TABLE
DECLARE @i int
SET @i=1
WHILE @i<=100
BEGIN
INSERT INTO testTB VALUES(@i,'20121026','testdata')
set @i=@i+1
END

2）此时对sys.dm_db_index_physical_stats进行第一次查询，索引碎片百分比为0：

--QUERY INDEX FRAGMENTATION INFO
SELECT database_id,object_id,index_id,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages 
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED');

3）接下来我对testTB表依次进行了删除，更新和增加记录的操作：

--DELETE SOME RECORDS FROM TABLE
DECLARE @j int
set @j=10
while @j<=100
BEGIN
DELETE FROM testTB WHERE C1=@j
SET @j=@j+10
END

--UPDATE TABLE
UPDATE testTB SET C2='20121025' WHERE C2='20121026'


--GO ON INSERTING RECORDS TO TABLE
DECLARE @i int
SET @i=101
WHILE @i<=800
BEGIN
INSERT INTO testTB VALUES(@i,'20121026','testdata')
set @i=@i+1
END

4）结合索引信息表sys_indexs我们做一个更详细的查询，可以看到索引碎片已经产生：

--QUERY INDEX FRAGMENTATION INFO AGAIN
SELECT DB_NAME(V1.database_id) as 'Database Name',
OBJECT_NAME(V1.object_id) as 'Table Name',
v2.name as 'Index Name',V1.index_id as "Index ID",
V1.avg_fragmentation_in_percent as 'Average Percent Fragmentation'
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'DETAILED') AS V1
JOIN sys.indexes AS V2 
ON V1.object_id=V2.object_id and V1.index_id=V2.index_id

 

修复索引碎片：

索引碎片过高会降低对数据的访问效率，想要修复索引碎片，可以使用ALTER INDEX命令，索引碎片化程度不同，使用的参数也不同，应该遵循如下规律：

1）当avg_fragmentation_in_percent >30%时，使用ALTER INDEX REBUILD命令修复索引碎片；

2）当5%<avg_fragmentation_in_percent <=30%时，使用ALTER INDEX REORGANIZE命令修复索引碎片。

在第一种情况下，如果想在修复索引碎片的同时其他用户仍然可以访问数据库，使用with (ONLINE=ON)参数，但是Online index 操作只能在企业版的SQL Server上执行，我当前测试的数据库是Standard Edition的，在执行这个命令时就会遇到问题，如下图所示：

因此在非企业版的SQL Server上只能不带with (ONLINE=ON)参数来执行ALTER INDEX REBUILD命令，本文例子如下：

ALTER INDEX index01 ON testTB REBUILD;

Note:如果表非常小，这两个命令可能并不适用。

参考资料：
Index Fragmentation Report in SQL Server 2005 and 2008

Fixing Index Fragmentation in SQL Server 2005 and SQL Server 2008

Identify Database Fragmentation in SQL 2000 vs SQL Server 2005