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?

Improve this question
3
  • Which version / edition of SQL server are you using?
    – George.Palacios
    Commented Jun 12, 2018 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
    Commented Jun 12, 2018 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
    Commented Jun 13, 2018 at 0:36

1 Answer 1

Reset to default
1

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

https://learn.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.

Improve this answer

Your Answer

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

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