1.2.8. Опции индексов

Опции индексов – это отдельный и очень интересный разговор, потому что с помощью опций можно повысить производительность индекса.

FILLFACTOR

Вы можете использовать опцию FILLFACTOR для оптимизации производительности операций вставки и обновления в таблице, которые содержат кластерный или не кластерный индекс.

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

Аврора
Александр
Анатолий
Андрей
Артем

• Будет содержать имена Аврора, Александр и Анатолий
• Будет содержать Андрей и Артем

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

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

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

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

Значения опции FILLFACTOR изменяются от 1 до 100 (это число в процентах). Значение по умолчанию – 0. Это означает, что страница заполняется до 100 процентов. Вы же не можете напрямую указать значение 0. Если нужен ноль, то не указывайте эту опцию.

Используйте опцию FILLFACTOR для таблиц, в которые часто вставляются данные, или где часто изменяется индексное значение. Пример использования оператора:

CREATE INDEX I_vcName
ON [Телефонный справочник](vcName) 
 WITH FILLFACTOR=70

PAD_INDEX

Опция PAD_INDEX указывает процент, до которого заполняется не листовые индексные страницы. Вы можете использовать PAD_INDEX опцию только тогда, когда указана опция FILLFACTOR, потому что процентное значение PAD_INDEX зависит от процента, указанного в FILLFACTOR.

Сервер SQL использует процент, который вы указали в опции FILLFACTOR для листового и не листового уровней индекса. По умолчанию, сервер всегда оставляет необходимое пространство для размещения в списке одной строки максимального размера индекса для каждой не листовой страницы.

Указывая значение PAD_INDEX вы должны учитывать, что количество элементов в не листовой странице индекса не может быть меньше двух, не обращая внимания на значение FILLFACTOR.

Следующий пример создает индекс с использованием описанных параметров:

CREATE INDEX I_vcName
ON [Телефонный справочник](vcName) 
 WITH PAD_INDEX, FILLFACTOR=70

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