Recombinant index (with statistical index reorganization time)

      Because in the work, the system of recombinant index take too long, and not the root of the problem, so the hand write the following code.

First of all, to establish the following log table

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

      Next, use the cursor to traverse, for all indexed by recombination, can also be changed by reconstruction, used to amend the do it yourself

      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

Through the above code can achieve.

In order to make note of.

Posted by Edward at November 20, 2013 - 4:03 PM