Category Archives: Uncategorized

« All posts

Memory Optimized Tables in SQL 2014

by ryltechadmin December 16, 2014

In-Memory OLTP can significantly improve OLTP database application performance. In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for OLTP.
To use In-Memory OLTP, you need to define a heavily accessed table as memory optimized. Memory-optimized-tables are fully transactional, durable, and are accessed using Transact-SQL in the same way as disk-based tables.
A query can reference both memory-optimized tables and disk-based tables. A transaction can update data in memory-optimized tables and disk-based tables. Stored procedures that only refer to memory-optimized tables can be natively compiled into machine code for further performance improvements.
The In-Memory OLTP engine is designed for extremely high session concurrency for OLTP transactions driven from a highly scaled-out middle-tier. To achieve this, it uses latch-free data structures and an optimistic, multi-version concurrency control. The result is predictable, sub-millisecond low latency and high throughput with linear scaling for database transactions.
The actual performance gain depends on many factors, but about 10 times performance improvements are common.
The following table summarizes the most benefited workload patterns by using In-Memory OLTP:

Implementation Scenario 1	Implementation Scenario 2	Benefits of In-Memory OLTP
High data insertion rate from multiple concurrent connections.	Unable to keep up with the insert workload.Primarily append-only store.	Reduce logging.Eliminate contention.
Read performance and scale with periodic batch inserts and updates.	Unable to meet scale-up requirements. High performance read operations, especially when each server request has multiple read operations to perform.	Lower latency data retrieval. Minimize code execution time. Eliminate contention when new data arrives.
Intensive business logic processing in the database server.	Intensive computation inside stored procedures.Read and write contention.Insert, update, and delete workload.	Minimize code execution time for reduced latency and improved throughput.Eliminate contention.
Low latency.	Require low latency business transactions which typical database solutions cannot achieve.	Eliminate contention.Minimize code execution time. Low latency code execution.Efficient data retrieval
Session state management.	High scale load from numerous stateless web servers. Frequent insert, update and point lookups.	Efficient data retrieval. Optional IO reduction or removal, when using non-durable tables Eliminate contention.

In-Memory OLTP will improve programming patterns that include concurrency scenarios, point lookups and workloads in which there are many inserts and updates, and business logic in stored procedures.

Integration with SQL Server means you can have both memory-optimized tables and disk-based tables in the same database, and query across both types of tables.

In SQL Server 2014 there are limitations in Transact-SQL surface area supported for In-Memory OLTP.
In-Memory OLTP achieves significant performance and scalability gains by using:

Algorithms that are optimized for accessing memory-resident data.
Optimistic concurrency control that eliminates logical locks.
Lock free objects that eliminate all physical locks and latches. Threads that perform transactional work don’t use locks or latches for concurrency control.
Natively compiled stored procedures, which have significantly better performance than interpreted stored procedures, when accessing a memory-optimized table.

SQL Server 2014 is designed to improve OLTP database application performance.

« All posts

SQL Server Backup to URL in SQL Server 2014

by ryltechadmin December 16, 2014

Windows Azure Blob storage service enables you to backup SQL Server databases to a URL. The backup and restore functionality are same or similar to when using DISK or TAPE, with a few differences that will be presented in this article.
Security
When creating a container for the Windows Azure Blob storage service, it is recommended you set the access to private. Setting the access to private restricts the access to users or accounts able to provide the necessary information to authenticate to the Windows Azure account.
The user account that is used to issue BACKUP or RESTORE commands should be in the db_backup operator database role with Alter any credential permissions.

Introduction to Key Components and Concepts

The following two sections introduce the Windows Azure Blob storage service, and the SQL Server components used when backing up to or restoring from the Windows Azure Blob storage service. It is important to understand the components and the interaction between them to do a backup to or restore from the Windows Azure Blob storage service.
Creating a Windows Azure account is the first step to this process. SQL Server uses the Windows Azure storage account name and its access key values to authenticate and write and read blobs to the storage service. The SQL Server Credential stores this authentication information and is used during the backup or restore operations.

Windows Azure Blob Storage Service

