In earlier two blogs (Part 1 and Part 2) we have seen various new options added to support MLP in online Index rebuild using ALTER INDEX. Same holds true for ALTER TABLE … SWITCH PARTITON as well. In this blog, we would see new enhancements done in DMVs and Extended Events support these options.

Enhancement in DMVs. As per books online, request_status column in sys.dm_tran_locks has three new values added to support this feature. LOW_PRIORITY_CONVERT, LOW_PRIORITY_WAIT and ABORT_BLOCKERS. In the similar way, the wait_type column in sys.dm_os_wait_stats has new values, LOW_PRIORITY and ABORT_BLOCKERS.

In previous blog, we have seen enhancements done in ERRORLOG logging. If you are familiar will the “KILL” command in SQL Server, you must be aware that this is logged into ERRORLOG as well. In the same fashion, if any session is aborted due to ALTER DDL command, it would also be logged in ERRORLOG. We would get information about hostname, ObjectName and session ID which I have put below for reference.

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.

Enhancement in xEvents Let’s look at same example of online index rebuild which we have used, in earlier blog, to learn more about extended events added to support monitoring of managed lock priority feature. There are three new events which have been introduced which will help us in diagnostics.

1. ddl_with_wait_at_low_priority – this event is fired when a ALTER INDEX / TABLE DDL statement is executed using the WAIT_AT_LOW_PRIORITY option.
2. lock_request_priority_state – The priority state of a lock request.
3. process_killed_by_abort_blockers – this event occurs if we chose ABORT = BLOCKERS and a process is killed due to that. This event would have information about who was killed by whom, what the was DDL fired etc.

Let’s create an extended event session and capture some data by reproducing the scenario. If you are comfortable with T-SQL then use below.

CREATE EVENT SESSION [MLP] ON SERVER 
ADD EVENT sqlserver.ddl_with_wait_at_low_priority(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.lock_request_priority_state(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.process_killed_by_abort_blockers(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text)) 
ADD TARGET package0.event_file(SET filename=N'MLP')
WITH (STARTUP_STATE=OFF)
GO

You can also use “New Session Wizard” as shown below and make sure that you choose new events which are listed above.

Let’s start the session first.

ALTER EVENT SESSION [MLP] ON SERVER STATE=START

Let’s create database (MLPDemo), table (OnlineIndexRebuild) and populate it. Refer earlier blog for script. I have executed below T-SQL and as per my server, SPID 60 is running select statement which is going to block ALTER INDEX DDL command.

BEGIN TRAN 
SELECT count(*) 'Count'
FROM   OnlineIndexRebuild (HOLDLOCK) 
WHERE  iID <= 1000  

Select @@SPID '@@SPID'
-- ROLLBACK TRAN

In another session, we would now execute ALTER INDEX DDL.

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 = BLOCKERS ) )
)

and as expected it went to executing state and got blocked by 60. After 1 minute, SPID 60 was killed to complete rebuild process which was started by SPID 64. Here is what is logged in MLP session which we created using T-SQL.

At row # 1, we started DDL command and “ddl_with_wait_at_low_priority” event is fired. This event captured abort_after_wait column as “BLOCKERS” and also shows object_name. Then we can see next event lock_request_priority_state where it shows the lock mode. The process was able to acquire schema shared (SCH_S) lock, intent shared lock (IS) and shared (S) lock. But when it came for schema-modification (SCM_M) lock (which is after build phase is complete) it waited (row #6). Exactly after MAX_DURATION (notice time gap between row # 6 and 7) the time got expired and “abort blockers” action was taken (row 8). Due to that we are seeing “process_killed_by_abort_blockers” and in “killed process id” column shows as 60 as it’s the session which was doing Select (refer image # 2)

Below is the same profiler with ABORT_AFTER_WAIT = NONE (see row # 1 below). Notice below that after expiration on the time (row # 7 below) the state was “Normal Priority” as oppose to Abort Blockers. Nothing was killed here.

You can check yourself and find what would happen if we give ABORT_AFTER_WAIT = SELF.

At this point, we have covered various aspects of MLP. It’s time to cover the limitations and good practices about this feature. Below are some limitations.

• One of the limitation which we should keep in mind (and it’s obvious as well) that even if we specify kill blockers, SQL Server would not kill any system transactions to get locks. SQL Server would kill only user blocking sessions. There could be situations where system transactions (ghost cleanup, shrink and others) is causing blocking as they also acquire some locks and those would not be killed by SQL Server.
• Another limitation is with AlwaysOn and Replication. The low priority queue are only available on primary replica (in availability group) or publisher (in replication) . If DDL has to move and execute (via data movement) on secondary replica in AlwaysOn Availability Group or Subscriber in replication – it would always run in normal queue.
• SWITCH PARTITION can only be used for between two tables or indexed views.
• SSMS doesn’t show the options for MLP. SMO and PowerShell can be used though.

We also need to keep in mind that MAX_DURATION setting can affect the transaction log flush. If you decided to choose a big number, make sure that you have enough disk space to hold that much of transaction log because it can’t be flushed/truncated. The size of transaction log can effect database recovery, replication performance, availability group performance etc. So it’s always good if you can predict how much time OIR would take and manage the space accordingly.

I truly hope that this blog has covered the pending things about this new feature, managed lock priority. We would continue the series and cover other features as well.

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.