性能优化与故障排除百日谈(16)-索引的维护-设置填充因子-误区解析

性能优化与故障排除百日谈(16)-索引的维护-设置填充因子-误区解析

我们在之前的文章中已经多次提到了“填充因子”，并且在上一篇文章谈到索引碎片的时候，也知道了填充因子的重要性。尽管如此，其实我们很多的时候在建立索引，维护索引的时候并没有对填充因子过于太多的关注。

我们本文就来说说填充因子的话题。

在讨论之前，首先要声明一下：如何要比较合理的设置填充因子，一定要知道表中的数据的使用情况，还有索引所在列的类型和特性（如是否是采用GUID，还是Identity自增，还是采用其他的生成方式）。没有一个一蹴而就，完美的方案，一切都是随着情况而变化的，但是分析的思路是不变的，切记。

关于填充因子的概念我们这里不做太多的讨论，我们就来讨论几种需要关注填充因子的情况，同时也看看有关填充因子的一些误区。

 

 

填充因子的误区

 

以为填充因子是使用在数据插入的时候使用
 

这个误区可能是很多人所犯的，因为我们知道，所谓的填充因子，就是在确定页上面到底有多大的空间被填充，或者换句话说，也确定页上面有多少空白被保留。那么，这个保留的空间就是为了防止我们在索引页中插入新数据的时候发生不必要的也拆分，从而减少碎片，从而节省资源，提升性能。

 

相信很多的朋友都按照所讲述的进行理解的，其实这是有问题的。为了使得大家更加清楚的认识这个问题，我们通过一个例子来说明下。
 

在下面的示例中，我们将会一个表AgileSharp_FillFactorDemo上面建立一个聚集索引，这个索引的填充因子是50%。并且在数据表建立好了之后我，我们会插入一些数据进去，然后我们通过查看sys.dm_db_index_physical_stats这个动态管理函数来看看索引页中的空间的使用情况。

CREATE TABLE dbo.AgileSharp_FillFactorDemo
(
RowID int NOT NULL
,Column1 varchar(500)
);

ALTER TABLE dbo.AgileSharp_FillFactorDemo ADD CONSTRAINT
PK_AgileSharp_FillFactorDemo PRIMARY KEY CLUSTERED (RowID) WITH(FILLFACTOR = 50);

WITH L1(z) AS (SELECT 0 UNION ALL SELECT 0)
, L2(z) AS (SELECT 0 FROM L1 a CROSS JOIN L1 b)
, L3(z) AS (SELECT 0 FROM L2 a CROSS JOIN L2 b)
, L4(z) AS (SELECT 0 FROM L3 a CROSS JOIN L3 b)
, L5(z) AS (SELECT 0 FROM L4 a CROSS JOIN L4 b)
, L6(z) AS (SELECT TOP 1000 0 FROM L5 a CROSS JOIN L5 b)

INSERT INTO dbo.AgileSharp_FillFactorDemo
SELECT ROW_NUMBER() OVER (ORDER BY z) AS RowID, REPLICATE('X', 500)
FROM L6

然后运行下面的查询：

 

SELECT object_id, index_id, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.AgileSharp_FillFactorDemo'),NULL,NULL,'DETAILED')
WHERE index_level = 0

 

 

结果如下：

 

相信看到结果的朋友会非常吃惊：为什么填充因子没有起到预想的作用？

 

这个结果之所以没有按照大家的想法，是因为我们的理解有误。

 

其实，填充因子一开始不会在数据修改的过程中使用，而是在索引重建，重组和创建的时候使用。换句话说，就是在创建了索引，设置了填充因子之后，填充因子不会按照我们的期望发生作用，直到我们第一次重组或者重建了索引之后，此时填充因子才会起作用，而且后续的数据的修改（Insert，Update，Delete）才会使用收到填充因子的影响。

 

为了使得大家更加的清楚，我们还是继续看例子。

下面，我们在表上面把索引重建，如下：

ALTER INDEX PK_AgileSharp_FillFactorDemo ON dbo.AgileSharp_FillFactorDemo REBUILD

SELECT object_id, index_id, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.AgileSharp_FillFactorDemo'),NULL,NULL,'DETAILED')
WHERE index_level = 0

结果如下：

 

下面，为了证明上面的讲述，我们再来看一个例子，
首先，我们还是来建立一个表，如下：

 

CREATE TABLE dbo.AgileSharp_FillFactorConfirm
(
RowID int NOT NULL
,Column1 varchar(500)
);

ALTER TABLE dbo.AgileSharp_FillFactorConfirm ADD CONSTRAINT
PK_AgileSharp_FillFactorConfirm PRIMARY KEY CLUSTERED (RowID) WITH(FILLFACTOR = 50);

 

然后我们插入数据，我们此时插入的RowID都是偶数，如下：

declare @ID int
set @ID=0
while @ID<=2000
begin
set @ID=@ID+2;
Insert into AgileSharp_FillFactorConfirm values(@ID,'AgileSharp Confirm FillFactor')

end

数据就可能如下：

我们查看查看索引页的使用情况和索引页的个数，如下：

SELECT object_id, index_id, avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.AgileSharp_FillFactorConfirm'),NULL,NULL,'DETAILED')
WHERE index_level = 0

结果如下：

上述结果还是和我们之前讲述的一样：填充因子没有起作用。

然后我们重建索引，如下：

ALTER INDEX PK_AgileSharp_FillFactorConfirm ON dbo.AgileSharp_FillFactorConfirm REBUILD

然后再次查看，结果如下：

此时，我们看到，页的填充率大约是47%，我们可以试想：我们在表中插入奇数的RowID，此时页面数应该是不变的，而且填充率会变为94%。如果结果真是这样，那么就说明填充因子起作用了。
我们插入数据，如下：

declare @ID int
set @ID=1
while @ID<=2000
begin
set @ID=@ID+2;
Insert into AgileSharp_FillFactorConfirm values(@ID,'AgileSharp Confirm FillFactor')

end

然后，我们查看索引页的情况，如下：

好，结果如我们所望，也就证明了上面我们的说法。
其实大家可以查看页上面的详细信息，我这里就不多说了。