Joy Smith San (6/17/2013)
What are thr things that we need to do to speed up the index rebuild process ?..... in brief...
Better IO subsystem.
More memory (maybe)
Reduce the need to rebuild indexes.
To add to Gail's suggestions...
Partitioning of certain tables, such as audit tables (or other tables that don't usually suffer updates, just inserts), can help a lot if partitioned by row creation date. For example, I have an audit table at work that I'm setting up to partition by creation date. It has almost 3 years of data in it and the table has about 25GB of data in it. Even though the clustered index is in good shape (based on the IDENTITY column) and never needs reindexing (follows Gail's suggestion of "Reduce the need to rebuild indexes"), the other indexes take quite some time. When I'm done partitioning it, I'll only need to reindex the "current month" partition.
I'm also moving all but the last 3 months of audit partitions to another database (using partitioned views so I can put them in a different DB) so that my backup and restore times will be reduced by quite a bit. During a restore for DR, it's much more important to get the database back up and running than it is to restore 3 years of audit data which can be done almost leisurely after the main database is up.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs