In a SQL Server 2012 Enterprise database without a true maintenance window, I would like to run Ola Hallengrens script to optimize indices.
Testing has shown that while indices are reorganized and rebuild (online rebuild) on this database, other processes can experience frequent deadlocks and timeouts.
I would like to avoid disturbing other processes as much as possible; the maintenance script can be rerun easily and without the users noticing, so it does not matter if the rebuild itself is cancelled.
Therefore I would like to modify the maintenance script
- to run at a low deadlock priority (
SET DEADLOCK_PRIORITY LOW),
- to wait only a short time to acquire locks (
- and possibly to automatically retry again after a short delay when an index reorganize or online rebuild is cancelled by a deadlock or lock timeout error (some sort of
WHILE ... TRY ... CATCH ...).
I have started by adding
SET DEADLOCK_PRIORITY LOW at the start of indexOptimize.sql, and it seems to run just fine, but I am not sure whether I have added it in the right place. I am worried that I may be messing with things I do not fully understand.
What are the pitfalls in adding these three options?
Is there a particular danger I should be aware of, a reason why the script does not include this functionality already?
What is the right place to put the
SET DEADLOCK_PRIORITY LOW and
SET LOCK_TIMEOUT within the script, so that they will be observed for all reorganize and rebuild operation?