10

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?

3 Answers 3

7

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

4
  • So, there really is no way of being able to tell how long it will actually take? Jan 17, 2012 at 11:46
  • 1
    No, there is no any reliable way to say that
    – Oleg Dok
    Jan 17, 2012 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, 2012 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.. Feb 27, 2012 at 16:08
5

Yes, it depends on a many factors and there's no way to really know unless you try.

I just built an index a single column of type BigInt over 200+ million rows however and it took a hairy 3 minutes.

Sure, my hardware is different from yours, my database version is different from yours etc. etc. but now you have a real world example to give you a ballpark idea rather than somewhere between a picosecond and a googolplex of lifetimes of the Universe.

4
  • 8
    I'm indexing 200m rows of nvarchar(MAX) on dev edition on my PC. I'll leave a message for my heirs to report back when it finishes. :P Aug 4, 2017 at 20:25
  • 3
    @BrienMalone - Ouch, it's been two days and no reply!!
    – Robino
    Aug 7, 2017 at 15:09
  • 5
    haha! It wasn't as bad as I thought. I didn't see when it finished, but it was less than 2 hours. When I ran it on the production server, it took about 25 minutes, but man what a difference in our queries. Aug 7, 2017 at 15:26
  • 2
    I indexed a varchar(32) with 6.5m rows in 12 min 13 sec on a 64-bit Intel w/ 2 cores @ 1.8 GHz Dec 24, 2019 at 1:41
1

Take a recent copy of your database and install in a test environment with similar (or identical if possible) specs. Create your index there. Now when you go to production you will have an idea of how long it should take. When in production it is best to perform this as part of a maintenance window without any other applications hammering away at the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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