重组索引（带统计索引重组时间）

由于在工作中，系统重组索引耗时太久，排查不出问题的根源，故此手工写了如下代码。

首先，建立如下日志表

        /****** 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

通过以上代码即可以实现。