Перестроение индексов на критических БД зачастую может быть сложной операцией по ряду причин. Наверное, практически каждый администратор БД сталкивался с длительными блокировками по время обслуживания индексов и ростом журнала транзакций. Отчасти может помочь перестроение индексов в режиме ONLINE или же операция REORGANIZE, но в SQL Server 2017 также в помощь приходит такая возможность, как возобновляемое перестроение, которая позволяет обходить проблемы, которые до этого не решались стандартными способами.

Операция ALTER INDEX теперь может ставить перестроение на паузу или возобновлять его, что позволяет управлять процессом обслуживания индексов более гранулярно. Представьте ситуацию: вы обнаружили, что процесс перестроения большого индекса сильно нагружает диск или блокирует важные процессы, и, вместо того, чтобы его прервать, вы просто ставите его на паузу и возобновляете, когда снова появляется возможность.

Конечно, приходится чем-то жертвовать ради удобства. В данном случае в БД необходимо будет хранить обе копии индекса: старую и новую до тех пор пока операция не завершится успешно или не будет прервана. Еще одним неприятным моментом может стать то, что опция SORT_IN_TEMPDB не работает в паре с возобновляемым перестроением.

Зато в качестве бонуса вы получаете возможность усекать журнал транзакций, как только вы ставите перестроение индексов на паузу или прерываете. Ранее он продолжал расти и расти до тех пор пока индекс полностью не перестроится.

Итак, давайте посмотрим на примере, как это работает. Для того, чтобы инициировать процесс перестроения индекса, который затем можно будет прервать, необходимо указать опции ONLINE=ON и RESUMABLE=ON.

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] rebuild with (resumable = on, online = on)

Также появилось новое представление, в котором можно следить за статусом всех возобновляемых перестроений.

select * from sys.index_resumable_operations

Очень удобно, что отображается, идет ли сейчас перестроение, когда началось, когда прерывалось, процент выполнения.

Во время выполнения этого перестроения в отдельной сессии вы можете управлять им с помощью следующих команд.

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] pause

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] resume

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] abort

PAUSE\RESUME позволяют останавливать и продолжать перестроение, в то время как команда ABORT может его полностью прервать, если вам вдруг это понадобилось.

При возобновление вы можете менять параметр MAXDOP.

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] resume with (maxdop = 4)

Но одна из самых интересных опций, состоит в том, что вы можете указывать максимальное количество минут, которые будет выполняться операция перестроения. Может быть очень полезно, если вы хотите перестраивать индекс, но при этом ограничивать эту операцию по времени.

alter index pk_MyBigDemoTable on [dbo].[MyBigDemoTable] rebuild with (resumable = on, online = on, max_duration = 1)

Как мы видим, новая возможность довольно интересна и позволяет вывести обслуживание индексов на новый уровень. Также вы можете посмотреть следующее видео на YouTube, где есть более детальная демонстрация использования этой возможности.