Post reply

Creating index on large table

  • January 19, 2011 at 6:02 pm

    #388673

    Hi there

    I am dropping and recreating an index which has millions of rows (300M+ records). There are lots of input entry points to this table. Also there are substantial amount of updates and reads all at same time.

    Can you tell me will there be any impact if i drop and recreate the index (within single transaction)?

    In dev environment it took 45 minutes to complete the transaction. Is there any precaution i need to consider? Will it cause downtime to live environment?

    Index created on these columns

    TransactionDate - datetime

    receiverAddress - varchar(max)

    senderAddress - varchar(max)

    Thankx

  • January 19, 2011 at 7:03 pm

    #1275638

    Defenetely should be careful if your SQL Server edition is not Enterprise. Then ONLINE index option will not be available and table will be locked for the duration of the operation.

  • January 19, 2011 at 7:31 pm

    #1275640

    we have enterprise edition. Will it still lock the table?

  • January 19, 2011 at 7:36 pm

    #1275643

    It will not lock the table if you use ONLINE = ON index option. It will still affect performance. I try to create indexes on large tables after hours only.

  • January 19, 2011 at 10:14 pm

    #1275666

    you can also use SORT_IN_TEMPDB =ON option.

    It might make your index rebuilding operation faster if the data files of your tempdb database

    is on a different set of disks. But it might also increase the size of your tempdb so make sure you have sufficient amount of disk space for your tempdb datafiles .

    ONLINE =ON option

    This is a very useful option but it delays the rebuiling operation because it will allow users to access the underlying tables involved in index rebuiling operation. So for transactional consistency the data modified during rebuilding operations will be kept in a version store in tempdb .

    So this might also inflate your tempdb database size so watch out for the same.

    Also, watch out your database log files because it is a fully logged operation by default and can inflate the size of your Transaction log .Better to change the recovert model to bulk-logged if your business allows that.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply