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

  • Статья

Применяется к:SQL ServerAzure SQL DatabaseAzure, управляемому экземпляру SQL Azure

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

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

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

Заметка

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

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

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

Performance Considerations (Приложения-функции Azure. Рекомендации по производительности)

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

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

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

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

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

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

Разрешения

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

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

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

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

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

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

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

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

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

  7. Нажмите кнопку Закрыть.

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

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

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

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

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

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

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

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

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

  8. Щелкните OK.

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

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

  1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.

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

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

    USE AdventureWorks2022;  
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. В обозревателе объектов подключитесь к экземпляру ядра СУБД.

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

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

    USE AdventureWorks2022;  
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).