Skip to Content
Author's profile photo Gaurav Jowhry

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.

Assigned tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Avinash Kothare
      Avinash Kothare

      What if the table has a clustered index ? Don't we  need to create that first ?

      Avinash

      Author's profile photo Gaurav Jowhry
      Gaurav Jowhry
      Blog Post Author

      Hi Avinash,

      yes, Clustered index should be created first.

      Author's profile photo Martin English
      Martin English

      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

      Author's profile photo Gaurav Jowhry
      Gaurav Jowhry
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Gaurav Jowhry
      Gaurav Jowhry
      Blog Post Author

      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