Storage Account: The storage account is the starting point for all storage services. To access the Windows Azure Blob Storage service, first create a Windows Azure storage account. The storage account name and its access key properties are required to authenticate to the Windows Azure Blob Storage service and its components.
Container: A container provides a grouping of a set of Blobs, and can store an unlimited number of Blobs. To write a SQL Server backup to the Windows Azure Blob service, you must have at least the root container created.
Blob: A file of any type and size. There are two types of blobs that can be stored in the Windows Azure Blob storage service: block and page blobs. SQL Server backup uses page Blobs as the Blob type. Blobs are addressable using the following URL format: https://.blob.core.windows.net//

SQL Server Components

URL: A URL specifies a Uniform Resource Identifier (URI) to a unique backup file. The URL is used to provide the location and name of the SQL Server backup file. In this implementation, the only valid URL is one that points to a page Blob in a Windows Azure storage account. The URL must point to an actual Blob, not just a container. If the Blob does not exist, it is created. If an existing Blob is specified, BACKUP fails, unless the “WITH FORMAT” option is specified.

Here is a sample URL value: http[s]://ACCOUNTNAME.Blob.core.windows.net//. HTTPS is not required, but is recommended.
Credential: A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. Here, SQL Server backup and restore processes use credential to authenticate to the Windows Azure Blob storage service. The Credential stores the name of the storage account and the storage account access key values. Once the credential is created, it must be specified in the WITH CREDENTIAL option when issuing the BACKUP/RESTORE statements.

Limitations

The maximum backup size supported is 1 TB.
You can issue backup or restore statements by using TSQL, SMO, or PowerShell cmdlets. A backup to or restoring from the Windows Azure Blob storage service by using SQL Server Management Studio Backup or Restore wizard is not currently enabled.
Creating a logical device name is not supported. So adding URL as a backup device using sp_dumpdevice or through SQL Server Management Studio is not supported.
Appending to existing backup blobs is not supported. Backups to an existing Blob can only be overwritten by using the WITH FORMAT option.
Backup to multiple blobs in a single backup operation is not supported.
Specifying a block size with BACKUP is not supported.
Specifying MAXTRANSFERSIZE is not supported.
Specifying backupset options – RETAINDAYS and EXPIREDATE are not supported.
SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL – ‘https://.blob.core.windows.net//.bak’, leaving 223 characters for account, container, and blob names put together.

Backup/Restore Statement	Supported	Exceptions	Comments
BACKUP	Yes	BLOCKSIZE, and MAXTRANSFERSIZE are not supported.	Requires WITH CREDENTIAL specified
RESTORE	Yes		Requires WITH CREDENTIAL specified
RESTORE FILELISTONLY	Yes		Requires WITH CREDENTIAL specified
RESTORE HEADERONLY	Yes		Requires WITH CREDENTIAL specified
RESTORE LABELONLY	Yes		Requires WITH CREDENTIAL specified
RESTORE VERIFYONLY	Yes		Requires WITH CREDENTIAL specified
RESTORE REWINDONLY	No

Support for Backup Arguments

Argument	Supported	Exceptions	Comments
DATABASE	Yes
LOG	Yes
TO (URL)	Yes	Unlike DISK and TAPE, URL does not support specifying or creating a logical name.	This argument is used to specify the URL path for the backup file.
MIRROR TO	No
WITH OPTIONS:
CREDENTIAL	Yes		WITH CREDENTIAL is only supported when using BACKUP TO URL option to back up to the Windows Azure Blob storage service.
DIFFERENTIAL	Yes
COPY_ONLY	Yes
COMPRESSION\|NO_COMPRESSION	Yes
DESCRIPTION	Yes
NAME	Yes
EXPIREDATE \| RETAINDAYS	No
NOINIT \| INIT	No		This option is ignored if used. Appending to blobs is not possible. To overwrite a backup use the FORMAT argument.
NOSKIP \| SKIP	No
NOFORMAT \| FORMAT	Yes		This option is ignored if used. A backup taken to an existing blob fails unless WITH FORMAT is specified. The existing blob is overwritten when WITH FORMAT is specified.
MEDIADESCRIPTION	Yes
MEDIANAME	Yes
BLOCKSIZE	No
BUFFERCOUNT	Yes
MAXTRANSFERSIZE	No
NO_CHECKSUM \| CHECKSUM	Yes
STOP_ON_ERROR \| CONTINUE_AFTER_ERROR	Yes
STATS	Yes
REWIND \| NOREWIND	No
UNLOAD \| NOUNLOAD	No
NORECOVERY \| STANDBY	Yes
NO_TRUNCATE	Yes

