Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I have a table with over 70 million rows in and I have viewed the execution plan which has identified a missing index. I took a snapshot of data a short while ago and created this index on a local test database (which was fast to create) and the improvement is exactly what is needed in the live system. What I'd like to know is, is there anyway to tell how long it will take to create on the live system?

share|improve this question

1 Answer

up vote 1 down vote accepted

Actually - it depends on many things like

  • hardware you use
  • wideness of index
  • current load
  • ...

If index is relatively wide - it can take from minutes to hours.

but

if you have your DB on Enterprise Edition of Sql Server, than you can choose to create an index WITH ONLINE=ON - it will take a little bit more time and space to complete, but will not stop other processing with the table involved

share|improve this answer
So, there really is no way of being able to tell how long it will actually take? – Neil Knight Jan 17 '12 at 11:46
No, there is no any reliable way to say that – Oleg Dok Jan 17 '12 at 11:47
@NeilKnight - there are TONS of variables in this. It's also impossible to say how big it will be with any precision. – JNK♦ Jan 17 '12 at 14:13
I've seen something remotely close to this in Oracle 10g, where having a long SELECT running you could query a system view and see how much % of the data has been gathered.. – Andrei Rinea Feb 27 '12 at 16:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.