6

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?

Improve this question
5
  • 3
    So you reorganize every day, and rebuild once a week. If you rebuild on Saturday and you have to reorganize again on Sunday, what did the rebuild buy you? Why not just reorganize every day, and save rebuilds for maintenance windows? (In theory, an online rebuild should not block anyone, save for a schema lock at the beginning and end of the operation, which should be fairly quick and certainly not cause timeouts, especially if the whole rebuild operation takes only 2 minutes. Are you sure it's actually working the way you think? Can you provide the .xdl for one of the deadlock events?)
    – Aaron Bertrand
    Dec 1, 2015 at 14:05
  • The problem is, we do not have a maintenance window. The database runs 24/7. Reorganizing on the next day may not always be necessary, but it also does not seem to hurt, which is why we did not change this part of the maintenance script so far. But we need a way to rebuild indices online; whether it happens daily, or weekly, or some other frequency.
    – HugoRune
    Dec 1, 2015 at 14:10
  • 1
    Just seems like the rebuild isn't buying you anything. In any case, we need more info about how an online rebuild can lead to deadlocks and timeouts. We can't help you fix the issue without actually understanding the issue. I mean, that is the whole point of the feature, after all.
    – Aaron Bertrand
    Dec 1, 2015 at 14:11
  • @HugoRune Have you tried doing a REBUILD with MAXDOP = 1 ? This CSS article explains why you should do with MAXDOP of 1. What is the fragmentation level of the indexes that you rebuild vs reorg along with page counts ? Also, what Aaron says perfectly makes sense - what do you gain by rebuilding indexes and followed by a reorg ?
    – Kin Shah
    Dec 1, 2015 at 14:19
  • Why do you need to rebuild? What would you get with a rebuild compared to a reorganize?
    – paparazzo
    May 12, 2016 at 8:17

1 Answer 1

Reset to default
6

There are several options available to help reduce the problems you're seeing:

/* set the LOCK_TIMEOUT value to the maximum number of milliseconds 
   you want to wait for the ALTER INDEX REBUILD to start
*/
SET LOCK_TIMEOUT 100;

/* this tells SQL Server our ALTER INDEX REBUILD statement should
   be the deadlock victim
*/
SET DEADLOCK_PRIORITY LOW;

/* Rebuild the index */
ALTER INDEX MyIndex 
ON MySchema.MyTable 
REBUILD WITH (
    ONLINE = ON
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , MAXDOP = 1
    );

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.

ALTER INDEX MyIndex 
ON MySchema.MyTable 
REBUILD WITH (
    ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF))
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , MAXDOP = 1
    );

From the MSDN SQL Server Books Online:

In order to execute the DDL statement for an online index rebuild, all active blocking transactions running on a particular table must be completed. When the online index rebuild executes, it blocks all new transactions that are ready to start execution on this table. Although the duration of the lock for online index rebuild is very short, waiting for all open transactions on a given table to complete and blocking the new transactions to start, might significantly affect the throughput, causing a workload slow down or timeout, and significantly limit access to the underlying table. The WAIT_AT_LOW_PRIORITY option allows DBA's to manage the S-lock and Sch-M locks required for online index rebuilds and allows them to select one of 3 options. In all 3 cases, if during the wait time ( (MAX_DURATION = n [minutes]) ) there are no blocking activities, the online index rebuild is executed immediately without waiting and the DDL statement is completed.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.