Category Archives: SQL Server 2014 CTP1

Concurrency Control in the In-Memory OLTP Engine

We believe that the In-Memory OLTP engine advances the industry state of the art with respect to concurrency control. The main reason for this advancement is due to the combination of lock free algorithms and the row-versioned architecture of the engine.

This post examines what we precisely mean when we describe the In-Memory OLTP engine as being ‘lock free’, both in abstract terms but more importantly in terms of impact on user workloads.

Let’s start with a brief definition capturing the core attributes implied by the term ‘lock free’ in In-Memory OLTP:

“At steady state, transaction carrying threads executing in the context of the In-Memory OLTP engine are designed to require no blocking operation.”

Element by element, this definition implies the following exclusions:

  • Steady state – we explicitly do not claim that execution will be lock free when the system ramps up or shuts down or when it goes through significant fluctuations in the workload. In particular for instance, the In-Memory OLTP engine allocates large pages of memory from the SQL Server host (and implicitly from the host OS). Clearly these operations will acquire locks since neither the OS nor SQL Server in general are ‘lock free’. The engine does implement its own lock free small block allocator (currently capped at 8K blocks), so once a page is obtained from the host process, future block allocations will be serviced without the need to acquire any subsequent locks, but the workload ramp-up – characterized as it is by page acquisition from the host – will incur some locking overhead.
  • Transaction carrying threads – we use this term to differentiate between threads doing work on behalf of the user workload from threads doing work on behalf of the system itself. Examples of system (or worker) threads include threads involved in checkpoint, some GC maintenance, file allocation – and so on. Since these threads are never visible to the end user and since their responsiveness does not impact the end user workload, execution of these system threads is not designed to be entirely lock free.
  • Execution in the context of the In-Memory OLTP engine. We use this phrase to distinguish between execution that takes place within the context of the engine and execution that takes place in the SQL host, in the OS or even in the client stack. One example triggering this exclusion was presented above: acquiring large pages from the SQL host will occasionally acquire locks. Similarly, file access in the context of checkpoint or logging will incur OS level locking; waiting for log IO before acknowledging a transaction commit will also incur some form of waiting; clearing of transaction dependencies - which is required for correctness - will also cause a thread to stall, and so on. These examples all demonstrate that locking is still present in SQL Server running In-Memory OLTP. However, it also remains true that we have eliminated all locks or waits within the engine itself – and that is precisely the location where locks (or in our case their absence) have the most impact on the end user.

Once we agree on the definitions above, we note that in the context of any database system, locking behavior always implies two distinct considerations: we use locks for logical data protection (also known as transactional isolation) and for physical data protection.

One example of logical data protection is row payload protection. Row payload protection means that while a user modifies a row another user does not modify the same row. This is what logical (transactional) locks are used for in traditional SQL Server (row locks, page locks, database locks and even app locks can be used for the same purpose). With In-Memory OLTP we rely on row versioning to ensure that row content is never modified by two users at the same time – in other words we don’t use transactional locks because we never update data in place. If two or more users try to update the same row at the same time, one will succeed while the others will fail due to a write/write conflict. Note that this can be achieved without any locking by creating a new version for each user and then trying to install each version atomically in the same index location (by using ‘InterlockedCompareExchange’ – or ICX). Out of the multiple users that try to update the same row at the same time, one will succeed and the others will fail this hardware level ICX operation – and that translates directly into the behavior reported back by the system.

