20

As we are about to change some of the large tables in our 5TB database I found myself needing a list of the operations that can be performed online and the which require full locks to be held while they run. Ideally, this list would also contain information about which statements require the SCH-M lock to commit at the end.

While I know most of them from my time in Microsoft, I was surprised that I could not find a publicly available list of online operations as they evolved from SQL Server 2005 and all the way to 2014 CTP.

Does anyone have such a list available? If not, I may decide to create it.

0

2 Answers 2

5

Sounds like an undertaking. Online index operations are not available in every edition of Microsoft SQL Server, hopefully this can get you started (it's in table form):

SQL Server 2012: http://msdn.microsoft.com/en-us/library/ms190981.aspx

SQL Server 2008R2: http://msdn.microsoft.com/en-us/library/ms190981(v=sql.105).aspx

SQL Server 2008: http://msdn.microsoft.com/en-us/library/ms190981(v=sql.100).aspx

SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms190981(v=sql.90).aspx

5

SQL Server 2014 does not introduce new online operations per se, but does improve a number of operations to increase availability. From the what's new section:

Partition Switching and Indexing

The individual partitions of partitioned tables can now be rebuilt. For more information, see ALTER INDEX (Transact-SQL).

and

Managing the Lock Priority of Online Operations

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. For more information, see ALTER TABLE (Transact-SQL) and ALTER INDEX (Transact-SQL). Troubleshooting information about new types of lock states is available in sys.dm_tran_locks (Transact-SQL) and sys.dm_os_wait_stats (Transact-SQL).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.