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.

Improve this question
  • 4
    I think you may be looking at this from the wrong side of the spectrum. Instead of interpolating what you can do online, you should be taking what you need to do and finding out what can be done online. "online" is a very generic term, and as you denote depends on concurrency. My follow up question would be, "what exactly are you trying to do?" – Thomas Stringer Jan 5 '14 at 20:28
  • 2
    I disagree @ThomasStringer - the choices you will make in a large system will depend on what you can do, not what you want to do. I would want everything to be online - but I know that is not possible - so I am trying to map the design space. – Thomas Kejser Jan 5 '14 at 20:31
  • "we are about to change some of the large tables"... What are you changing? That's what I'm trying to get at. I doubt your schedule allows you to just come up with random things to do in a big table in a large database, so you must have some operation in mind. If you're looking for what operations require schema stability or modify locks that's something completely different. Again... "online" is in the eye of the beholder/requester. – Thomas Stringer Jan 5 '14 at 22:03
  • 2
    If no authoritative source is found, it would be helpful to future readers to list what you do know is an online operation from your time at MS. – billinkc Jan 6 '14 at 1:32
  • 8
    How is the list going? :) – nmad Mar 25 '14 at 8:15
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

Improve this answer
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).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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