索引碎片的整理

解决碎片问题

一旦你确定表或索引有碎片问题，那么你有4个选择去解决那些问题：

1. 删除并重建索引

2. 使用DROP_EXISTING子句重建索引

3. 执行DBCC DBREINDEX

4. 执行DBCC INDEXDEFRAG

尽管每一个技术都能达到你整理索引碎片的最终目的，但各有各的优缺点。

 

删除并重建索引

    用DROP INDEX和CREATE INDEX或ALTER TABLE来删除并重建索引有些缺陷包括在删除重建期间索引会消失。在索引删除重建时，对于查询它不在可用，查询性能也许会受到明显的影响，直到重建索引为止。另一个潜在的缺陷是当都请求索引的时候会引起阻塞，直到重建索引为止。通过其他的处理也能解决阻塞，就是索引被使用的时候不删除索引。另一个主要的缺陷是在用DROP INDEX和CREATE INDEX重建聚集索引时会引起非聚集索引重建两次。删除聚集索引时非聚集索引的行指针会指向数据堆，聚集索引重建时非聚集索引的行指针又会指回聚集索引的行位置。

删除并重建索引的确有一个好处就是通过重新排序索引页，使索引页紧凑并删除不需要的索引页来完全重建索引。你也许需要考虑那些内部和外部碎片都很高的情况下才使用，以使那些索引回到它们应该在的位置。

 

使用DROP_EXISTING子句重建索引

    为了避免在重建聚集索引时表上的非聚集索引重建两次，可以使用带DROP_EXISTING子句的CREATE INDEX语句。这个子句会保留聚集索引键值，以避免非聚集索引重建两次。和删除并重建索引一样，该方法也可能会引起阻塞和索引消失的问题。该方法的另一个缺陷是也强迫你去分别发现和修复表上的每一个索引。

除了和上一个方法一样的好处之外，该方法的好处是不必重建非聚集索引两次。这样可以对那些带约束的索引提供正确的索引定义以符合约束的要求。

 

执行DBCC DBREINDEX

    DBCC DBREINDEX类似于第二种方法，但它物理地重建索引，允许SQLServer给索引分配新页来减少内部和外部碎片。DBCC DBREINDEX也能动态的重建带约束的索引，不象第二种方法。

DBCC DBREINDEX的缺陷是会遇到或引起阻塞问题。DBCC DBREINDEX是作为一个事务来运行的，所以如果在完成之前中断了，那么你会丢失所有已经执行过的碎片。

 

执行DBCC INDEXDEFRAG

    DBCC INDEXDEFRAG（在SQLServer2000中可用）按照索引键的逻辑顺序，通过重新整理索引里存在的叶页来减少外部碎片，通过压缩索引页里的行然后删除那些由此产生的不需要的页来减少内部碎片。它不会遇到阻塞问题但它的结果没有其他几个方法彻底。这是因为DBCC INDEXDEFRAG跳过了锁定的页且不使用任何新页来重新排序索引。如果索引的碎片数量大的话你也许会发现DBCC INDEXDEFRAG比重建索引花费的时间更长。DBCC INDEXDEFRAG比其他方法的确有好处的是在其他过程访问索引时也能进行碎片整理，不会引起其他方法的阻塞问题。

 

具体方法如下：

第一步：将过程创建到 master 数据库中 第二步： USE [ShoveDS] GO exec sp_defragment_indexes 90 GO 第三步： [ShoveDS]重建索引 USE [master] GO /****** Object: StoredProcedure [dbo].[sp_defragment_indexes] Script Date: 06/27/2009 15:08:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_defragment_indexes] @maxfrag DECIMAL AS SET NOCOUNT ON DECLARE @tablename VARCHAR (128) DECLARE @execstr VARCHAR (255) DECLARE @objectid INT DECLARE @objectowner VARCHAR(255) DECLARE @indexid INT DECLARE @frag DECIMAL DECLARE @indexname CHAR(255) DECLARE @dbname sysname DECLARE @tableid INT DECLARE @tableidchar VARCHAR(255) --检查是否在用户数据库里运行 SELECT @dbname = db_name() IF @dbname IN ('master', 'msdb', 'model', 'tempdb') BEGIN PRINT 'This procedure should not be run in system databases.' RETURN END --第1阶段:检测碎片 --声明游标 DECLARE tables CURSOR FOR SELECT convert(varchar,so.id) FROM sysobjects so JOIN sysindexes si ON so.id = si.id WHERE so.type ='U' AND si.indid < 2 AND si.rows > 0 -- 创建一个临时表来存储碎片信息 CREATE TABLE #fraglist ( ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL) --打开游标 OPEN tables -- 对数据库的所有表循环执行dbcc showcontig命令 FETCH NEXT FROM tables INTO @tableidchar WHILE @@FETCH_STATUS = 0 BEGIN --对表的所有索引进行统计 INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') FETCH NEXT FROM tables INTO @tableidchar END -- 关闭释放游标 CLOSE tables DEALLOCATE tables -- 为了检查，报告统计结果 --SELECT * FROM #fraglist --第2阶段: (整理碎片) 为每一个要整理碎片的索引声明游标 DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity FROM #fraglist f JOIN sysobjects so ON f.ObjectId=so.id WHERE /*ScanDensity <= @maxfrag AND*/ INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 -- 输出开始时间 --SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE()) --打开游标 OPEN indexes --循环所有的索引 FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag WHILE @@FETCH_STATUS = 0 BEGIN SET QUOTED_IDENTIFIER ON SELECT @execstr = 'DBCC DBREINDEX (' + '''' +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + '''' + ', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS' --SELECT 'Now executing: ' --SELECT(@execstr) EXEC (@execstr) SET QUOTED_IDENTIFIER OFF FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag END -- 关闭释放游标 CLOSE indexes DEALLOCATE indexes -- 报告结束时间 SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE()) -- 删除临时表 DROP TABLE #fraglist