Creating index on large table

Question

Creating index on large table

sqlcool

SSC Eights!

Points: 967
More actions
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

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

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

magasvs SSCertifiable Points: 7660 More actions · Answer 1

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.

sqlcool SSC Eights! Points: 967 More actions · Answer 2

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

magasvs SSCertifiable Points: 7660 More actions · Answer 3

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.

sachnam SSCrazy Points: 2317 More actions · Answer 4

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.