Wednesday, October 8, 2014

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/

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates