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.

Thomas Kejser
  • 6,045
  • 2
  • 19
  • 43
  • 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 05 '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 05 '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 05 '14 at 22:03
  • Don't make any assumptions about our schedule. I can bet you that our schedule allows us to make ANY change we want that saves space and provides speed - we are not a bank or some other stone age organisation. Everything from moving columns around, adding/removing indexes, adding indexed views, repartitioning, changing NULL'ability, adding check constraints etc. The options we will explore entirely depend on what we can get away with doing while only taking an SCH-M lock at the end. – Thomas Kejser Jan 05 '14 at 22:10
  • It seems like you're both speaking different languages. – Kermit Jan 05 '14 at 23:30
  • Ok, well without knowing specifically what you are trying to do, no I cannot think of any reference that lists operations that take schema modify locks or the durations in which they hold them. – Thomas Stringer Jan 06 '14 at 00:28
  • 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 06 '14 at 01:32
  • I will update this entry (working on the list now) – Thomas Kejser Jan 06 '14 at 08:31
  • 8
    How is the list going? :) – nmad Mar 25 '14 at 08:15

2 Answers2

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

Java Riser
  • 151
  • 1
  • 3
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).

Community
  • 1
Jos de Bruijn - MSFT
  • 151
  • 1
  • 2