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?
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.
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
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.
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.