Support for Restore Arguments

Argument	Supported	Comments
DATABASE	Yes
LOG	Yes
FROM (URL)	Yes	The FROM URL argument is used to specify the URL path for the backup file.
WITH OPTIONS:
CREDENTIAL	Yes	WITH CREDENTIAL is only supported when using RESTORE FROM URL option to restore from Windows Azure Blob Storage service.
PARTIAL	Yes
RECOVERY \| NORECOVERY \| STANDBY	Yes
LOADHISTORY	Yes
MOVE	Yes
REPLACE	Yes
RESTART	Yes
RESTRICTED_USER	Yes
FILE	Yes
PASSWORD	Yes
MEDIANAME	Yes
MEDIAPASSWORD	Yes
BLOCKSIZE	Yes
BUFFERCOUNT	No
MAXTRANSFERSIZE	No
CHECKSUM \| NO_CHECKSUM	Yes
STOP_ON_ERROR \| CONTINUE_AFTER_ERROR	Yes
FILESTREAM	Yes
STATS	Yes
REWIND \| NOREWIND	No
UNLOAD \| NOUNLOAD	No
KEEP_REPLICATION	Yes
KEEP_CDC	Yes
ENABLE_BROKER \| ERROR_BROKER_CONVERSATIONS \| NEW_BROKER	Yes
STOPAT \| STOPATMARK \| STOPBEFOREMARK	Yes

Using Backup Task in SQL Server Management Studio

The Backup task in SQL Server Management Studio has been enhanced to include URL as one of the destination options, and other supporting objects required to backup to Windows Azure storage like the SQL Credential.
The following steps describe the changes made to the Back Up Database task to allow for backing up to Windows Azure storage.

Start SQL Server Management Studio and connect to the SQL Server instance. Select a database you want to backup, and right click on Tasks, and select Back Up… This opens the Back Up Database dialog box.
On the general page the URL option is used to create a backup to Windows Azure storage. When you select this option, you see other options enabled on this page:
a. File Name: Name of the backup file.
b. SQL Credential: You can either specify an existing SQL Server Credential, or can create a new one by clicking on the Create next to the SQL Credential box.
c. Azure storage container: The name of the Windows Azure storage container to store the backup files.
d. URL prefix: This is built automatically using the information specified in the fields described in the previous steps. If you do edit this value manually, make sure it matches with the other information you provided previously. For example if you modify the storage URL, make sure the SQL Credential is set to authenticate to the same storage account.

SQL Server Backup to URL Using Maintenance Plan Wizard

The Maintenance Plan Wizard in SQL Server Management Studio has been enhanced to include URL as one of the destination options, and other supporting objects required to backup to Windows Azure storage like the SQL Credential.

Restoring from Windows Azure storage Using SQL Server Management Studio

If you are restoring a database using SSMS 2014, URL is included as the device to restore from. Following steps describe the changes in the Restore task to allow restoring from Windows Azure storage:

When you select Devices in the General page of the Restore task in SQL Server Management Studio, this takes you to the Select backup devices dialog box which includes URL as a backup media type.
When you select URL and click Add, this opens the Connect to Azure storage dialog. Specify the SQL Credential information to authenticate to Windows Azure storage.
SQL Server then connects to Windows Azure storage using the SQL Credential information you provided and opens the Locate Backup File in Windows Azure dialog. The backup files residing in the storage are displayed on this page. Select the file you want to use to restore and click OK. This takes you back to the Select Backup Devices dialog, and Clicking OK on this dialog takes you back to the main Restore dialog where you will be able complete the restore.

« All posts

Managing the Lock Priority of Online Operations in SQL Server 2014

by ryltechadmin December 16, 2014

The ONLINE = ON option now contains a WAIT_AT_LOW_PRIORITY option which permits you to specify how long the rebuild process should wait for the necessary locks. The WAIT_AT_LOW_PRIORITY option also allows you to configure the termination of blocking processes related to that rebuild statement.
Use the MAXDOP option to limit the number of processors used in parallel plan execution. The maximum is 64 processors.

ALTER TABLE added parameters in SQL Server 2014:

