Share via

SQL Server 2014 Troubleshooting: Online Index Rebuild Issues

  • Article

In order to make data management easy and self-manageable, there are various versions of SQL Server that has been released over the years. With the release of the latest SQL Server 2014 version, you can make use of some of the advanced features, as well as use the features of earlier versions as well. This self-tuning, self-organizing, and self-maintaining software supports Cloud Backup Using Azure, ColumnStore, and In-Memory OTP. With an improved integration with Windows Server 2012, the software also provides T-SQL, APIs, and management tools used in an earlier version, to give the same look and feel.

When you execute a query in SQL Server, it finds the record by making use of the indexes. The mechanism of using the index to find a record is similar to the way in which a book is read. You need not have to traverse each page to find a particular topic; rather you can open the page or section directly just by looking for its entry in the index. SQL Server uses the same pattern by having keys and pointers associated with an index. There are various columns that can be used to build a key, whereas the pointers refer to the queried data storage location. If the indexes are well defined, they can be helpful in improving the overall performance of a system. However, there are times when the index fails to perform its function properly. It could be in the form of index corruption or disabled index. There could be various reasons for this, including power outage, virus infections, mishandling, etc. In such cases, it becomes very important to perform SQL Server database recovery to resolve the issues at the earliest. You can try rebuilding the indexes to get rid of such situations.

However, in versions like SQL Server 2012 and SQL Server 2014, when you go for an online repair of the index, you might experience some abnormal behavior supported by some errors like lock timeout. In the worst case, you can also end up losing all your data. It is very unlikely to lose all your data, but one should be prepared for such data loss situations.

Su ch an attempt is more likely to happen if you are running an Enterprise or Equivalent edition. Any version other than these two would not support online rebuilds. So, not everyone needs to be concerned about this issue. In other versions, the MAXDOP value is set to 1, which denies any online rebuild attempt, unless they are overridden. The issue was first noticed in SQL Server 2012 RTM version. The same issue was manifested in SP1 and was not fixed till the release of SP2. You cannot find its fix even in the RTM version, as it has gone out of support now. The case of SQL Server 2014 is no different. However, there are various updates available with SQL Server 2012 Service Pack 1 and 2 as well as SQL Server 2014 that can be used to fix such issues.

SQL Server 2012 RTM (11.0.2100 -> 11.0.2999)

This version falls into the vulnerable category. You can try upgrading this version to SP1 or SP2 and apply the required fix. If this does not work, you can apply other techniques applicable to other versions, as mentioned in the below points.

SQL Server 2012 Service Pack 1 (11.0.3000 -> 11.0.3436)

This version is less vulnerable as compared to the SQL Server 2012 RTM (11.0.2100 -> 11.0.2999) version. You can apply the cumulative updates to fix the issue. In case you have installed SP2, it should be followed up by with a relevant cumulative update. However, if this does not work, you can try few other steps as mentioned in below points.

For the SQL Server 2012 Service Pack 1 (11.0.3437 -> 11.0.5057) version, you need not do anything, as it already has the fix.

SQL Server 2012 Service Pack 2 (11.0.5058 -> 11.0.5521)

This is also less vulnerable as compared to the SQL Server 2012 RTM (11.0.2100 -> 11.0.2999) version. The issue can be fixed by applying the relevant cumulative update. If this fails, you can try one or more of the workarounds as mentioned below.

The SQL Server 2012 Service Pack 2 (11.0.5522 or greater), already contains the fix. 

SQL Server 2014 RTM (12.0.2000 -> 12.0.2369)

The latest SQL Server 2014 RTM is also less vulnerable than the SQL Server 2012 RTM version. Issues arising in this version can be taken care of by applying the relevant cumulative update.  In case this fails, you can apply one or more steps from below mentioned workarounds. The SQL Server 2014 RTM (12.0.2370 or greater) does not require any additional fix.

From the above points, it is seen that except 2012 RTM, all have the cumulative update or on-demand hotfix. However, there are situations where you might not be able to provide such updates, like in the case when your company policy or testing cycle deny you the permission. In such cases, you can try other options as mentioned below:

  1. You can wait for a new service pack to perform the rebuild.
  2. The instance-wide max degree of parallelism can be set to 1.
  3. You can manually add the WITH (MAXDOP = 1) option to all rebuild commands.
  4. You can run a specific login by setting up your index maintenance jobs. Then you can create a Workload Group using the Resource Governor to limit the login's MAX_DOP to 1.