Sql-server – How to control blocking sessions while an index maintenance job runs

index-maintenanceola-hallengrensql server

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?

Best Answer

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.

Related Question