Scared? Don’t worry, there is a workaround and a hotfix. More on that in a moment.
This morning I checked my Twitter feed and saw this post from Nicholas Cain, it’s really important to take note of this so I decided to write a few lines in the hope that more people would see this and take action.
The following is from Article ID: 2969896 – Data loss in clustered index occurs when you run online build index in SQL Server 2012
Symptoms
The online index rebuild can cause index corruption or data loss when it is used together with concurrent queries that modify many rows. For example, a replication updates many rows on a table when the online index build is running on the same table concurrently.
This issue occurs when the following conditions are true:
- The online index build query runs in parallel mode.
- Deadlock error and a fatal error such as “lock timeout” occur in a specific order.
Note To work around this issue, run the online index build in serial mode (DOP = 1).
Action Points
So what Microsoft are suggesting is that by setting DOP=1 you will only use one thread to process the rebuild rather than multiple threads.
How do you do this? Here’s an example:
ALTER INDEX <idxname> ON <objectname> REBUILD WITH (ONLINE=ON, MAXDOP=1);
Now if you can’t download the hotfix (which you can find from the KB here) because of policies that stop you from implementing them then you need to take a look at the code you have for your index rebuilds and make the necessary changes.
Using Maintenance Plans which doesn’t provide an option to rebuild upon how fragmented each index is? My advice – stop using a sledgehammer and find a good well tested community script that will only rebuild and reorganise those indices that need to be defragmented. The other problem with maintenance plans is that you cannot set DOP, not via the UI anyway.
Alternatively, you could look at the script (as shown in the image above) and then copy it into a T-SQL task with the DOP=1 option. Please be cognizant of the fact that this will then become a static list and any index you may add will not be picked up. The other option is to have a statement setting maxdop to 1 for the instance and setting it back when the plan ends, this approach would however affect every other query that would normally use parallelism during this time unless it explicitly had a DOP parameter set for it.
To set MAXXDOP at the instance level you enter code similar to the code shown below:
EXEC sys.sp_configure N’max degree of parallelism’, N’1′
GO
RECONFIGURE WITH OVERRIDE
GO
Consider this a wake up call and pay close attention to how long your maintenance window will now last.
