Help: SQL Server

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.