Managed lock priority
Its available in SQL 2014 and used for online operation s
Mainly used for switching partition and rebuilding indexes online.
Till now its issue to switch partition while tables are in use.
Its because that requires schema modification lock SCH-M lock.
As its not possible to get this lock until any operation is running on table and hence switching partition is a nightmare for any developers.
This request for SCH-M lock was creating a chain of locks and hence affecting performance of OLTP operations.
Now in 2014 we have option that we can define priority for switching or online reindexing with other operations.
Lets see how to use this feature
If we see at syntax of alter table or alter index we will find this option
WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ], ABORT_AFTER_WAIT =
{ NONE | SELF | BLOCKERS } )
MAX_DURATION is time for which we want to wait before completing the operation
ABORT_AFTER_WAIT :- What action we want to take after waiting time completes
NONE :- Will take no action and continue waiting
SELF :- It will kill self session
BLOCKERS:- It will kill session which are creating blocking and will do switching or
reindexing.
But this killing session has one drawback it will lead transaction to rollback and if we dont know which operations are in rollback it may impact OLTP operations.
So we need to be careful while doing this and should be done when there is low activity or switching/reindexing has higher priority compare to other tasks.
Lets create a table and check this behavior
CREATE PARTITION FUNCTION part_test_func( datetime)
AS RANGE LEFT FOR VALUES( '2014-09-10' , '2014-09-20' , '2014-09-30');
CREATE PARTITION SCHEME part_test_scheme
AS PARTITION part_test_func TO( [primary],[primary],[primary],[primary]);
CREATE TABLE part_test( id int IDENTITY( 1 , 1) ,
data varchar( 100) ,
date datetime)
ON part_test_scheme( date);
CREATE TABLE part_test1( id int IDENTITY( 1 , 1) ,
data varchar( 100) ,
date datetime)
ON part_test_scheme( date);
INSERT INTO part_test
VALUES( 'a' ,
'2014-09-05'
);
INSERT INTO part_test
VALUES( 'a' ,
'2014-09-15'
);
INSERT INTO part_test
VALUES( 'a' ,
'2014-09-25'
);
INSERT INTO part_test
VALUES( 'a' ,
'2014-10-05'
);
Now in other window I will run this query
BEGIN TRAN
SELECT *
FROM part_test WITH(updlock)
Now we will go for partition switching in first window
We will run this query
ALTER TABLE part_test switch PARTITION 1 TO part_test1 PARTITION 1
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES,
ABORT_AFTER_WAIT = BLOCKERS))
It will wait for 1 minute and then kill the blocking and statement and complete
switching.
But we also need to know which session were killed.
We can find this details in errorlog
