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 24×7 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.
