The fastest way to rebuild indexes

Question

We have over 5 Tb database. There are some indexes more then 15 Gb in size.

The total rebuild of all indexes which are fragmented more then 30% takes about 1 day to perform.

It takes about 7 minutes to rebuild one index of 26 Gb in size. Is this ok?

We tried to make several jobs to go in parallel, but it makes no help.

What is the fastest possible way to rebuild indexes?

Answer 1

Hi zhichkin,

The fastest possible way to rebuild indexes is to rebuild the indexes in OFFLINE mode because OFFLINE index rebuilds do require less resources and take less time to complete than ONLINE index rebuilds.

And if you are using SQL Server enterprise edition, increase the "maximum degree of parallelism" (MAXDOP) when rebuilding indexes, which will speed up SQL Server off-line index rebuilds.

For more details about rebuilding indexes, please review the following blogs.
Rebuilding SQL Server indexes using the ONLINE option
Reduce Time for SQL Server Index Rebuilds and Update Statistics


Thanks,
Lydia Zhang

---

Lydia Zhang
TechNet Community Support

Answer 2

Adding to Lydia's response Make sure you use Ola hallengren for index rebuild its tested and used industry wide.

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My Technet Wiki Article
MVP

Answer 3

Thank you, Lydia, very much! The links given in your answer was very usefull. That was just what I wanted.