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

  • Статья

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

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

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

ПримечаниеПримечание

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

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

  • Перед началом работы выполните следующие действия.

    Вопросы производительности

    Безопасность

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

    Среда SQL Server Management Studio

    Transact-SQL

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

Вопросы производительности

Разбиения страниц

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

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

Добавление данных в конец таблицы

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

Безопасность

Разрешения

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

Значок стрелки, используемый со ссылкой «В начало»[Top]

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

Задание коэффициента заполнения с помощью конструктора таблиц

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

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

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

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

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

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

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

  8. В меню Файл выберите команду Сохранить table_name.

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

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

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

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

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

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

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

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

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

Значок стрелки, используемый со ссылкой «В начало»[Top]

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

Указание коэффициента заполнения для существующего индекса

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

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

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

    USE AdventureWorks2012;
GO
-- Rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index 
-- with a fill factor of 80 on the HumanResources.Employee table.

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80); 
GO

Другой способ указания коэффициента заполнения в индексе

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

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

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

    USE AdventureWorks2012;
GO
/* Drops and re-creates the IX_Employee_OrganizationLevel_OrganizationNode index on the HumanResources.Employee table with a fill factor of 80.
*/

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
   (OrganizationLevel, OrganizationNode) 
WITH (DROP_EXISTING = ON, FILLFACTOR = 80); 
GO

Дополнительные сведения см. в разделе ALTER INDEX (Transact-SQL).

Значок стрелки, используемый со ссылкой «В начало»[Top]