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