Fixes for SQL Server 2012 & 2014 Online Index Rebuild Issue

There is a regression bug in SQL Server 2012 and SQL Server 2014 where, if you rebuild an index online in parallel, and you also experience a fatal error such as a lock timeout, you could experience data loss or corruption. This should be a relatively rare scenario (Phil Brammer has a simple repro in Connect #795134), but data loss is data loss, and I am not prepared to gamble. The fix is described in KB #2969896 : FIX: Data loss in clustered index occurs when you run online build index in SQL Server 2012.

Not everyone needs to be concerned about this issue. If you are not running Enterprise (or an equivalent) Edition, you can't perform parallel or online rebuilds in the first place (and there are probably some folks on Enterprise not rebuilding or not rebuilding online). If you have instance-wide MAXDOP set to 1, they can't go parallel unless you override it at the statement level. But, if you are on 2012 or 2014, running an adequate edition, and your online rebuilds could go parallel, you are vulnerable to this problem.

As I alluded to above, this problem could manifest in SQL Server 2012 RTM, Service Pack 1, and even Service Pack 2, which was released on June 10. The bug was not fixed until long after the SP2 code was frozen, so SP2 does not include this fix or any of the fixes from SP1 CU #10 or #11. I blogged about this here. The RTM branch is officially out of support, so you will not be seeing a fix there. The issue can also occur in SQL Server 2014.

There are now cumulative updates available for SQL Server 2012 Service Pack 1 & 2 as well as SQL Server 2014. A quick summary of the options I recommend:

If your branch / @@VERSION is…   vulnerable   maybe   not vulnerable		…you should…
SQL Server 2012 RTM	11.0.2100 -> 11.0.2999	Upgrade to Service Pack 1 or Service Pack 2 (RTM is out of support!) and apply the relevant fix from KB #2969896. If you can't implement 1., employ one or more of the workarounds below.
SQL Server 2012 Service Pack 1	11.0.3000 -> 11.0.3436	Apply Cumulative Update #11 (11.0.3449) from KB #2975396. If you then install Service Pack 2, you should follow up with SP2 CU #1.* Failing 1., employ one or more of the workarounds below.
	11.0.3437 -> 11.0.5057	Do nothing; you already have the fix.
SQL Server 2012 Service Pack 2	11.0.5058 – > 11.0.5521	Apply Cumulative Update #1 (11.0.5532) from KB #2976982. Failing 1., employ one or more of the workarounds below.
	11.0.5522 or greater	Do nothing; you already have the fix.
SQL Server 2014 RTM	12.0.2000 – > 12.0.2369	Apply Cumulative Update #2 (12.0.2370) from KB #2967546. Failing 1., employ one or more of the workarounds below.
	12.0.2370 or greater	Do nothing; you already have the fix.
* If you install the SP1 hotfix or Cumulative Update #11 and then install SP2, you will undo those changes, including this fix.

Solutions for the hotfix/CU averse

Since all affected branches (well, except 2012 RTM) have an on-demand hotfix and/or a cumulative update that addresses the issue, the easy answer is to just install the relevant update. However, you may be in a scenario where your company policy or testing cycles prevent you from deploying these updates quickly, or maybe ever. So what other options do you have?

• You can stop performing rebuilds until there is a new service pack available for your branch (maybe you can just stick with REORGANIZE for now). Unfortunately, if you are in a "service pack only" company, your options are very limited: you can fight harder to change that policy, or you can wait for SQL Server 2012 Service Pack 3 (which may be a long time, or may simply never come – see FAQ #21 here) or SQL Server 2014 Service Pack 1 (which we probably won't see before 2015 rolls around).
• You can set the instance-wide max degree of parallelism to 1, however this may have a negative effect on the rest of your workload – think about things like multi-threaded DBCC, parallel queries against or between partitioned tables, and other operations where you may want to reduce parallelism but not eliminate it altogether. Also, this setting won't affect an online rebuild with, say, an explicit MAXDOP = 8 hard-coded into the command, as this will override the sp_configure setting.
   
• You can add the WITH (MAXDOP = 1) option manually to all of your rebuild commands. (Note: you don't have to do this for XML indexes, since they inherently run single-threaded, but I would just apply it to all rebuilds for consistency and to avoid any unnecessary conditional logic.)
   
• You can set your index maintenance jobs to run as a specific login, and then use Resource Governor to create a Workload Group that limits that login's MAX_DOP to 1, regardless of what they are doing. I have an example of this in the 2008 white paper I wrote with Boris Baryshnikov, Using the Resource Governor, in the section entitled, "Limiting Parallelism for Intensive Background Jobs."
   
• If you are using Ola Hallengren's index maintenance solution, you can add the @MaxDop parameter to your calls to dbo.IndexOptimize:
   

  EXEC dbo.IndexOptimize
      /* other parameters */
      @MaxDop = 1;

• If you are using SQL Sentry Fragmentation Manager, you can dictate the level of MAXDOP to use under Settings – and you can do this enterprise-wide, per instance, per database, or even per individual index (in this case, you'd probably want to set this per instance, for all instances without a fix available):
   

  
  Fragmentation Manager settings for the instance (left) and an individual index (right).

• If you're using Maintenance Plans for your index rebuilds, you're going to have to change them to use Execute T-SQL Statement Tasks, and write your ALTER INDEX ... WITH (ONLINE = ON, MAXDOP = 1); commands manually (so may as well switch to an automated solution). See, the Index Rebuild Task does not have an exposed property for MAXDOP, even though it has been requested multiple times (most recently in 2012, by Alberto Morillo, and as far back as 2006, by Linchi Shea). And just look at all of these other useful properties they expose, like AdvSortInTempdb, ObjectTypeSelection, and TaskAllowesDatbaseSelection [sic²!]:
   

  
  All those options, but still no cure for MAXDOP.