SQL Server 2014 comes with a new argument for how to handle the blocking issues that come from online index rebuilds.
Before SQL Server 2014
Before the existence of SQL Server 2014, if you were doing an online index rebuild… SQL Server would wait until the index rebuild could get the locks that it needs for rebuilding the index. This can cause long running maintenance plans on very active tables.
SQL Server 2014 Online Index Rebuild Argument
The new functionality available in SQL Server 2014 tags on to the original index/table rebuild syntax. It allows you to customize how you want to handle the blocking that occurs on the indexes/tables that are being rebuilt. Here is a sample of the new syntax for managing the blocking on the index rebuild.
ALTER INDEX ALL ON Animal REBUILD WITH ( SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF, ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ) )
Notice in the statement above, the new WAIT_AT_LOW_PRIORITY flag that gets added to the ONLINE argument. When you rebuild an index in SQL Server with the WAIT_AT_LOW_PRIORITY flag turned on, it will allow other operations to proceed while the index build waits for low priority locks. This argument comes with 2 parameters.
The MAX_DURATION parameter is how long you want to wait in minutes. It must always be in minutes.
This parameter comes with 3 options. This is basically who loses if the rebuild process cannot get the locks that it needs. The connection that you choose will be terminated after the timeout period.
- NONE – Continue waiting for the lock
- SELF – The rebuild operation will fail
- BLOCKERS – All transactions that are blocking the rebuild will be killed
If you do not specify WAIT_AT_LOW_PRIORITY with you index rebuild, then SQL Server will automatically set it to the default of 0 minutes and NONE.
Index Reference: http://msdn.microsoft.com/en-us/library/ms188388.aspx
Table Reference: http://msdn.microsoft.com/en-us/library/ms190273.aspx