Укажите коэффициент заполнения для индекса

В этом разделе приведено описание коэффициента заполнения и описываются способы указания значения коэффициента заполнения в индексе в SQL Server с помощью SQL Server Management Studio или Transact-SQL.

Коэффициент заполнения (параметр fill factor) служит для точной настройки хранения и производительности индекса. При создании или перестроении индекса коэффициент заполнения отображает процент заполнения пространства каждой страницы конечного уровня, что позволяет зарезервировать для будущего расширения оставшееся на каждой странице пространство как свободное. Например, при указании для коэффициента заполнения значения 80 на каждой странице конечного уровня будет зарезервировано 20 процентов занимаемого ею дискового пространства. Данное дисковое пространство будет использовано для расширения индекса при добавлении в базовую таблицу новых данных. Пустое место резервируется не в конце индекса, а между строками индекса.

Коэффициент заполнения — это значение в процентах от 1 до 100; значение по умолчанию на сервере — 0, что означает полное заполнение страниц конечного уровня.

Значения коэффициента заполнения 0 и 100 равнозначны.

В этом разделе

Перед началом работы

Указание коэффициента заполнения для индекса с помощью следующих средств:

Перед началом

Правильный выбор значения коэффициента заполнения уменьшает потенциальное разделение страниц, предоставляя достаточно места для увеличения индекса при добавлении данных в базовую таблицу. При добавлении новой строки на страницу, которая используется в полнотекстовом индексировании, компонент Компонент Database Engine перемещает примерно половину имеющихся на ней данных на новую страницу, таким образом освобождая пространство для новой строки. Такая реорганизация называется разбиением страницы. Операция разбиения страницы позволяет выделить пространство для размещения новых записей, однако является ресурсоемкой и может выполняться длительное время. Также указанная операция может вызвать фрагментацию, что приводит к увеличению количества операций ввода-вывода. Для предотвращения слишком частого разбиения страниц необходимо перераспределить данные, перестроив индекс с помощью нового или уже существующего коэффициента заполнения. Дополнительные сведения см. в статье Реорганизация и перестроение индексов.

Хотя низкое значение коэффициента заполнения (отличное от 0) может снизить необходимость в разделении страниц при расширении индекса, для его хранения требуется больший объем памяти, что приводит к снижению производительности. Даже для приложений, ориентированных на выполнение множества операций вставки и обновления, количество операций считывания обычно в 5–10 раз превышает количество операций записи. Поэтому при задании значения коэффициента заполнения, отличного от значения по умолчанию, производительность операций считывания снижается обратно пропорционально значению коэффициента заполнения. Например, значение коэффициента заполнения, равное 50, может снизить производительность операций чтения в два раза. Производительность операций чтения снижается из-за того, что в индексе содержится большее количество страниц, что увеличивает количество операций ввода-вывода, проводимых для извлечения данных.

Ненулевой коэффициент заполнения, отличный от 0 и 100, может повысить производительность, если новые данные равномерно распределяются по таблице. Однако, если данные добавляются в конец таблицы, пустое место на страницах индекса заполняться не будет. Например, если ключевым столбцом индекса является столбец IDENTITY, ключ для новых строк будет постоянно увеличиваться и строки индекса будут логически добавляться в конец индекса. Если существующие строки будут обновляться данными, увеличивающими размер строк, следует использовать коэффициент заполнения менее 100. Дополнительные байты на каждой странице помогут снизить до минимума разбиение страниц с помощью увеличения длины строк.

Необходимо разрешение ALTER для таблицы или представления. Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner.

Использование среды SQL Server Management Studio

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

Чтобы развернуть папку Таблицы , щелкните значок «плюс».

Щелкните правой кнопкой мыши таблицу, для которой необходимо указать коэффициент заполнения индекса, а затем выберите пункт Конструктор.

В меню Конструктор таблиц выберите пункт Индексы и ключи.

Выберите индекс с коэффициентом заполнения, который необходимо указать.

Разверните Характеристики заполнения, выберите строку Коэффициент заполнения и введите в строке необходимый коэффициент заполнения.

Щелкните Закрыть.

В меню Файл выберите пункт Сохранитьимя_таблицы.

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

Чтобы развернуть папку Таблицы , щелкните значок «плюс».

Щелкните знак плюса (+), чтобы развернуть таблицу, для которой необходимо указать коэффициент заполнения индекса.

Чтобы развернуть папку Индексы , щелкните знак «плюс» (+).

Щелкните правой кнопкой мыши индекс с коэффициентом заполнения, который нужно указать, и выберите пункт Свойства.

В разделе Выбор страницы щелкните Параметры.

В строке Коэффициент заполнения введите нужный коэффициент заполнения.

Нажмите кнопку ОК.

Использование Transact-SQL

В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.

На стандартной панели выберите пункт Создать запрос.

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

В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.

На стандартной панели выберите пункт Создать запрос.

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