Tag Archives: Indexes

SQL Server 2017: возобновляемое перестроение индексов

Перестроение индексов на критических БД зачастую может быть сложной операцией по ряду причин. Наверное, практически каждый администратор БД сталкивался с длительными блокировками по время обслуживания индексов и ростом журнала транзакций. Отчасти может помочь перестроение индексов в режиме 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, где есть более детальная демонстрация использования этой возможности.

SQL Server 2016: увеличен максимальный размер ключа в некластерном индексе

В SQL Server 2016 и в Azure SQL Database увеличен максимальный размер ключа в некластерном индексе с 900 до 1700 байт. Максимальный размер ключа для кластерного индекса по-прежнему остается 900 байт.

Ниже приведен пример, когда создается таблица со строковым полем фиксированной длины 1700 байт и индексом на этом поле.

if object_id('dbo.test_table', 'U') is not null
	drop table dbo.test_table;
go

create table dbo.test_table (
	c1 varchar(1700) not null
);
go

create index ix_test_c1 on dbo.test_table (c1)
go

insert into dbo.test_table values (replicate('A', 1700))
go

Скрипт успешно отрабатывает на SQL Server 2016. На ранних версиях SQL Server команда CREATE INDEX выдает предупреждение.

Warning! The maximum key length is 900 bytes. The index ‘ix_test_c1’ has maximum length of 1700 bytes. For some combination of large values, the insert/update operation will fail.

А при попытке вставить какие данные в таблицу – ошибку.

Msg 1946, Level 16, State 3, Line 13
Operation failed. The index entry of length 1700 bytes for the index ‘ix_test_c1’ exceeds the maximum length of 900 bytes.

При создании некластерных колоночных индексов вы можете указать столбцы, которые будут храниться в колоночном формате. Эти колонки не будут являться ключом индекса, и здесь нет жесткого ограничения на их размер.

Для некластерных индексов в in-memory таблицах ограничение на размер ключа составляет 2500 байт, для hash индексов ограничений нет.