Фактор заполнения (Fill Factor)

В этой статье я собираюсь рассказать о факторе заполнения (FILLFACTOR, FF, http://msdn.microsoft.com/en-us/library/ms191005.aspx), возможности, которой часто не придают значения и оставляют ее с установками по умолчанию.

Fill Factor указывает, сколько листовых страниц (leaf pages) индекса заполняется, а Pad Index (http://msdn.microsoft.com/en-us/library/ms186872.aspx) указывает, должны ли страницы Intermediate Index использовать тот же фактор заполнения, что указан для индекса. По умолчанию фактор заполнения индекса берется из настроек экземпляра БД, как показано ниже. Значение по умолчанию равно 0, что значит, что движок SQL Server будет полностью заполнять все страницы индекса данными, то есть то же, что и 100%. На рисунке 1 показано, как изменить настройки по умолчанию.

Поиск и задание идеального FF для БД – не самое простое дело. Это не тот случай, когда достаточно задать некие магические числа, чтобы БД работала всегда и во всех сценариях. Нужно поразмыслить и провести тесты, чтобы убедиться, что выбранные значения удовлетворительно работают. При высоком FF больше строк упаковывается в одну страницу данных, но есть большие шансы, что разбиений страниц станет больше, особенно в транзакционных системах. Это нежелательно, поскольку чем меньше разбиений страниц, тем лучше для производительности. С другой стороны, при низком FF на странице данных будет меньше записей, что снизит число разбиений страниц, но потребует больше таких ресурсов, как I/O, для чтения того же объема данных, так как данные будут распределены по большему числу страниц.

Кроме настройки FF для целой БД, можно настроить FF для каждого индекса индивидуально. Это делается при одной из следующих операций:

Fill Factor для индекса вычисляется и применяется только в перечисленных выше операциях. При обычных операциях, т.е. DML-выражениях, настройки FF игнорируются, и движок будет пытаться заполнить страницы индекса настолько полно, насколько сможет. Когда в конце концов происходит разбиение страницы, движок в общеv случае переместит половину исходной страницы на новую страницу, независимо от настроек FF. Подробнее об этом можно прочитать по адресу http://msdn.microsoft.com/en-us/library/ms177459.aspx.

Тестовые сценарии

В прилагаемом скрипте я создаю две одностолбцовых таблицы, _TEST1 и _TEST2, соответственно.

Для таблицы _TEST1 создается индекс INDEX1 по столбцу col_1 со 100% фактором заполнения.

Для таблицы _TEST2 создается индекс INDEX2 по столбцу col_2 с 50% фактором заполнения.

Таблицы _TEST
Название таблицы	Название индекса	Название столбца	Фактор заполнения
_TEST1	INDEX1 (CLUSTERED)	Col_1 char(900)	100%
_TEST2	INDEX2 (CLUSTERED)	Col_2 char(900)	50%

USE tempdb
GO

CREATE TABLE [dbo].[_TEST1](
  [Col_1] [char](900) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[_TEST2](
  [Col_2] [char](900) NULL
) ON [PRIMARY]

GO

----

Delete _TEST1;
Delete _TEST2;

Declare @Counter Int;

set @Counter = 1;

While @Counter <= 8
Begin
  INSERT INTO _TEST1 (Col_1)
  Values       ('Test Data - Column 1');
  
  INSERT INTO _TEST2 (Col_2)
  Values       ('Test Data - Column 2');


  Set @Counter = @Counter + 1;
End

-----

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[_TEST1]') AND name = N'INDEX1')
  DROP INDEX [INDEX1] ON [dbo].[_TEST1] 
GO


CREATE CLUSTERED INDEX [INDEX1] ON [dbo].[_TEST1] ([Col_1] ASC)

GO


IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[_TEST2]') AND name = N'INDEX2')
  DROP INDEX [INDEX2] ON [dbo].[_TEST2] 
GO


CREATE CLUSTERED INDEX [INDEX2] ON [dbo].[_TEST2] 
([Col_2] ASC) WITH (FILLFACTOR = 50, PAD_INDEX = ON)
GO
-------

Исполняя приведенный ниже скрипт, легко проверить распределение данных.

USE tempdb
GO

SELECT CAST(OBJECT_NAME(S.object_id, DB_ID('tempdb')) 
  AS VARCHAR(20)) 
  AS 'Table Name',
  I.name AS IndexName,
  CAST(index_type_desc AS VARCHAR(20)) 
    AS 'Index Type',
  CASE WHEN I.fill_factor in (0,100) THEN
    100
  ELSE 
    I.fill_factor
  END AS fill_factor,

  CASE WHEN I.is_padded = 1 THEN
    'On'
  ELSE 
    'Off'
  END As 'Pad Index', 

  avg_fragmentation_in_percent AS 'Avg % Fragmentation',
  record_count AS 'RecordCount',
  page_count AS 'Pages Allocated',
  avg_page_space_used_in_percent AS 'Avg % Page Space Used', 
  avg_record_size_in_bytes,
  Case When Index_level = 0 Then
    'Leaf'
  Else 
    'Intermediate'
  End As 'Index Level' 

  FROM sys.dm_db_index_physical_stats (
    DB_ID('tempdb'),
    OBJECT_ID('_TEST'),
    NULL,NULL,'DETAILED' ) S
    INNER JOIN sys.indexes I On (
      I.object_id = S.object_id 
      AND I.index_id = S.index_id)

Table: _TEST1. INDEX1

Для этой таблицы был указан FF, равный 100%, и из-за этого движок заполнил всю страницу данных (8060 байт). 8 записей, имеющихся в таблице, умещаются на одной листовой странице (как показано выше). Если провести некоторые вычисления:

Записи, которые уместятся на странице = 8060/914 = 8.818, то есть округленно 8 записей, так как запись не может быть разделена между несколькими страницами.

Использовано 90.511% страницы. Движок пытался использовать всю страницу целиком (FF = 100%). Однако оставшиеся 10% невозможно использовать, поскольку еще одна запись не уместится на оставшемся месте, которое составляет (8060 - (8 x 914)) = 748 байт!

Table: _TEST2. INDEX2

Для этой таблицы был указан FF в 50%, и движок заполнил страницы данных наполовину (на каждой странице использовано 4030 байт). 8 записей, имеющиеся в таблице, не помещаются на одной странице и, в данном случае, для них требуются две листовые страницы и одна промежуточная: конечные страницы – для хранения записей, а промежуточная – для хранения указателей на листовые страницы данных.

Эффективное свободное пространство на странице = 8060/2 (50% FF) = 4030 байт.

Записи, умещающиеся на странице = 4030/914 = 4.409, т.е. 4 записи.

Число страниц, необходимое для хранения 8 записей = общее число записей/число записей на странице = 8/4 = 2

Среднее использованное пространство страницы = 45.24%. Движок пытался использовать 50% страницы данных, однако оставшиеся 5% нельзя использовать, поскольку еще одна запись не уместится в оставшихся (4030 - (4x 914)) = 374 байтах.

Производительность SELECT

Давайте теперь проанализируем производительность SELECT для таблиц/индексов. Используем опцию

SET STATISTICS IO, которая возвращает информацию о вводе/выводе, необходимую для анализа различий в производительности.

-- Select Performance...
Print 'Running First Statement... using INDEX 1'
SET STATISTICS IO ON;
select col_1 From _TEST1 Where col_1 = 'Test Data - Column 1'
Print ''
Print 'Running Second Statement... using INDEX 2'
select col_2 From _TEST2 Where col_2 = 'Test Data - Column 2'
SET STATISTICS IO OFF;

Исполнение этого скрипта сгенерирует статистику, приведенную ниже. Как и ожидалось, поскольку INDEX1 (100% FF) компактнее, нужно только 2 логических чтения, а для выборки того же объема данных из INDEX2 (50% FF) требуется 4 логических чтения (рисунок 3).

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

Теперь я добавлю несколько записей в обе таблицы.

SET STATISTICS IO ON;
INSERT INTO _TEST1 (Col_1)
Values ('A Test Data - Column 1')

INSERT INTO _TEST2 (Col_2)
Values ('A Test Data - Column 2')
SET STATISTICS IO OFF;

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

С другой стороны, поскольку INDEX2 для таблицы _TEST2 был создан с FF 50%, в нем есть свободное место для новой записи в листовых страницах.

На рисунке 4 показано, как будет выглядеть распределение после вставки записей и исполнения SQL-скрипта sys.dm_db_Index_physical_stats.

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

А вот при вставке в INDEX2 потребовалось меньше логических чтений, так как в обеих существующих страницах данных имелось свободное место, и движку достаточно было только внести новую запись на листовую и промежуточную страницу.

Ниже приведены планы исполнения для выражений SELECT, приведенных выше.

Это выходит за рамки статьи, но я хотел бы обратить внимание на разницу между приведенным выше STATISTICS IO и приведенным ниже планом исполнения. На обновление INDEX1 потребовалось втрое больше логических чтений, но оптимизатор этого не заметил и оценил обе операции одинаково!

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

Заключение

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

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

Технология Клиент-Сервер 2010'4

Фактор заполнения (Fill Factor)

Автор: Брайан Эллул (Brian Ellul)

Опубликовано: 28.12.2010
Версия текста: 1.1

Тестовые сценарии

Table: _TEST1. INDEX1

Table: _TEST2. INDEX2

Производительность SELECT

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

Заключение

Технология Клиент-Сервер 2010'4

Фактор заполнения (Fill Factor)

Автор: Брайан Эллул (Brian Ellul)

Опубликовано: 28.12.2010Версия текста: 1.1

Тестовые сценарии

Table: _TEST1. INDEX1

Table: _TEST2. INDEX2

Производительность SELECT

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

Заключение

Опубликовано: 28.12.2010
Версия текста: 1.1