Performance issue during Alter table on a huge table having many indexes.
You can face the performance issue while running alter table on a very huge table having many indexes created.
for example : alter table takes around 38 hours for a very large table which has around 538 columns and 15 nonclustered indexes.
The table size is approx 600 GB having 7.5 millions of rows .
The reason for the performance issue is because the indexes are created serially.
To fix this performance issue, Please follow the below steps :
1. drop the indexes,
2. run the alter table,
3. recreate the indexes in parallel
Hope this will be helpful.
What if the table has a clustered index ? Don't we need to create that first ?
Avinash
Hi Avinash,
yes, Clustered index should be created first.
Gaurav,
How long did the alter take after dropping the indexes, and how long did creating the indexes in parallel take ?
I'm willing to entertain the idea that there is an improvement, but I'd like to know what it actually was.
Martin
Martin ,
In the above case with table having around 7.5 millions of records.
Dropping the indexes, Run the alter table, Recreate the indexes in parallel - This whole activity takes approx. 9 hours.
Hi Gaurav,
Correct me if I'm wrong but when you say you create the indices in parallel, does it mean you're using the clause with consumers = xxx ?
if this is the case, how can you check it's indeed creating the indices in parallel?
I believe all the consumers part of an index creation will have the same FID in sp_who output, right?
Thanks
Simon
Hi Simon,
With consumers clause specifies the number of consumer processes that will perform the sort operation for creating the index.
Creating the index in parallel, means you can create the non-clustered indexes in seperate sessions in parallel.
~Gaurav