Monday, February 20, 2012

Online Reindex Build Perfomance Issues

We are in the process of testing online reindex rebuilds on a 260 Gig
database on a table with 40 gig of data and an index size of 42 gigs.
We are finding that this process is taking up a substantial amount of
resources (processor and Disk IO) so much so that at time even simple
queries are locking. Does any one know of any means to improve the
performance of the reindexing or perhaps lower it priority to reduce it
impact?
Thanks in advance,
-Bob"Bob Sills" <bobsills@.gmail.com> wrote in message
news:1163779525.169860.225690@.b28g2000cwb.googlegroups.com...
> We are in the process of testing online reindex rebuilds on a 260 Gig
> database on a table with 40 gig of data and an index size of 42 gigs.
> We are finding that this process is taking up a substantial amount of
> resources (processor and Disk IO) so much so that at time even simple
> queries are locking. Does any one know of any means to improve the
> performance of the reindexing or perhaps lower it priority to reduce it
> impact?
>
That's a gotcha with online rebuilds. They don't use locks, but they may
run in parallel and chew up a lot of resources. Try setting the MAXDOP in
the ALTER INDEX statement to reserve processors for your other workloads.
David|||David Browne wrote:
> "Bob Sills" <bobsills@.gmail.com> wrote in message
> news:1163779525.169860.225690@.b28g2000cwb.googlegroups.com...
> That's a gotcha with online rebuilds. They don't use locks, but they may
> run in parallel and chew up a lot of resources. Try setting the MAXDOP in
> the ALTER INDEX statement to reserve processors for your other workloads.
>
> David
Thanks! This is helping greatly.
-Bob

No comments:

Post a Comment