由于在工作中，系统重组索引耗时太久，排查不出问题的根源，故此手工写了如下代码。
首先，建立如下日志表
/****** Object: Table [dbo].[ReorganizeLog] Script Date: 06/20/2013 16:09:27 ******/
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [ dbo ]. [ ReorganizeLog ]( [ PKID ] [ int ] IDENTITY( 1, 1) NOT NULL, [ Name ] [ varchar ]( 200) NULL, [ TableName ] [ varchar ]( 200) NULL, [ BeginTime ] [ datetime ] NULL, [ EndTime ] [ datetime ] NULL, [ TimeSpan ] [ time ]( 7) NULL, CONSTRAINT [ PK_ReorganizeLog ] PRIMARY KEY CLUSTERED ( [ PKID ] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ PRIMARY ] ) ON [ PRIMARY ] GO SET ANSI_PADDING OFF
GO
接下来，利用游标对所有索引进行遍历，逐个重组，也可以改成逐个重建，用到的自己动手修改
USE DbName;
GO DECLARE @indexName varchar( 200), @tableName varchar( 100); DECLARE allIndex CURSOR FOR SELECT a.name , c.name FROM sysindexes a JOIN sysindexkeys b ON a.id = b.id AND a.indid = b.indid JOIN sysobjects c ON b.id = c.id WHERE a.indid NOT IN ( 0, 255 ) and c.xtype = ' U ' ORDER BY c.name , a.name OPEN allIndex; FETCH NEXT FROM allIndex INTO @indexName, @tableName; WHILE @@FETCH_STATUS = 0 BEGIN -- Concatenate and display the current values in the variables. PRINT @indexName + ' , ' + @tableName declare @beginTime datetime, @endTime datetime, @timespan time, @sql varchar( 1000) set @beginTime = GETDATE() set @sql = ' ALTER INDEX ' + @indexName + ' ON ' + @tableName + ' REORGANIZE WITH ( LOB_COMPACTION = ON ) ' exec( @sql) set @endTime = GETDATE() set @timespan = @endTime - @beginTime INSERT INTO [ YeeGoTemp ]. [ dbo ]. [ ReorganizeLog ]( [ Name ], [ TableName ], [ BeginTime ], [ EndTime ], [ TimeSpan ]) VALUES( @indexName, @tableName, @beginTime, @endTime, @timespan) FETCH NEXT FROM allIndex INTO @indexName, @tableName; END CLOSE allIndex; DEALLOCATE allIndex; GO
通过以上代码即可以实现。
在此以作备忘之用。