0

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;
2
  • Check my reply for this thread: dba.stackexchange.com/questions/119605/…
    – user165491
    Commented Feb 12, 2020 at 5:17
  • If you plan to use your script I'd suggest to implement also MAXDOP = n, which will run the index creation/rebuild in parallel and can save you some time. (N will depend on your CPU count) Also if tempdb is on a separate disk, you can set sort_in_tempdb on.
    – dbamex
    Commented Feb 12, 2020 at 18:36

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.