在淺談INDEX與FillFactor一篇有談到FillFactor的設定會與Fragmentation產生有相當大的關連。這篇實際測試FillFactor對Index的影響。

測試一：建立Index並設定FillFactor為100

IF OBJECT_ID('TestNoFillFactorTable') IS NOT NULL
BEGIN
DROP TABLE TestNoFillFactorTable
END
GO

--建立測試的Table
CREATE TABLE TestNoFillFactorTable
(
COLUMN1 INT ,
Data CHAR(200)
)
GO

--建立測試資料
DECLARE @i INT
SET @i = 1
WHILE @i <= 1000
BEGIN
INSERT INTO dbo.TestNoFillFactorTable VALUES(@i,'RYO')
SET @i = @i + 1
END

--建立叢集索引
CREATE CLUSTERED INDEX [IX_TestFillFactor100Table] ON [dbo].[TestNoFillFactorTable] (	COLUMN1)
WITH (FILLFACTOR = 100)
GO

--建立測試資料
DECLARE @i INT
SET @i = 1
WHILE @i <= 1000
BEGIN
INSERT INTO dbo.TestNoFillFactorTable VALUES(@i,'RYO')
SET @i = @i + 1
END

--查看INDEX的碎裂程度
SELECT DB_NAME() DBName,i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [FragmentationPercentage]
FROM sys.dm_db_index_physical_stats(DB_ID('Performance'),NULL, NULL, NULL, NULL) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE i.name='IX_TestFillFactor100Table'
ORDER BY 3 DESC
GO

執行結果：

測試二：建立Index並設定FillFactor為80

--設定FillFactor
IF OBJECT_ID('TestFillFactorTable') IS NOT NULL
BEGIN
DROP TABLE TestFillFactorTable
END
GO

--建立測試的Table
CREATE TABLE TestFillFactorTable
(
COLUMN1 INT ,
Data CHAR(200)
)
GO


--建立測試資料
DECLARE @i INT
SET @i = 1
WHILE @i <= 1000
BEGIN
INSERT INTO dbo.TestFillFactorTable VALUES(@i,'RYO')
SET @i = @i + 1
END

--建立叢集索引
CREATE CLUSTERED INDEX [IX_TestFillFactor80Table] ON [dbo].[TestFillFactorTable] (	COLUMN1) 
WITH (FILLFACTOR = 80)
GO

--建立測試資料
DECLARE @i INT
SET @i = 1
WHILE @i <= 1000
BEGIN
INSERT INTO dbo.TestFillFactorTable VALUES(@i,'RYO')
SET @i = @i + 1
END

--查看INDEX的碎裂程度
SELECT DB_NAME() DBName,i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [FragmentationPercentage]
FROM sys.dm_db_index_physical_stats(DB_ID('Performance'),NULL, NULL, NULL, NULL) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE i.name='IX_TestFillFactor80Table'
ORDER BY 3 DESC
GO

執行結果：

比較結果：

結論：由上述測試可知設定FillFactor為80%時，因為尚餘20%的空間可以再新增資料時讓Fragmentation產生量變小，而設定FillFactor為100%時因為Page沒有多餘的空間，所以新增資料時會用到一個新的Page儲存資料導致產生Fragmentation。所以依情況設定適當的FillFactor可以減少Fragmentation產生並提升查詢效能。