MAXDOP can be one of the following values:
1
Suppresses parallel plan generation.
>1
Restricts the maximum number of processors used in a parallel index operation to the specified number.
0 (default)
Uses the actual number of processors or fewer based on the current system workload.

ALTER INDEX added parameters in SQL Server 2014:
ONLINE = { ON | OFF }

Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF. REBUILD can be performed as an ONLINE operation.
ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is held on the source object for a very short time. At the end of the operation, for a short time, an S (Shared) lock is acquired on the source if a non-clustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or non-clustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table. Only single-threaded heap rebuild operation is allowed.
To execute the DDL for SWITCH or online index rebuild, all active blocking transactions running on a particular table must be completed. When executing, the SWITCH or rebuild operation prevents new transaction from starting and might significantly affect the workload throughput and temporarily delay access to the underlying table.
OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a non-clustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a non-clustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements. Multi-threaded heap rebuild operations are allowed.

WAIT_AT_LOW_PRIORITY

An online index rebuild has to wait for blocking operations on this table. WAIT_AT_LOW_PRIORITY indicates that the online index rebuild operation will wait for low priority locks, allowing other operations to proceed while the online index build operation is waiting. Omitting the WAIT AT LOW PRIORITY option is equivalent to WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES ]

The wait time (an integer value specified in minutes) that the online index rebuild locks will wait with low priority when executing the DDL command. If the operation is blocked for the MAX_DURATION time, one of the ABORT_AFTER_WAIT actions will be executed. MAX_DURATION time is always in minutes, and the word MINUTES can be omitted.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

NONE
Continue waiting for the lock with normal (regular) priority.
SELF
Exit the online index rebuild DDL operation currently being executed without taking any action.
BLOCKERS
Kill all user transactions that block the online index rebuild DDL operation so that the operation can continue. The BLOCKERS option requires the login to have ALTER ANY CONNECTION permission.

New Wait Types in SQL Server 2014 in the sys.dm_os_wait_stats DMV:

