Index Fragmentation - SQL Server Query Performance Tuning - page 255

Note that avg_page_space_used_in_percent is 100 percent, since the default fill factor allows the
maximum number of rows to be compressed in a page. Since a page cannot contain a part row to fill the page fully,
avg_page_space_used_in_percent will be often a little less than 100 percent, even with the default fill factor.
To reduce the initial frequency of page splits caused by INSERT and UPDATE operations, create some free space
within the leaf (or data) pages by re-creating the clustered index with a fill factor as follows:
ALTER INDEX FillIndex ON dbo.Test1 REBUILD
WITH (FILLFACTOR= 75);
Because each page has a total space for eight rows, a fill factor of 75 percent will allow six rows per page. Thus, for
24 rows, the number of leaf pages should increase to four, as in the sys.dm_db_index_physical_stats output shown
in Figure 13-19 .
Figure 13-19. Fill factor set to 75
Note that avg_page_space_used_in_percent is about 75 percent, as set by the fill factor. This allows two more
rows to be inserted in each page without causing a page split. You can confirm this by adding two rows to the first set
of six rows ( C1 = 100 - 600, contained in the first page).
INSERT INTO dbo.Test1
VALUES (110, 'a'), --25th row
(120, 'a') ; --26th row
Figure 13-20 shows the current fragmentation.
Figure 13-20. Fragmentation after new records
From the output, you can see that the addition of the two rows has not added any pages to the index. Accordingly,
avg_page_space_used_in_percent increased from 74.99 percent to 81.25 percent. With the addition of two rows to
the set of the first six rows, the first page should be completely full (eight rows). Any further addition of rows within the
range of the first eight rows should cause a page split and thereby increase the number of index pages to five.
INSERT INTO dbo.Test1
VALUES (130, 'a') ; --27th row
Now sys.dm_db_index_physical_stats displays the difference in Figure 13-21 .
Figure 13-21. Number of pages goes up