Index Fragmentation - SQL Server Query Performance Tuning - page 254

Significance of the Fill Factor
The internal fragmentation of an index is reduced by getting more rows per leaf page in an index. Getting more
rows within a leaf page reduces the total number of pages required for the index and in turn decreases disk I/O and
the logical reads required to retrieve a range of index rows. On the other hand, if the index key values are highly
transactional, then having fully used index pages will cause page splits. Therefore, for a transactional table, a good
balance between maximizing the number of rows in a page and avoiding page splits is required.
SQL Server allows you to control the amount of free space within the leaf pages of the index by using the fill
factor. If you know that there will be enough INSERT queries on the table or UPDATE queries on the index key columns,
then you can pre-add free space to the index leaf page using the fill factor to minimize page splits. If the table is
read-only, you can create the index with a high fill factor to reduce the number of index pages.
The default fill factor is 0, which means the leaf pages are packed to 100 percent, although some free space is
left in the branch nodes of the B-tree structure. The fill factor for an index is applied only when the index is created.
As keys are inserted and updated, the density of rows in the index eventually stabilizes within a narrow range.
As you saw in the previous chapter's sections on page splits caused by UPDATE and INSERT , when a page split occurs,
generally half the original page is moved to a new page, which happens irrespective of the fill factor used during the
index creation.
To understand the significance of the fill factor, let's use a small test table with 24 rows.
IF (SELECT OBJECT_ID('dbo.Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;
GO
CREATE TABLE dbo.Test1 (C1 INT, C2 CHAR(999)) ;
WITH Nums
AS (SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Nums
WHERE n < 24
)
INSERT INTO dbo.Test1
(C1, C2)
SELECT n * 100,
'a'
FROM Nums ;
Increase the maximum number of rows in the leaf (or data) page by creating a clustered index with the default
fill factor.
CREATE CLUSTERED INDEX FillIndex ON Test1(C1);
Since the average row size is 1,010 bytes, a clustered index leaf page (or table data page) can contain a
maximum of eight rows. Therefore, at least three leaf pages are required for the 24 rows. You can confirm this in
the sys.dm_db_index_physical_stats output shown in Figure 13-18 .
Figure 13-18. Fill factor set to default value of 0