Fill factor

5 Things About Fillfactor (Kendra Little)

Несколько статей на близкие темы.

Does low fill factor affect SELECT performance? (Tara Kizer)

Does index fill factor affect fragmentation? (Erik Darling)

Good Reasons to Rebuild or Reorganize Indexes (Erik Darling)

5 фактов о Fillfactor (Кендра Литтл)

Краткое введение: что такое fillfactor и как он устанавливается?

“Fillfactor” - это параметр для индексов в SQL Server. При создании или перестроении индекса вы можете указать SQL Server, какой процент каждой страницы данных объемом 8 КБ, используемой на “конечном” уровне индекса, необходимо заполнить.

Другими словами, если при перестройке кластеризованного индекса вы установите коэффициент заполнения 90%, SQL Server попытается оставить 10% каждой конечной страницы пустыми.

По умолчанию SQL Server использует 100% фактор заполнения и пытается заполнить все страницы в индексах до предела. Значения 0 и 100 – синонимы.

Существует два способа установки fillfactor в SQL Server

На уровне экземпляра SQL Server с использованием параметра sys.configuration для коэффициента заполнения. (Я не рекомендую использовать эту опцию по причинам, которые я опишу ниже.)

На уровне отдельного индекса, указав fillfactor при создании или перестроении индекса.

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

Почему люди любят устанавливать fillfactor?

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

Коэффициент заполнения по умолчанию 100% не всегда хорош. Если я заполню свои страницы до предела, а затем мне нужно будет вставить строку на эту страницу, она не поместится. Чтобы данные соответствовали и сохраняли логическую структуру индекса, SQL Server придется выполнить множество сложных действий (избыточное разделение страниц), в том числе:

• Добавить новую страницу

• Перенести примерно половину данных на новую страницу

• Установить недействительными перенесенные данные на старой странице

• Обновить указатели ссылок на страницы на существующих страницах, чтобы указать на новую страницу

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

На что влияет fillfactor?

Вот в чем дело: наличие большого количества пустого пространства на ваших страницах данных ТАКЖЕ негативно сказывается на производительности. Ваши данные более распределены, поэтому вам, вероятно, придется читать больше страниц в памяти. Вы тратите место в кэше, которое просто пустует. Это не только не потрясающе, во многих случаях это может быть УЖАСНО.

Это особенно расточительно, потому что не все индексы подвержены избыточным разделениям страниц. Допустим, у меня есть кластеризованный индекс с увеличивающимся значением идентификатора INT или BIGINT. Я вставляю множество новых строк, и значения редко обновляются или удаляются. В этом случае я могу заполнить свои страницы очень полно, потому что я всегда помещаю новые строки в конец индекса. Добавление этих новых страниц не является избыточным разбиением страниц, хотя, к сожалению, они учитываются в счетчике производительности “разбиение страниц/сек”, что делает очень сложным поиск только избыточных разбиений.

Я часто нахожу, что люди устанавливают значение коэффициента заполнения 80 или ниже для всех индексов в базе данных. Это может привести к потере многих Гб места на диске и в памяти. Это потраченное впустую пространство приводит к дополнительным перемещениям в хранилище, и все это снижает производительность ваших запросов.

Рекомендации по настройке фактора заполнения

Вот несколько простых советов о том, как безопасно установить fillfactor:

Не устанавливайте общесистемное значение для fillfactor. Очень маловероятно, что это поможет вашей работе больше, чем повредит.

Получите хорошее решение для обслуживания индексов, которое проверяет фрагментацию индексов и действует только на индексы, которые достаточно сильно фрагментированы. Запишите решение в таблицу. Ищите индексы, которые часто фрагментированы. Рассмотрите возможность постепенного снижения коэффициента заполнения для этих отдельных индексов, используя запланированное изменение для перестройки индекса. Когда вы впервые снизите коэффициент заполнения, подумайте о том, чтобы просто перейти на 95 и переоценить индекс после недели или двух повторного обслуживания. (В зависимости от вашей версии и выпуска SQL Server, перестройку может потребоваться выполнить в автономном режиме. Реорганизация не может быть использована для установки нового фактора заполнения.)

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

Итак - 5 фактов о fillfactor

1) документация Microsoft предупреждает:

Самое болезненное в поиске плохих параметров наполнения заключается в том, что команда Microsoft пыталась предупредить людей о том, как плохие параметры наполнения могут повлиять на производительность. К сожалению, большинство людей, похоже, не замечают этого предупреждения.

Ознакомьтесь с этой цитатой: “Значение коэффициента заполнения 50 может привести к снижению производительности чтения базы данных в два раза“.

