sql-server - without - sql server create index

Best way to deploy new index to very large table in SQL Server 2008 (3)

Another approach can be not to implement the indexes on all the tables suggested by the sql server optimizer, rather first implement this on one table or group of tables. As you mentioned that a few hours downtime is OK, so using these few hours plan out the various tables on which the indexing needs to be done. Now daily select those tables whose indexes can be build in the given downtime. Working smartly can easily resolve this problem.

Same scenario came up to us where we could get only 1 hr downtime daily and we made the same approach and within 9 days the new indexes were made and the downtime too was used effectively.

Hope this helps ...

I have a database in production with one table that has grown extremely large (lots of accumulated data).

To improve query performance I used the sql server optimizer which suggested a new index.

So I made a copy of the production database to test against and it does improve performance, however my problem is that it took about 24 hours to create the index and while the index is being created the application is unusable.

For this particular application, being down for a few hours is not a problem but a 24 hour downtime would be and I am looking for a way to create this index without having to do that.

I only have a few ideas at the moment.

One idea is to copy a backup to another server. Apply the new index and any other changes. Copy the backup back to the production server. Take the application down and merge over any new data since when I took the backup.

Of course this has its own set of problems like having to merge the data back together so I don't like this idea for that reason.

This is SQL Server 2008 Standard Ed.

I normally deploy database changes by script.

UPDATE: Another idea would be to move the archive data out of the main table over several days in chunks. Then create the index when the table got small enough. Then slowly migrate the data back.

Given the lack of processing power available on the VM machine, combined with what is no doubt pretty poor IO throughput, I would actually consider calculating the time to backup, restore to a half decent server, index and then backup / restore back to the VM machine.

To avoid the initial backup taking a long time, you could back it up one day and be moving it during the day, and then when the maintenance window starts, backup the transaction log and move that across - on the basis it will be a smaller move. (This assumes bulk / full log mode)

Why dont your partition the table and index each partition . This way you are only indexing in small parts and then you can combine the partitions later on.