Speed up Index (non clustered) creation

Question

Speed up Index (non clustered) creation

Ells

SSCrazy Eights

Points: 9901
More actions
May 25, 2016 at 5:47 am

#400203

OK
so I need to add a new nonclustered index and its going to have a long list of columns in the include list. With 90 m Rows this could take some time.
Is there any way to guarantee that it happens as quickly as possible?
Dont fancy uit running for hours. The index build on dev has exceeded 1 hour so not good as that has less data.
Cheers
E

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Try experimenting in dev with the SORT_IN_TEMPDB option, but be aware of the consequences for the size of tempdb. If you have Enterprise Edition, consider using the ONLINE option. That may not make it go any quicker, but it will possibly reduce the implications of the operation taking a long time.

John

Ells SSCrazy Eights Points: 9901 More actions · Answer 2

Thanks,

Standard edition so no chance of ONLINE. Currently its whirring away slowly. We are now at 4 hours!

Its a non cluseterd index on one column including approx 30 other columns (all of the columns in the table).

Not a happy camper at the moment. I hate t when things like this get out of control.

E

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

All of the columns? Are you sure you need them all in the index? If you do, have you considered making this index the clustered index?

John

Ells SSCrazy Eights Points: 9901 More actions · Answer 4

It previously included most of the columns but as some columns had been added to the table but not the index I looked to add the new columns.

I have had issues with some of the stored procs that query the table (they do select *). Just dont ask why select * seems to be acceptable.

I would have thought there would be a batter option. But this comes up as the suggested missing index when looking at estimated execution plan.

E

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

You didn't answer the question about the clustered index. But assuming that 's not possible (or not desirable), don't just blindly accept index recommendations from the execution plan. Such recommendations are made with respect to the current query only. You may find that the cost of creating and maintaining the index is not worth the saving that it provides compared to a key lookup.

John