0

I would like to know, is there any possibility to control the locks while Ola's index optimize job is running?

I have implemented Ola Hallengren's index optimize job and am running it every day, but it is blocking the user transactions and causing performance issues.

How can I avoid blocking and locks while an index job is running?

  • Which version / edition of SQL server are you using? – George.Palacios Jun 12 '18 at 19:18
  • There is an important consideration based on whether or not you are doing a REBUILD. Currently, only the Enterprise Edition of SQL Server can perform a REBUILD without locking the index for the entire index for the entire rebuild time by using the ONLINE option. If this is a problem, you probably have to use REORGANIZE instead, unless you can bring your system down during the rebuilds. – Laughing Vergil Jun 12 '18 at 20:23
  • If you have a window of opportunity (i.e. off-business hours during the week, weekends, etc.) then I would schedule the index optimize job during that window. However, I'd be asking myself if I really needed to run those index jobs every night: link – Colin Douglas Jun 13 '18 at 0:36
1

Not really controlling solution but you may try new index rebuild options :

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-2017

::=
{
WAIT_AT_LOW_PRIORITY ( MAX_DURATION = [ MINUTES ] ,
ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

this way you may control behavior of maintenance operation if it is blocking other processes.

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.