聚类复合键高碎片和填充因子

Question

最近，我制作了一个表，用于保存用户的语言首选项，如下所示：

CREATE TABLE [dbo].[systemUserLangPreference](
  [systemUserID] [int] NOT NULL,
  [langID] [int] NOT NULL,
  [preferredOrder] [int] NOT NULL,
  [createdBy] [int] NOT NULL,
  [createdOn] [datetime] NOT NULL,
  [lastActionBy] [int] NOT NULL,
  [lastActionOn] [datetime] NOT NULL,
CONSTRAINT [PK_systemUserLangPreference] PRIMARY KEY CLUSTERED 
([systemUserID] ASC, [langID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

现在只有不到1,000行，所以性能在这个时候并不是什么大问题，但是，当我运行索引碎片报告时，无论我做什么，这个索引和更多的复合键总是以高分段的形式出现。

使用

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()

这一个索引将返回80%的avg碎片，即使在我重建它的时刻。我尝试使用填充因子，结果如下：

Fill 100%; Fragmentation 75%
Fill 80%; Fragmentation 80%
Fill 50%; Fragmentation 62.5%
Fill 10%; Fragmentation 19.44%
Fill 1%; Fragmentation 1.90%

看来我走得越低越好？我不确定我对这件事的理解是否正确。因为表主要是读的(插入新用户，我猜当他们学习一种语言时)，那么更高的填充因子不是更好吗？为什么它会导致立即分裂，这是一件坏事吗？

Answer 1

对于非常小的表，碎片不仅无关紧要，而且几乎不可能控制。前八页是从混合区段中分配的，这些区域几乎总是不连续的。只有在索引有超过8页之后，才会从统一区段分配额外的页面。

在少于1,000行的情况下，聚集索引将远低于64K，并且完全由混合区段分配(除非您开始通过调整填充因子来强制处理这个问题，正如您已经看到的那样)。

通过发出以下命令，您可以了解如何分配聚集索引的页面：

DBCC EXTENTINFO( DB_NAME, systemUserLangPreference, PK_systemUserLangPreference)

一旦索引足够大，分配到超过8页，您就可以通过删除索引，然后再创建索引，确保它们都是从统一的区段中分配出来的。尽管如此，对于这么小的表来说，碎片不会对性能产生影响，您可能不应该尝试通过修改填充因子来解决它。

降低填充因子实际上可能会产生有害的结果，因为每个页面上的记录较少，因此需要将更多的页面加载到内存中。这里还有更多关于为什么要小心填充因子。

有关页面和区段的详细信息，请参阅这篇文章。