SQL Server:应用小于 100% 的填充因子后，预计索引增长量

Question

我在填充因子为 100% 的数据库中有大约 250 GB 的索引。为了提高性能，我想将填充因子降低到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

-

-使用填充因子创建一些索引

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 行，我认为它非常接近，因此您可以安全地使用该计算