1

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 (SET LOCK_TIMEOUT),
  • 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?

5
  • TomV meant: What Edition of SQL Server are you running? Enterprise or Standard Edition.
    – John K. N.
    Apr 4, 2017 at 10:53
  • I just rechecked, the script runs on SQL Server 2012 Enterprise, v11.0.3128.0. I can use online rebuilds available in Enterprise. I cannot use "low priority lock wait" added in sql 2014
    – HugoRune
    Apr 4, 2017 at 11:22
  • 1
    Why don't you just contact Ola and ask if/when he will add support for those features? Just to also note you could take a look at Minion Reindex as it offers a bit more configuration options compared to Ola's...matches more to what you are wanting to do.
    – user507
    Apr 4, 2017 at 12:41
  • 1
    I hope you are petitioning the project manager for a true maintenance window. Changing the oil on a car while still driving is going to lead to problems eventually.
    – datagod
    Apr 4, 2017 at 12:46
  • 1
    I'm voting to close this question as off-topic because it's an older, unanswered question and as such is likely no longer relevant to the current build of Ola's solution. Nov 20, 2019 at 15:33

0