Managed lock priority -SQL 2014

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