Wait Type	Description
LCK_M_BU_ABORT_BLOCKERS	Occurs when a task is waiting to acquire a Bulk Update (BU) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_BU_LOW_PRIORITY	Occurs when a task is waiting to acquire a Bulk Update (BU) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_IS_ABORT_BLOCKERS	Occurs when a task is waiting to acquire an Intent Shared (IS) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_IS_LOW_PRIORITY	Occurs when a task is waiting to acquire an Intent Shared (IS) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_IU_ABORT_BLOCKERS	Occurs when a task is waiting to acquire an Intent Update (IU) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_IU_LOW_PRIORITY	Occurs when a task is waiting to acquire an Intent Update (IU) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_IX_ABORT_BLOCKERS	Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_IX_LOW_PRIORITY	Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RIn_NL_ABORT_BLOCKERS	Occurs when a task is waiting to acquire a NULL lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. A NULL lock on the key is an instant release lock. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RIn_NL_LOW_PRIORITY	Occurs when a task is waiting to acquire a NULL lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. A NULL lock on the key is an instant release lock. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RIn_S_ABORT_BLOCKERS	Occurs when a task is waiting to acquire a shared lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RIn_S_LOW_PRIORITY	Occurs when a task is waiting to acquire a shared lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RIn_U_ABORT_BLOCKERS	Task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RIn_U_LOW_PRIORITY	Task is waiting to acquire an Update lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
CK_M_RIn_X_ABORT_BLOCKERS	Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers on the current key value, and an Insert Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RIn_X_LOW_PRIORITY	Occurs when a task is waiting to acquire an Exclusive lock with Low Priority on the current key value, and an Insert Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RS_S_ABORT_BLOCKERS	Occurs when a task is waiting to acquire a Shared lock with Abort Blockers on the current key value, and a Shared Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RS_S_LOW_PRIORITY	Occurs when a task is waiting to acquire a Shared lock with Low Priority on the current key value, and a Shared Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RS_U_ABORT_BLOCKERS	Occurs when a task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Update Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RS_U_LOW_PRIORITY	Occurs when a task is waiting to acquire an Update lock with Low Priority on the current key value, and an Update Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RX_S_ABORT_BLOCKERS	Occurs when a task is waiting to acquire a Shared lock with Abort Blockers on the current key value, and an Exclusive Range with Abort Blockers lock between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RX_S_LOW_PRIORITY	Occurs when a task is waiting to acquire a Shared lock with Low Priority on the current key value, and an Exclusive Range with Low Priority lock between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RX_U_ABORT_BLOCKERS	Occurs when a task is waiting to acquire an Update lock with Abort Blockers on the current key value, and an Exclusive range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RX_U_LOW_PRIORITY	Occurs when a task is waiting to acquire an Update lock with Low Priority on the current key value, and an Exclusive range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RX_X_ABORT_BLOCKERS	Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers on the current key value, and an Exclusive Range lock with Abort Blockers between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_RX_X_LOW_PRIORITY	Occurs when a task is waiting to acquire an Exclusive lock with Low Priority on the current key value, and an Exclusive Range lock with Low Priority between the current and previous key. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_S_ABORT_BLOCKERS	Occurs when a task is waiting to acquire a Shared lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_S_LOW_PRIORITY	Occurs when a task is waiting to acquire a Shared lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_SCH_M_ABORT_BLOCKERS	Occurs when a task is waiting to acquire a Schema Modify lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_SCH_M_LOW_PRIORITY	Occurs when a task is waiting to acquire a Schema Modify lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_SCH_S_ABORT_BLOCKERS	Occurs when a task is waiting to acquire a Schema Share lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_SCH_S_LOW_PRIORITY	Occurs when a task is waiting to acquire a Schema Share lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_SIU_ABORT_BLOCKERS	Occurs when a task is waiting to acquire a Shared With Intent Update lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_SIU_LOW_PRIORITY	Occurs when a task is waiting to acquire a Shared With Intent Update lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_SIX_ABORT_BLOCKERS	Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_SIX_LOW_PRIORITY	Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_U_ABORT_BLOCKERS	Occurs when a task is waiting to acquire an Update lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_U_LOW_PRIORITY	Occurs when a task is waiting to acquire an Update lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_UIX_ABORT_BLOCKERS	Occurs when a task is waiting to acquire an Update With Intent Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_UIX_LOW_PRIORITY	Occurs when a task is waiting to acquire an Update With Intent Exclusive lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_X_ABORT_BLOCKERS	Occurs when a task is waiting to acquire an Exclusive lock with Abort Blockers. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
LCK_M_X_LOW_PRIORITY	Occurs when a task is waiting to acquire an Exclusive lock with Low Priority. (Related to the low priority wait option of ALTER TABLE and ALTER INDEX.)
PWAIT_HADR_OFFLINE_COMPLETED	An AlwaysOn drop availability group operation is waiting for the target availability group to go offline before destroying Windows Server Failover Clustering objects.
PWAIT_HADR_ONLINE_COMPLETED	An AlwaysOn create or failover availability group operation is waiting for the target availability group to come online.
WAIT_XTP_CKPT_CLOSE	Occurs when waiting for a checkpoint to complete.
WAIT_XTP_CKPT_ENABLED	Occurs when checkpointing is disabled, and waiting for checkpointing to be enabled.
WAIT_XTP_CKPT_STATE_LOCK	Occurs when synchronizing checking of checkpoint state.
WAIT_XTP_GUEST	Occurs when the database memory allocator needs to stop receiving low-memory notifications.
WAIT_XTP_HOST_WAIT	Occurs when waits are triggered by the database engine and implemented by the host.
WAIT_XTP_OFFLINE_CKPT_LOG_IO	Occurs when offline checkpoint is waiting for a log read IO to complete.
WAIT_XTP_OFFLINE_CKPT_NEW_LOG	Occurs when offline checkpoint is waiting for new log records to scan.
WAIT_XTP_PROCEDURE_ENTRY	Occurs when a drop procedure is waiting for all current executions of that procedure to complete.
WAIT_XTP_TASK_SHUTDOWN	Occurs when waiting for an In-Memory OLTP thread to complete.
WAIT_XTP_TRAN_COMMIT	Occurs when execution of a natively compiled stored procedure is waiting for an XTP transaction to commit (waiting for transactions dependent on for instance).
WAIT_XTP_TRAN_DEPENDENCY	Occurs when waiting for transaction dependencies.
XTPPROC_CACHE_ACCESS	Occurs when for accessing all natively compiled stored procedure cache objects.
XTPPROC_PARTITIONED_STACK_CREATE	Occurs when allocating per-NUMA node natively compiled stored procedure cache structures (must be done single threaded) for a given procedure.