Physical data protection is a different problem altogether. In traditional database systems (SQL Server but also more broadly in the industry) we protect internal data structures via spinlocks and latches. Broadly speaking (and oversimplifying), the difference between spinlocks and latches is that a thread trying to acquire a spinlock will spin when the lock is found to be currently held by a different thread whereas for latches the acquiring thread will yield its CPU time back to the OS if the latch is found to be currently held by a different thread. Given this difference, traditional SQL uses latches for waits that could take a while (getting a page in the buffer pool) while spinlocks are used for short term waits (waiting for a memory only linked list traversal for instance). However, both locks and latches have in common the fact that they are ‘region locks’. We use the term ‘region lock’ to describe the mechanism used to protect a region of code or data structures against simultaneous thread access. Region locks implement an ‘Acquire/Release’ pattern – where a lock (either latch or spinlock) is first acquired, the protected region executes, and then the lock is released. The problem with that approach is that it does not scale. In a system with many cores or very high concurrency the region being protected becomes a bottleneck. For instance, in Windows Server the scheduling quantum is around 180ms, so if a thread that holds a spinlock gets preempted, that spinlock will be held for 180ms regardless of how short the protected region would be otherwise. There are other negative side effects from region locks because they all involve writing to shared cache lines even when the lock is acquired for read access – which becomes problematic in many-core and NUMA systems or under high concurrency. The lock free engine avoids these issues by implementing all operations in an atomic fashion. In other words, the In-Memory OLTP engine does not define any protected regions in transaction executing paths. The data structures and algorithms are structured such that state transitions are atomic and therefore are not subject to the whims of the scheduling subsystem. In addition, many operations are done without any shared-cache line modifying instructions at all (meaning that the entire operation does not even use ICX but rather touches in write mode only cache lines that are private to the local processor) which improves scalability and concurrency to the limits supported by the hardware.

The prime example of that is index traversal: the engine walks both hash and range indices without any locks or ICX instructions. In the process the engine detects if the underlying data structure has changed in a manner that could invalidate the current traversal and re-attempts the small portion of the traversal that was invalidated. These re-traversal are extremely rare even at very high concurrency (in the early days of the In-Memory OLTP engine we have measured under 100 retries for million tx / sec workload) – so their measurable performance impact is virtually non-existent. When the engine needs to modify one of these lock free data structures it does so via ICX – which makes the modification visible atomically.

A careful observation of current hardware trends presents overwhelming evidence that the number of available cores in any given computing platform is likely to rise with time. In this context, concurrency control that is at its core lean and efficient is absolutely crucial to achieving first-rate performance. With In-Memory OLTP we have taken these insights to heart and built an engine that relies on no locks, waits, latches or other synchronization primitives to ensure consistency of execution. We believe this approach removes locking and latching as a concern for even our most demanding users.

For more information, download SQL Server CTP1and get started today, or see more blogs in the series introduction and index here.

In-Memory OLTP Programmability: Concurrency and Transaction Isolation for Memory-optimized Tables

The new In-Memory OLTP feature introduces memory-optimized tables and natively compiled stored procedures in SQL Server 2014, to optimize performance and scalability of OLTP workloads. To benefit from this technology in an existing SQL Server application, you need to migrate the performance critical tables and stored procedures to memory-optimized tables and natively compiled stored procedures respectively. End-to-end migration of tables and procedures was discussed in the blog post here. In this post we focus on the implications of the new transaction isolation model to application migration.

Transaction isolation levels for memory-optimized tables are implemented using optimistic concurrency control and therefore are lock-free. This means that SQL Server does not use locks to guarantee consistency in case multiple transactions attempt to access the same data. Instead, SQL uses a combination of snapshot-based isolation and conflict detection to guarantee consistency. For more details on transactions with In-Memory OLTP see the Books Online topic here. We will discuss the principles and the architecture of the lock-free engine for memory-optimized tables in more detail in a later post.

Snapshot-based isolation

All transactions read rows from memory-optimized tables as of a single point in time, usually the start of the transaction. This means that a running transaction does not observe changes made by other, concurrent, transactions that commit while the transaction is running.

Consider, for example, two transaction, tx1 and tx2, and a single memory-optimized table Tab, with a single row r1. The following table shows an example in which the transaction tx1 reads from the table, and tx2 inserts a row. The first column indicates the time; the second column indicates the contents of the table Tab.

Time

Contents of Tab

Operations in tx1

Operations in tx2

100

r1

BEGIN TRAN

 

101

r1

BEGIN TRAN

102

r1

SELECT FROM Tab

* returns (r1)

INSERT Tab VALUES (r2)

103

r1, r2

COMMIT

104

