Фактор заполнения SQL и производительность

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

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

Наименьшая единица в SQL-сервере — это страница, состоящая из страниц размером 8 КБ. Фактор заполнения указывает процентное значение, которое должно быть заполнено на странице данных данными на сервере SQL. Означает, что он определяет процент пространства на каждой конечной странице, который должен быть заполнен данными. Он играет жизненно важную роль в настройке производительности запросов.

Default value of Fill Factor : 0  or 100

Значение, установленное на 100, указывает на 100% использование пространства для хранения данных. Значение, установленное на 90, позволит заполнить страницу данных на 90%, а остальное пространство будет свободно. Это свободное пространство учитывается при обновлении или изменении данных и не используется для вставки новых записей.

Но вы можете подумать, почему мы просто не устанавливаем значение 100 и не используем все пространство? Почему нужно оставлять свободное место на каждой странице?

Попробуем ответить на этот вопрос на примере:

Скажем, у нас есть страницы, полностью заполненные данными, и теперь мы хотим добавить новые записи, тогда полностью заполненная страница теперь будет разделена на две наполовину заполненные страницы. Этот процесс разделения страниц является дорогостоящим. А также теперь в системе есть две страницы для чтения, что означает более высокое использование памяти, ЦП и пропускной способности ввода-вывода.

Чтобы установить коэффициент заполнения для индекса, мы можем использовать следующий код:

CREATE INDEX [index_name] ON [table_name] ([column_name])
WITH (FILLFACTOR = [fill_factor_value]

Запрос:

CREATE INDEX my_index ON my_table (my_column)
WITH (FILLFACTOR = 80)

Мы также можем изменить коэффициент заполнения существующего индекса с помощью следующего кода:

ALTER INDEX [index_name] ON [table_name] REBUILD WITH (FILLFACTOR = [fill_factor_value])

Запрос:

ALTER INDEX my_index ON my_table REBUILD WITH (FILLFACTOR = 80)

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

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

-- Create a table
CREATE TABLE MyTable (
   ID INT PRIMARY KEY,
   Name VARCHAR(50)
)
-- Insert some data
INSERT INTO MyTable (ID, Name)
VALUES (1, 'John'), (2, 'Mary'), (3, 'Bob'), (4, 'Alice'), (5, 'Tom')

-- Create an index with high fill factor (100%)
CREATE INDEX MyIndex_HighFillFactor ON MyTable (ID) WITH (FILLFACTOR=100)

-- Create an index with low fill factor (50%)
CREATE INDEX MyIndex_LowFillFactor ON MyTable (ID) WITH (FILLFACTOR=50)

-- Query the table using the high fill factor index
SELECT * FROM MyTable WHERE ID = 3

-- Query the table using the low fill factor index
SELECT * FROM MyTable WHERE ID = 3

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

Как определить наилучшее значение коэффициента заполнения?

При определении коэффициента заполнения необходимо учитывать множество факторов, некоторые из них:

• Скорость роста данных
• Размер и использование таблицы
• Частота обновления данных

1. Скорость роста данных: считается, что если ожидается, что таблица будет быстро расти, чтобы избежать частого перестроения индекса, вы можете сохранить более низкое значение коэффициента заполнения.
2. Частота обновления данных.Если вам необходимо часто обновлять данные, то для увеличения свободного места лучшим выбором будет более низкий коэффициент заполнения, поскольку это уменьшит разрыв страницы.
3. Размер таблицы. Если размер таблицы небольшой, то, чтобы избежать пустой траты места, предпочтительнее использовать более высокий коэффициент заполнения, и наоборот.
4. Мониторинг фрагментации индекса.Независимо от коэффициента заполнения важно регулярно поддерживать фрагментацию индекса и перестраивать индекс для повышения производительности.

Примечание. Фрагментация индекса — это ситуация, когда страницы данных в индексе логически искажены, слабо заполнены или переполнены.

Фактор заполнения таблицы

Запрос:

Имя таблицы: data

SELECT
   name AS index_name,
   (avg_page_space_used_in_percent/100) AS fill_factor
FROM
   sys.dm_db_index_physical_stats (DB_ID(), 
   OBJECT_ID('data'), NULL, NULL, 'DETAILED')
   JOIN sys.indexes ON indexes.object_id = 
   OBJECT_ID('data') AND indexes.index_id = index_id
WHERE
   index_level = 0;

Выход:

index_name  fill_factor
---------- -----------
PK_orders   0.75

Заключение

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