SQL Server 2014 Managed Lock Priority for Partition Switch and Online Reindex

  • Article

Online reindex and partition switch have both been around for a while now, however one of the biggest issues which still exists is they both require a schema modification lock which is very unfriendly with other locks.    

From the lock compatibility matrix you can determine that a Sch-M lock is in conflict with everything. It is not difficult to determine that if a transaction comes along after the Sch-M has been requested you may end up with a pretty significant blocking chain. If you have a table that has a significant OLTP workload, reindexes and partition switch can be a troublesome operation to schedule.

SQL 2014 addresses this with Managed Lock Priority (MLP). MLP creates a low priority queue for both partition switch and for online index operations, MLP will not work with any other features in SQL 2014. You will notice new WAIT_AT_LOW_PRIORITY syntax for ALTER TABLE and for ALTER INDEX. 

Here is what the result is when you use the new features of the syntax;

 

Using AdventureWorksDW2012 the syntax below will rebuild the AK_DimSalesTerritory_SalesTerritoryAlternateKey index on DimSalesTerritory will waiting for up to 15 minutes, then if the Sch-M lock cannot be gained, the DDL statement will terminate itself. If the Sch-M lock can be taken immediately the statement will execute without wait.

 

ALTER INDEX AK_DimSalesTerritory_SalesTerritoryAlternateKey
ON DimSalesTerritory REBUILD
WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY (MAX_DURATION = 15 minutes, ABORT_AFTER_WAIT = SELF )))

 

The following statement will perform an online rebuild of just partition 5 wait 0 minutes (execute immediately) and kill all blockers. You will also notice that a single partition can now be rebuilt online as well. While the AdventureWorksDW2012 does not partition the DimSalesTerritory table, this is the syntax that would be used.

ALTER INDEX AK_DimSalesTerritory_SalesTerritoryAlternateKey
ON DimSalesTerritory REBUILD PARTITION = 5
WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = BLOCKERS)))

Since the DDL is waiting in a low priority lock queue any new transactions that come in after the DDL was submitted will not be blocked behind the DDL. 

One little nugget to keep in mind is that of you use the MAX_DURATION, this will prevent the transaction log from truncating from the point the DDL was submitted until it is executed. For example, you can set the MAX_DURATION up to 71,582 minutes (1,193 hours or 49 days) unless you have the transaction log drive space to support 49 days’ worth of transactions, you might want to keep the MAX_DURATION a bit lower.

For a deeper dive and to see the new waittypes introduced for this feature review Improved Application Availability During Online Operations in SQL Server 2014.