Manage Your Locks–New Managed Lock Priority Feature In SQL Server 2014

Imagine you have to perform a typical database management operation such as Online Index Rebuild or Partition Switch on a very busy OLTP database. Or you are about to deploy a change to a column on the table, yet you cannot take an outage completely. Your enemy here is something known as exclusive Schema modification lock – Sch-M lock during the DDL operation impacting the database workload running concurrently and using the affected table. In case of Online Index Rebuilds two locks are required, a table S-lock in the beginning of the DDL operation and a Sch-M lock at the end. In order to execute the DDL statement for OIR, all active blocking transactions running on a particular table must be completed. Conversely, when the DDL for OIR is executed first, this will block all new transactions that require locks on the affected table. Although the duration of the lock for OIR is very short, waiting for all open transactions on a given table to complete and blocking the new transactions to start, may significantly affect the throughput, causing workload slow down or timeout, and significantly limiting an access to the underlying table(s). This has an impact for 24X7 Mission Critical workloads that focus on maximizing throughput and availability with short or non-existent maintenance window.

SQL Server 2014 introduces another new feature – Managed Lock Priority that may at least provide some assistance here. Now the new parameters are added to ALTER INDEX\ALTER TABLE commands that may give us lock priority choices as we perform Online Index Rebuild and Partition Switch operations.  The syntax to add managed lock priority to ALTER INDEX\ALTER TABLE statement is added in SQL Server 2014 as:

low_priority_lock_wait::= { WAIT_AT_LOW_PRIORITY ( MAX_DURATION = [ MINUTES ] , ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } ) }

Following choices are introduced here:

  • Kill All Blockers immediately or after a specified wait time  as specified by MAX_DURATION parameter and ABORT_AFTER_WAIT parameter.  That would also to give maintenance operations precedence over user transactions and kill blocking user transactions either immediately or after some wait in number of minutes.
  • Exit DDL after wait, therefore opposite of above give precedence to user transaction over maintenance DDL. In this case command we issue would be something like:

ALTER index myindex on mytable REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 30 MINUTES, ABORT_AFTER_WAIT = SELF) ))

The idea behind the SQL Server 2014 solution is sacrificing the deterministic and predictability of a DDL command over having no impact on the user workload. 

More information is available in SQL BOL.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s