SQL Server: насколько ожидается рост индекса после применения коэффициента заполнения менее 100%

Question

У меня около 250 ГБ индексов в базе данных с коэффициентом заполнения 100%. Для повышения производительности я хочу снизить коэффициент заполнения до 95%.

Поэтому перед применением коэффициента заполнения к моим индексам я хотел узнать, насколько увеличится размер индекса после применения 95% коэффициента заполнения.

Что я ожидаю, так это то, что 5% размера индекса будет расти, это правильно?

Answer 1

Я думал об этом прошлой ночью, и логически это имело бы смысл, поэтому я решил проверить это:

- Создать тестовый стол

CREATE TABLE TableIndexTest (id INT, id2 INT, var VARCHAR(100))

- Заполните некоторые значимые данные

DECLARE @num     INT = 1000000
DECLARE @var     VARCHAR(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @num2    INT = 1000000
DECLARE @iter    INT = 1000000
DECLARE @num_curr INT = 1
WHILE (@num_curr <= @num)
BEGIN
    INSERT TableIndexTest (id, id2, var) SELECT @num_curr, @num_curr, @var
    SELECT @num_curr = @num_curr + 1
END

- Создать индекс с помощью fillfactor

CREATE NONCLUSTERED INDEX ix_test_fillfactor100 ON TableIndexTest ([id], [id2], [var])
CREATE NONCLUSTERED INDEX ix_test_fillfactor90 ON TableIndexTest ([id], [id2], [var]) WITH (FILLFACTOR = 90)
CREATE NONCLUSTERED INDEX ix_test_fillfactor80 ON TableIndexTest ([id], [id2], [var]) WITH (FILLFACTOR = 80)
CREATE NONCLUSTERED INDEX ix_test_fillfactor70 ON TableIndexTest ([id], [id2], [var]) WITH (FILLFACTOR = 70)
CREATE NONCLUSTERED INDEX ix_test_fillfactor60 ON TableIndexTest ([id], [id2], [var]) WITH (FILLFACTOR = 60)
CREATE NONCLUSTERED INDEX ix_test_fillfactor50 ON TableIndexTest ([id], [id2], [var]) WITH (FILLFACTOR = 50)

- Проверить размеры индекса

SELECT
    i.name AS IndexName,
    SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = OBJECT_ID('TableIndexTest')
GROUP BY i.name
ORDER BY SUM(s.used_page_count) * 8;

Ожидаемым результатом будет то, что индекс FillFactor, установленный на 50, будет вдвое меньше индекса без установленного коэффициента заполнения (по умолчанию для сервера установлено значение 0, поэтому, по сути, оно будет равно 100).

Результаты:

    IndexName              IndexSizeKB
ix_test_fillfactor100         52008
ix_test_fillfactor90          57176
ix_test_fillfactor80          64480
ix_test_fillfactor70          73952
ix_test_fillfactor60          85752
ix_test_fillfactor50          103328

Таким образом, FillFactor 50 * 2 = 104 016, когда ожидается, будет 103,328, но, учитывая, что на последней странице индекса потенциально может быть только 1 строка, я думаю, что она довольно близка, так что вы можете безопасно использовать этот расчет