We setup ALTER Index job with Ola's maintenance solution, the job is failing with below error, the job is failing with locking, enter image description here

  • That is no small index. I would experiment doing a REBUILD of an index of that size with different MAXDOP options to achieve faster results. As for why, you can setup a blocked process Extended Events session to capture the why. sqlskills.com/blogs/erin/…. Here is another good resource for Blocked process XE brentozar.com/archive/2014/03/extended-events-doesnt-hard – nkdbajoe May 21 '18 at 16:48
  • Some information on the index would be helpful. Is it clustered? Unique? Is there a clustered index on the table? How many fields in the index? Also, is this table actively being written to as the rebuild happens? How long are locks held on the indexes in this table (either this index OR the clustered index, if any). Finally, try it with LOB Compaction OFF, and see if you need to reorg first, then do it again with compaction. – Laughing Vergil May 21 '18 at 20:01

Here are a few possible solutions to your problem:

Option 1: Don't rebuild that index

That's a 30 GB index you have there. What measurable performance problem are you trying to solve by rebuilding it? Especially at 5% fragmentation, this seems like an incredibly expensive operation (in terms of system resources and locking) for very little gain.

You can read some very well-founded opinions on why you might want to give up on the index rebuild here:

This is far and away your best, and EASIEST, option. This is the home run. Do this.

Option 2: Rebuild online

Index rebuilds require a SCH-M lock. If you add WITH (ONLINE = ON) to your ALTER INDEX command, that lock will be deferred until the very end of the rebuild operation, which might increase the potential that whatever is preventing your maintenance task from completing has released its locks.

Option 3: Identify the blocking query

This is probably the most work of the options. You can run sp_WhoIsActive while the ALTER INDEX command is running, and it should show you what else is running, and specifically it will show you what other session is blocking the ALTER INDEX command from acquiring its lock. At that point, you have a bunch of options to deal with the problem:

  • reschedule your index maintenance so it's not running at the same time as the blocking query
  • re-work your blocking query so that it takes less locks / different locks / holds locks for less time (you could post another question with execution plan details if you need help with that)
  • reschedule the blocking query so that it is not running during the period when you do index maintenance

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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