r1, r2

 

105

r1, r2

SELECT FROM Tab

* returns (r1)

 

106

r1, r2

COMMIT

 

Notice that at time 105, even though the table Tab contains two rows (r1, r2), the transaction tx1 only sees r1. All read operations in tx1 are executed as of time 100.

Snapshots in transaction isolation are not new in SQL Server. SNAPSHOT isolation has been available for disk-based tables since SQL Server 2005. However, the default isolation level for disk-based table remains read committed, and higher isolation levels such as REPEATABLE READ and SERIALIZABLE do not use snapshots. Memory-optimized tables use snapshots for all transaction isolation levels, including SNAPSHOT, REPEATABLE READ, and SERIALIZABLE.

You need to keep this in mind when migrating a table to memory-optimized, in particular when using READ COMMITTED isolation today. If there are no dependencies between concurrent transactions at the application level, in most cases you can use SNAPSHOT isolation on the migrated tables without changing the application. However, if there is a dependency, and the application relies on tx1 seeing the changes made by tx2, you need to make further changes to the app to handle snapshot-based isolation. For example, you could commit tx1 after the initial read operation at time 102, and start a new transaction, which would be able to see rows inserted by tx2.

For guidelines on transaction isolation levels with memory-optimized tables, and how to migrate apps that use the READ COMMITTED isolation level today, see Books Online.

Conflicts

Rather than taking locks to prevent concurrent access to a table, memory-optimized tables use conflict detection to enforce isolation of modifications, and to guarantee the higher isolation levels REPEATABLE READ and SERIALIZABLE.

If two concurrent transactions attempt to update the same row, one of the transactions will fail and roll back. Consider the following example, where two transaction attempt to update the same row r1.

Time

Operations in tx1

Operations in tx2

100

BEGIN TRAN

 

101

BEGIN TRAN

102

UPDATE r1 – success

103

UPDATE r1 – error – tx1 is aborted

104

 

COMMIT – success

In this example, tx2 successfully updates r1 and later successfully commits. Transaction tx1 attempts to update r1 after it has already been updated by tx2. The update fails, and transaction tx1 is aborted. The first writer, in this case tx2, always wins. Transaction tx1 will need to be retried. In contrast, with READ COMMITTED isolation in disk-based tables, tx2 would take a lock when it updates r1. Transaction tx1 would block and wait until tx2 commits and the lock is released. At that point, i.e. after time 104, tx1 would proceed with the update to r1.

Validation of REPEATABLE READ and SERIALIZABLE isolation is done during commit processing. If SQL Server finds that the desired isolation level has been violated, the transaction is aborted at this time.

As conflicts cause transaction abort, transactions may need to be retried. For this reason, transactions that modify memory-optimized tables require logic to retry the transactions on failures. This retry logic can be implemented either in the client application, or through a wrapper stored procedure on the server. For more details on retry logic and a sample stored procedure implementing retry logic, see Books Online.

Download SQL Server CTP1and get started today, or see more blogs in the series introduction and index here!

New AMR Tool: Simplifying the Migration to In-Memory OLTP

As we have addressed in previous blog articles, the transition into SQL Server In-Memory OLTP is not as simple as flipping a switch. One of the design principles of In-Memory OLTP is that you would only need to make surgically precise migrations for your app to reap the most benefit from In-Memory OLTP for the least amount of work. With that in mind, we recognize that it is sometimes difficult to identify the appropriate targets for you to take advantage of In-Memory OLTP. To solve this problem, we have devised and integrated the AMR (Analyze, Migrate, Report) Tool into the SQL Server 2014 CTP 1 Management Studio to assist you in your transition to In-Memory OLTP.

The AMR Tool works well if you are:

  • Uncertain which tables and stored procedures you should migrate into In-Memory OLTP

The AMR Tool’s analysis process is built with this goal in mind. With its analysis reports, you can easily identify the tables and stored procedures that can reap the most benefit when moved into In-Memory OLTP.

  • Seeking validation for your migration plans

You may understand your workload extremely well. By reading about In-Memory OLTP’s characteristics and playing with it a bit, you know which objects you want to move into this new, efficient engine. The AMR Tool can help you validate your knowledge and support your decision with data straight from your workload instances.

  • Evaluating work needed for the tables and stored procedures you plan to migrate

You know from experience and data what objects you need to migrate. You need a list of work items so that you can carefully plan your migration effort, estimate the amount of time needed for each object, and give a completion date of the migration. The AMR Tool can help you by identifying the incompatibilities in these objects and even do some of the work for you.

As released in SQL Server 2014 CTP1, the AMR Tool consists of the following components:

  • A set of data collectors, leveraging the existing Data Collection framework in SQL Server

This set of data collectors are ultra-lightweight and can easily attach to your SQL Server instances (in production or otherwise) as long as they are running SQL Server 2008 or later. They collect performance data points from DMVs in your SQL Server instance and deposit them to a designated database called a Management Data Warehouse for the analysis reports.

  • A set of Management Data Warehouse analysis reports

This set of reports ingest data from the Management Data Warehouse into which the AMR data collectors have uploaded data. They use these data points to analyze your workload patterns and provide recommendations for migration based on:

  • your workload’s access characteristics,
  • your workload’s contention statistics, and
  • your workload’s stored procedure CPU usage

The overview reports are designed so that it is easy for you to identify the tables and stored procedures with the best return on investment as illustrated below:

Recommended Tables Based on Usage

The reports will provide a set of recommended tables or stored procedures for each basis. For example, if your workload has a contention issue, you may choose to follow the recommendations based on contention statistics and disregard the others.

Since natively-compiled stored procedures cannot access disk-based tables at this time, we highly recommend that you make your table migrations first. When you are already taking advantage of the multitudes of benefits memory-optimized tables provide, you can run the AMR tool again on a second pass and select stored procedures you want to compile with In-Memory OLTP.

The setup and teardown of the AMR Tool is extremely simple:

  • First, you need to connect to a SQL Server 2008 or later instance using SQL Server 2014 Management Studio. You do not need a SQL Server 2014 instance to proceed.      

  • Next, you need to establish a Management Data Warehouse (MDW) somewhere. This MDW can reside on any instance on the same network as the workload instance. It could even be your desktop computer.

To create a MDW, go to “Object Explorer”, expand “Management”, right-click “Data Collection”, mouse over “Tasks”, and choose “Configure Management Data Warehouse”. The wizard will guide you through the task of creating a MDW for use.

  • After you have a MDW, you will need to establish data collectors on your workload instance. The AMR Tool requires the set of data collectors to situate on your workload instance in order to obtain and store the performance data it needs.

To install data collectors on an instance, go to “Object Explorer”, expand “Management”, right-click “Data Collection”, mouse over “Tasks”, and choose “Configure Data Collection”. In the wizard, check “Transaction Performance Collection Set” in the list of collection sets.

  • Once you have finished your migration work and do not want to use the collectors any more, you can remove them easily as well. We provide a graphic user interface for tearing down both the system data collectors the AMR data collectors in SQL Server 2014 Management Studio.

To tear down the AMR data collectors if you don’t want them running any more, go to “Object Explorer”, expand “Management”, right-click “Data Collection”, mouse over “Tasks”, and choose “Clean up Data Collection”. In the wizard, check “Transaction Performance Collection Set” in the list of collection sets.

If you are thinking about In-Memory OLTP for your app, why not give the new AMR Tool a try today? We hope that you’ll like it!

SQL Server 2014 CTP1 is available for download here, or for more information please see the introduction and index to this complete series.

Solving Session Management Database Bottlenecks with In-Memory OLTP

As we started working with early adopters on the SQL Server 2014 In-memory OLTP engine (project codenamed Hekaton) one of the first scenarios we ran into involved a session management database in SQL Server. The application was a high volume website storing ASP.net Session State in the database and running into latch contention and locking problems due to the number of concurrent users supported. As this database was a central, and mission critical, single point of scale for all web traffic it had become a performance bottleneck for the business.

As we continued to work with other customers, a number had mission critical databases storing some form of session state information. Moving the critical tables and workload suffering from latch contention and therefore scale issues into the In-memory OLTP engine has produced excellent results. Below we provide further information around this common application scenario which has seen success in being migrated to In-memory OLTP.

Architecture Characteristics:

Typically for session state related architectures there exists a large (scale-out) number of webservers with a few tables responsible for servicing the number of users on the site. The workload pattern would be:

  1. As users come into the site, information is inserted into these tables and then a significant amount of updates to their session information may occur with point-lookup queries.
  2. As user scale and threads increase the amount of latching and locking on these tables increases which becomes a barrier to scale. As these tables are in the critical path of the web user experience (typically linking login to state info which can be displayed on a website) the time it takes to update and query these tables is critical to the user experience.

span style="text-decoration: underline;">Bottlenecks Experienced:

em>Latch contention:In some cases latching due to monotonical incremental keys (last-page insert) was a barrier to scale, other latching on intermediate pages also became an issue. This only allowed a certain amount of user traffic to be active at one time. The ability to scale and fully utilize the hardware resources were application bottlenecks.

Throughput/Latency: In other cases, while the application requirements were still around throughput and the number of concurrent users, the latency per business transaction also had an effect on the overall experience. Here reducing latency and increasing throughput was a primary goal.

In-memory OLTP Migration Considerations:

  1. Memory-optimized tables do not suffer from latch contention as the core In-memory OLTP engine uses lock-free algorithms and structures which have no latching. Migrating the tables with latch hotspots to memory-optimized tables eliminated this scale bottleneck. Of note, the optimistic concurrency model is a new paradigm which applications will have to consider.
  2. Durability of data. The In-memory OLTP engine supports full ACID compliance and table durability by logging content into the SQL Server database transaction log. It also provides the table level configuration for creating tables as SCHEMA_ONLY, or non-durable. On recovery only the schema will be recovered and data lost. If the data in these tables is transient, for example a staging table where if the data was lost it could be easily recreated, creating the table as SCHEMA_ONLY can significantly reduce the IO impact of the workload, dependency on disk drives, and improve overall transaction throughput.
  3. In some cases the tables contain large object (LOB) datatypes or a row size > 8060. Memory-optimized tables do not support LOB columns and only row sizes under 8060 bytes. Application design patterns for this would include either splitting tables into memory-optimized and disk based tables containing the LOB data or larger datatype columns or splitting up the large rows into multiple rows. For examples of this please reference: Implementing LOB Columns in a Memory-Optimized table. A primary consideration would be how often the LOB data is accessed and how critical it is to the overall performance. If frequent, as is the case with session information, splitting the rows into multiple rows in the memory-optimized table is typically best for performance. In a number of cases, we have seen users have the ability to split a single large row row into multiple rows (i.e. varbinary) and then “re-assemble” the data out to the application as needed.
  4. Performance of Transact-SQL calls. Reducing the time of each business transaction was also an important goal in terms of overall performance. Being able to utilized memory-optimized tables and move the T-SQL code into native compiled stored procedures increased performance and also allowed for reducing the latency of a single transaction execution, critical factors in improving the overall application scale. A number of customers used native compiled procedures for their implementation.

Results:

As I mentioned we were testing with some customers with this scenario. Here are some early results on what customers have achieved:

  1. As you may have observed from our earlier guest blog by Rick at bwin located here, using In-memory OLTP was able to provide them with gains of 16x (from 15,000 to 250,000 batch requests/seq) with no application code changes. For further information reference our earlier blog post on the subject.
  2. Another customer implemented their own session state management and has achieved gains of 8x (non-durable tables) and 3x (durable tables) in their current testing. Adding additional users at scale is expected to help improve performance to even greater gains.

In conclusion, do you have a session management database which is becoming the bottleneck for your application? Unable to scale-up and fully utilize your CPU and hardware resources? If so, hopefully this helps provide you with a solution!

Interested in more patterns where we have seen success with SQL Server 2014 In-memory OLTP? Stay tuned for more blogs around patterns and a follow-up whitepaper which will include more scenarios and considerations to help you migrate to SQL Server 2014 In-memory OLTP. 

SQL Server 2014 CTP1 is available for download here, or you can find the index to the complete blog series here.

Improved Application Availability During Online Operations in SQL Server 2014

SQL Server 2014 introduces enhancements to two common database maintenance operations namely Partition Switching and Online Index Rebuild which greatly increases enterprise application availability by reducing maintenance downtime impact.

The following features are available in SQL Server 2014 CTP1 which can be downloaded here:

  • Managed Lock Priority for Table Partition Switch & Online Index Rebuild - This feature allows customers to manage the priority of table locks acquired by partition SWITCH and Online Index Rebuild (OIR), thereby allowing customers to mitigate any negative performance impact of these locks on the primary workload on the server.
  • Single Partition Online Index Rebuild - This features allows customers with large partition tables to rebuild index online for individual partitions, thereby increasing application uptime.

Partition Switching and Online Index Rebuild are executed via ALTER TABLE and ALTER INDEX respectively. Current functionality for partition switching (SWITCH) and online index rebuild (OIR) acquires an exclusive table Sch-M lock during the DDL operation impacting the database workload (DML or query operations) running concurrently and using the affected table. In case of OIR two locks are required, a table S-lock in the beginning of the DDL operation and a Sch-M lock at the end. In case of SWITCH two Sch-M locks are required one for the target and one for the destination table.

In order to execute the DDL statement for SWITCH/OIR, all active blocking transactions running on a particular table must be completed. Conversely, when the DDL for SWITCH/OIR is executed first, this will block all new transactions that require locks on the affected table. Although the duration of the lock for SWITCH/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.

Managed Lock Priority

This new feature will allow a database administrator (DBA) to manage the Sch-M lock for SWITCH and S-lock/Sch-M lock for OIR. The DBA will now have the ability to specify one of different manageability options to handle the DDL locks of SWITCH/OIR:

  • Enforce the SWITCH or OIR  ( kill all blockers immediately or after a specified wait time /(MAX_DURATION =n [minutes])  expires)
  • Wait for blockers and after the wait time (MAX_DURATION)  expires place the lock in the regular lock queue ( as it does today)
  • Wait for blockers and after the wait time expires (MAX_DURATION)  exit the DDL (SWITCH/OIR) w/o any action

It is important to notice that in all 3 cases if during the wait time ((MAX_DURATION =n [minutes])) there are no blocking activities, the SWITCH/OIR lock will be executed immediately w/o waiting and the DDL statement will be completed

Syntax

ALTER TABLE and ALTER INDEX DDL have been extended to support the following new syntax to specify managed lock priority options

<low_priority_lock_wait>::=
   {

     WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time>[MINUTES],
 
          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
    } 

NONE - current behavior

SELF - abort DDL after MAX_DURATION has elapsed

BLOCKERS – abort user blockers after MAX_DURATION has elapsed

When using the new syntax to manage lock priority, it is important to note the following:

  • For Partition Switching
    • Sch-M lock is required for the two tables (source and destination).
    • User transactions blocking SWITCH DDL will be killed for the source and destination tables
    • For Online Index Rebuild
      • MAX_DURATION applies to every lock requested during the OIR DDL statement
      • The time will be reset for every S & Sch-M lock
      • Only Sch-M lock conflict for read only workloads.

In both cases, if ABORT_AFTER_WAIT=BLOCKERS is specified:

  • The login executing the DDL needs to be granted ALTER ANY CONNECTION permission in addition to the standard permissions for executing ALTER TABLE or ALTER INDEX.
  • All user transactions that block SWITCH (both source and target tables) or OIR DDL operation will be killed.

Example

In the example below partition 1 from Production.Transactionhistory will be switched with partition 1 from staging table. The DDL will wait for up 60 minutes for the Sch_M lock on the table as specified by MAX_DURATION parameter and if the time elapses without the lock being acquired successfully either at the source table or at the destination table, the DDL will abort itself.

ALTER TABLE Production.Transactionhistory SWITCH PARTITION 1 TO Production.Transactionhistory_staging PARTITION 1

WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION= 60, ABORT_AFTER_WAIT=SELF))

In the example below index PK_TransactionHistory_fragment_TransactionID for table Production.Transactionhistory is rebuilt online. The DDL will wait for up 300 minutes each for both the S lock (in the beginning) and the Sch_M lock (at the end) on the table as specified by MAX_DURATION parameter and if the time elapses without the lock being acquired successfully, the DDL will abort any blocking queries.

ALTER INDEX PK_TransactionHistory_fragment_TransactionID ON Production.Transactionhistory

REBUILD

WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION= 20, ABORT_AFTER_WAIT=BLOCKERS)));

Diagnostics

Errorlog

When a DDL with new syntax is executed, and sessions are aborted, abort session diagnostics are logged in SQL error log as shown below. Note the difference in timestamp between the ALTER INDEX execution and the ABORT matches the MAX_DURATION specified by DDL (1 minute):

2013-07-26 11:33:40.92 spid57      An 'ALTER INDEX REBUILD' statement was executed on object 'Production.Transactionhistory' by hostname 'YSQLSERVER2014', host process ID 14060 using the WAIT_AT_LOW_PRIORITY options with MAX_DURATION = 20 and ABORT_AFTER_WAIT = BLOCKERS. Blocking user sessions will be killed after the max duration of waiting time.

2013-07-26 11:53:40.95 spid57      An ABORT_AFTER_WAIT = BLOCKERS lock request was issued on database_id = 5, object_id = 1207675350. All blocking user sessions will be killed.

2013-07-26 11:53:40.96 spid57      Process ID 55 was killed by an ABORT_AFTER_WAIT = BLOCKERS DDL statement on database_id = 5, object_id = 1207675350.

Extensions to Dynamic Management Views

New columns and waittypes have been added to sys.dm_tran_locks and sys.dm_os_wait_stats Dynamic Management Views which show the status of the request.

sys.dm_tran_locks has additional status values for request_status column  - LOW_PRIORITY_CONVERT, LOW_PRIORITY_WAIT, or ABORT_BLOCKERS

Example: The output below from sys.dm_tran_locks and sys.dm_exec_requests respectively show that the DDL query (session_id 57) is waiting in the low priority queue for Sch_M lock on the table

request_session_id request_mode    request_status

56                 IX              GRANT

57                 Sch-M           LOW_PRIORITY_WAIT

session_id    command              wait_type       blocking_session_id

57         ALTER TABLE  LCK_M_SCH_M_LOW_PRIORITY             56

sys.dm_os_wait_stats shows aggregated statistics on new low priority wait types:

“wait_type” extensions

*_LOW_PRIORITY and  *_ABORT_BLOCKERS

Example:

wait_type                   waiting_tasks_count  wait_time_ms  max_wait_time_ms

LCK_M_SCH_M_ABORT_BLOCKERS               0             0                    0     

LCK_M_SCH_M_LOW_PRIORITY                 1        300000               300000            

New Extended Events

For additional diagnostics to aid in troubleshooting, the following extended events are available:

ddl_with_wait_at_low_priority – This event is fired when a DDL statement is executed using the WAIT_AT_LOW_PRIORITY options

lock_request_priority_state – This event describes the priority state of a lock request

process_killed_by_abort_blockers – This event is fired when a process is killed by an ABORT = BLOCKERS DDL statement

Single Partition Online Index Rebuild

In SQL Server 2012 and older versions, one had the option of rebuilding the index for an entire table online or rebuild the index at a partition level offline. Both of these had the consequences of partition availability in case of offline rebuild or resource usage (CPU, memory and disk) if the entire table is rebuilt online. This resulted in workload slowdown or timeouts and affected throughput and availability of a database.

Single partition online index rebuild overcomes the above limitations and provides the ability to rebuild an index (or indexes) for a table at a partition level granularity online.

