0 SQL2014: Manage Lock priority with online index rebuild and partition switch

In SQL 2014, new option WAIT_AT_LOW_PRIORITY has been introduce to help manage the operation.

• MAX_DURATION: How long the session needs to wait un the low priority
• ABORT_AFTER_WAIT: What happen after the MAX_DURATION
• NONE: Default. continue to wait
• SELF: Terminate the DDL ( itself)
• BLOCKER: Terminate the blocking process

Here is the sample syntax for Index rebuild.

ALTER INDEX ALL ON [HumanResources].[Department] REBUILD

 WITH (ONLINE = ON (

   WAIT_AT_LOW_PRIORITY (

    MAX_DURATION = 2   MINUTES,

    ABORT_AFTER_WAIT = BLOCKERS

      ) 

    )

  ) 

Here is the sample syntax for switching partition.

ALTER TABLE T1  SWITCH PARTITION 2 TO T2

with (WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = NONE ));

Reference

http://www.mssqltips.com/sqlservertip/3120/whats-new-in-sql-server-2014-is-it-worth-the-upgrade/