We have a SQL Server 2012 Enterprise Edition database whose indices fragment very quickly.
We use a maintenance plan that reorganizes those indexes daily, and rebuilds them weekly. The rebuild happens online and uses tempdb (Enterprise Edition SQL Server feature).
However during the 2 minutes of the rebuild, other connections with the database suffer. We get several timeouts or deadlocks. Sometimes the rebuild itself fails with a deadlock as well.
The rebuild failing is no problem, we can just try again. However other connections timing out or even taking very long is very troublesome.
Is there a way to run the rebuild with a lower priority, or automatically fail if the current processing resources will not suffice for an online rebuild?
There is no window of downtime for this database, so we cannot rebuild indices offline. Is there any way we can rebuild the indices online without significant disturbance of other requests?
Best Answer
There are several options available to help reduce the problems you're seeing:
I know you have SQL Server 2012, however with SQL Server 2014+, you can use the
WAIT_AT_LOW_PRIORITY
option to tell SQL Server to allow other sessions to proceed while the online index build operation is waiting to obtain its schema modification locks.From the MSDN SQL Server Books Online: