SQL 2014 Learning Series # 14 – New Feature – Managed Lock Priority (Part 2)
Posted by blakhani on June 19, 2014
In last part Managed Lock Priority (Part 1) we have seen the reason of introducing such behavior. In this part, we would see some demo about this feature. I would try to cover as many demo as I can.
Demo 1 : ABORT_AFTER_WAIT = NONE
-- Create Database CREATE DATABASE MLPDemo go -- Create Table in Database USE MLPDemo go CREATE TABLE OnlineIndexRebuild ( iID INT, vcFName VARCHAR(100), vcLName VARCHAR(100), CONSTRAINT PK_OnlineIndexRebuild PRIMARY KEY (iID) ) go -- Populate Table SET nocount ON go DECLARE @iLoop INT SET @iLoop = 1 WHILE @iLoop <= 100000 BEGIN INSERT INTO OnlineIndexRebuild VALUES (@iLoop, 'FName' + CONVERT(VARCHAR(10), @iLoop), 'LName' + CONVERT(VARCHAR(10), @iLoop) ) SET @iLoop = @iLoop + 1 END go SET nocount OFF go
Now, we are going to take a shared lock on the table and run index rebuild in separate query window. You may need to modify the SPID as per your demo environment.
Query 1: Taking Shared Lock and Holding it.
BEGIN TRAN SELECT count(*) 'Count' FROM OnlineIndexRebuild (HOLDLOCK) WHERE iID <= 1000 Select @@SPID '@@SPID' -- ROLLBACK TRAN
Query 2: Running Online Index Rebuild with MLP settings.
Select @@SPID 'SPID' go ALTER INDEX PK_OnlineIndexRebuild ON OnlineIndexRebuild REBUILD WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = NONE ) ) )
Since we have asked SQL Server to run this work on lower Priority, It would wait behind 53 (refer earlier screenshot) but only for 1 minute.
select session_id, blocking_session_id, wait_resource, wait_time, wait_type from sys.dm_exec_requests where session_id = 55
SELECT request_session_id ,request_mode ,request_type ,request_status ,resource_type FROM sys.dm_tran_locks WHERE request_session_id = 55
After MAX_DURATION is elapsed, we would see below in the same DMVs. This confirms that after MAX_DURATION the DDL thread came to normal queue.
Demo 2 : ABORT_AFTER_WAIT = SELF
This option is self-explanatory. Suicide
Demo 3 : ABORT_AFTER_WAIT = BLOCKERS
As the options says, SQL Server is going to kill all blockers. I have run same command again with this option and as expected, the command got executed after waiting for 1 minute. (Please note that it waited for 1 minute because there was a session blocking it)
Here is the message in ERRORLOG
2014-06-19 09:00:20.850 spid55 An ‘ALTER INDEX REBUILD’ statement was executed on object ‘OnlineIndexRebuild’ by hostname ‘SQLServerHelp’, host process ID 13560 using the WAIT_AT_LOW_PRIORITY options with MAX_DURATION = 1 and ABORT_AFTER_WAIT = BLOCKERS. Blocking user sessions will be killed
2014-06-19 09:01:21.780 spid55 An ABORT_AFTER_WAIT = BLOCKERS lock request was issued on database_id = 7, object_id = 245575913. All blocking user sessions will be killed.
2014-06-19 09:01:21.780 spid55 Process ID 53 was killed by an ABORT_AFTER_WAIT = BLOCKERS DDL statement on database_id = 7, object_id = 245575913.
If we go back and look at first image, it was SPID 53 which had locks held due to HoldLock hint and hence killed. Here is what we would see if try to execute the query in the session which was killed.
Msg 0, Level 20, State 0, Line 0
The connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. No attempt was made to restore the connection.
Also notice the time gap between green highlighted time. This means whenever such request is submitted, the message is written into ERRORLOG. And if sessions are killed, its also logged in ERRORLOG.
We will see more demos in next part of MLP.