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.