Это ведь совершенно ясно, верно? Поэтому, если я установлю коэффициент заполнения 70 (когда он мне не нужен), я рискую снизить производительность на 30%. Это тоже звучит не очень хорошо. Из-за этого хрупкого баланса следуйте приведенным выше рекомендациям.

2) fillfactor не применяется к кучам

Параметр fillfactor применяется только к индексам, а не ко всем таблицам. Если у вас есть таблица, в которой нет кластеризованного индекса, в SQL Server она называется “кучей”. Кучи странны в нескольких отношениях. Один из этих способов заключается в том, что фактор заполнения не применяется– даже тот фактор заполнения, который вы установили на уровне экземпляра. (Разделение страниц тоже работает по-разному, но это не веская причина для создания кучи.)

3) fillfactor не влияет на новые страницы, вставленные в конце индекса

SQL Server использует fillfactor только при создании, перестройке или реорганизации индекса. Он не использует fillfactor, если он выделяет новую страницу в конце индекса.

Давайте рассмотрим пример кластеризованного индекса, где ключом снова является увеличивающееся значение идентификатора INT. Мы просто вставляем строки, и это добавляет новые страницы в конце индекса. Индекс был создан с коэффициентом заполнения 70% (что, возможно, было не очень хорошей идеей). По мере добавления новых страниц эти страницы заполняются как можно больше– вероятно, более 70%. (Это зависит от размера строки и от того, сколько строк может поместиться на странице.)

4) fillfactor не применяется к LOB pages

Фактор заполнения применяется к страницам данных в строке. При создании таблицы, в зависимости от типов данных, у вас есть варианты, когда некоторые большие типы будут храниться в строке или вне строки. Если данные не хранятся в строке, параметры fillfactor не применяются к этим специальным страницам.

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

5) Кто-то, возможно, изменил fillfactor без вашего ведома

Как только fillfactor установлен в индексе, он остается там. Дальнейшие перестройки или реорганизации индекса сохраняют этот фактор заполнения, если вы не укажете другое значение. Но это может сделать кто-то другой. Если вы не проверите наличие индексов, вы можете не понять, что происходит в вашей базе данных.

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

Заключение оригинальной статьи

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

Как проверить свои настройки сегодня

Наш бесплатный инструмент sp_BlitzIndex ищет все виды безумия в ваших индексах на уровне базы данных. Он будет просматривать ваши метаданные и предупреждать вас о низких значениях коэффициента заполнения, а также позволит вам точно знать, насколько выросли эти индексы. Проверьте это сегодня.

От переводчика

Лично меня статья полностью убедила: fillfactor должен быть по умолчанию 100%. В комментариях к статье указан источник, в котором есть рекомендация устанавливать fillfactor 80%. Это редкий частный случай. Можно ли выиграть, уменьшая значение fillfactor ?

В базе данных TEST_UT_VN создадим таблицу и кластерный индекс

USE [TEST_UT_VN]
GO
CREATE TABLE [dbo].[TestMe]
(               [Column1] [uniqueidentifier] NOT NULL,
                [Column2] [nchar](400) NOT NULL
) ON [PRIMARY]
CREATE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[TestMe]
(              [Column1] ASC,
               [Column2] ASC
) ON [PRIMARY]

Здесь значения Столбец1 – не являются монотонно возрастающими, а Столбец2 – занимает 800 байт, это близко к максимальному размеру индекса 900 байт. Страница СУБД размером 8 Кб содержит 9 строк. Соответственно, при вставке строк, split page происходит достаточно часто. На моем сервере скрипт работает 7 секунд, примерно 7000 запросов (Insert)  в секунду, около 600 page split в секунду.

Будем выполнять скрипт, изменяя значение fillfactor:

USE [TEST_UT_VN];  
GO
DECLARE @Counter as integer;
SET @Counter = 1;
ALTER INDEX ALL ON [dbo].[TestMe] REBUILD WITH (FILLFACTOR = 100);
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);  
WHILE @Counter < 50000
BEGIN  
   SET @Counter = @Counter + 1;
   INSERT INTO [dbo].[TestMe] VALUES (NEWID(), '');
END  
SELECT TOP 5 * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC

Удалось обнаружить корреляцию между показателями fillfactor и LOGMGR_QUEUE:

Опыты показывают, что ожидание типа LOGMGR_QUEUE может зависеть от fillfactor на 20-40 процентов. Если у вас этот тип ожидания доминирует, то можно пробовать изменить настройки fillfactor. Во всех других случаях лучше значение по умолчанию=100%.

Переводчик выражает благодарность Виктору Богачеву – автору и ведущему «Подготовка к 1С:Эксперту по технологическим вопросам. Основной курс» за спонсорскую помощь.