Sql-server – Index Rebuild Operation

indexsql server

I'm planning to do an index rebuild operation for over 30+ indexes. Some index rebuild require almost >30min because the Table Record is around 60,000,000. Command used for this table is 

ALTER INDEX [T_MAIL_RCVLIST_IDX1] ON [dbo].[T_MAIL_RCVLIST] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

However, I would have to run this command for every single index, which is a big hassle and I decided to use a cursor and run it at one go. But I'm worried there could be an unexpected issue and my boss also warned me of this and suggested I run each index build individually. Could there be an issue? If yes, what kind and how would I fix this?

Here is the cursor index build query.

DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 1

DECLARE DB_Cursor CURSOR FOR 
 SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)'
 EXEC (@sql)

 PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
 SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor
 INTO @ownerName, @tablename

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor;

Best Answer

Ola Hallengren has already done the work for you here, no need to roll your own code. You can even cherry pick specific indexes or tables.

https://ola.hallengren.com/

I'd also recommend going ONLINE with those rebuilds if you're on Enterprise Edition, that'll save you locking of the tables for writes.

Related Question