SQL Server: насколько ожидается рост индекса после применения коэффициента заполнения менее 100%
У меня около 250 ГБ индексов в базе данных с коэффициентом заполнения 100%. Для повышения производительности я хочу снизить коэффициент заполнения до 95%.
Поэтому перед применением коэффициента заполнения к моим индексам я хотел узнать, насколько увеличится размер индекса после применения 95% коэффициента заполнения.
Что я ожидаю, так это то, что 5% размера индекса будет расти, это правильно?
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 строка, я думаю, что она довольно близка, так что вы можете безопасно использовать этот расчет