By enabling online index rebuild at partition level, the following benefits are achieved:

  • Table will be accessible for DML and query operations, except for short term locks requested in the beginning and at the end of the index rebuild
  • Managed Lock Priority functionality available for single partition online index rebuilds.
  • Significant benefit for all customers who cannot afford a downtime for mission-critical tables
  • Resource saving – (CPU, memory and disk space) by rebuilding only a single partition online instead of rebuilding the entire index online
  • Log space usage reduced

Syntax

ALTER INDEX DDL has been extended with following additional switches. The important point to note here is that the ONLINE keyword and PARTITION keyword exists in older versions but could not be used together.

<single_partition_rebuild_index_option> ::=
{
    ….  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
 }
  <low_priority_lock_wait>::=
   {

     WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time>[MINUTES], 
     ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
    } 

Example

Let us examine the current fragmentation for PK_TransactionHistory_fragment_TransactionID for table Production.Transactionhistory.

Before Rebuild:

database_id   index_id      partition_number     avg_fragmentation_in_percent

5                    1                    1                               40.1174168297456

5                    1                    2                               20.7729468599034

5                    1                    3                               0.359712230215827

5                    1                    4                              33.4782608695652

5                    1                    5                              19.8979591836735

To rebuild index just for partition 4 above, you can execute a DDL as shown below. The DDL will wait for up 120 minutes each for both the S lock (in the beginning) and the Sch_M lock (at the end) on the table as specified by MAX_DURATION parameter and if the time elapses without the lock being acquired successfully, the DDL will abort any blocking queries.

ALTER INDEX PK_TransactionHistory_fragment_TransactionID ON Production.Transactionhistory

REBUILD PARTITION=4

WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION= 120, ABORT_AFTER_WAIT=BLOCKERS)));

After Rebuild: You can see that only partition 4 was rebuild and its fragmentation almost eliminated.

database_id   index_id      partition_number     avg_fragmentation_in_percent

5                    1                    1                                40.1174168297456

5                    1                    2                                20.7729468599034

5                    1                    3                                0.359712230215827

5                    1                    4                                0.523560209424084

5                    1                    5                               19.8979591836735

Diagnostics

The following diagnostic enhancements are available to aid in troubleshooting.

Query Plan

For ALTER INDEX the query plan shows the partition being rebuilt in the Constant Scan operator

Rows       Executes  StmtText

0              1              insert [Production].[TransactionHistory] select * from [Production].[TransactionHistory] with (index = 1)

0              1                |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1009]))

1              1                     |--Constant Scan(VALUES:(((4))))

0              1                     |--Online Index Insert(OBJECT:([AdventureWorks].[Production].[TransactionHistory].[PK_TransactionHistory_fragment_TransactionID]))

27483      1                          |--Clustered Index Scan(OBJECT:([AdventureWorks].[Production].[TransactionHistory].[PK_TransactionHistory_fragment_TransactionID]), SEEK:([PtnId1000]=[Expr1009]) ORDERED FORWARD)

Extended Event

Two data fields have been added to  sqlserver.progress_report_online_index_operation extended event:

partition_number: shows ordinary number of the partition being built

partition_id :  shows ID of the partition being built

Manageability

In addition to the DDL extensions, SMO and PowerShell interfaces are available for both Managed Lock Priority and Single Partition Online Index Rebuild.

In SSMS, manage partition wizard for partition switching will script out the new syntax for managed lock priority with default values.

Conclusion

Both Managed Lock Priority and Single Partition Online Index Rebuild provide a database administrator options to define and to manage the maintenance of databases in large mission critical 24x7 workloads In addition, this enhances enterprise application availability by reducing maintenance downtime as well as resource consumption by common maintenance operations.

SQL Server 2014 CTP1 is available for download here. For more information on the new SQL Server 2014 syntax for using managed lock priority and single partition online index rebuild, see the ALTER TABLE (Transact-SQL) and ALTER INDEX (Transact-SQL) topics in SQL Server 2014 Books Online.

See additional posts on SQL Server 2014 In-Memory OLTP by visiting the blog series introduction and index