Fillfactor

Fill factor is a SQL Server index setting that chooses the target page density for leaf-level index pages. The default value, 0, means pages are targeted at 100% full; another value specifies a percentage. Lowering it reserves space for later inserts, while fuller pages may suit data-warehouse-style workloads. section 11.1

Do not treat empty space as automatically wasteful or fragmentation as automatically bad. The useful setting depends on the workload: pages around 95% to 100% full can be appropriate for data-warehouse workloads, while busy transactional indexes may need substantially more room to reduce harmful page splits. section 11.1

Configure and maintain fill factor with these rules:

• Set it by rebuilding the index. For example, rebuilding an index with FILLFACTOR = 90 targets 90% density on its leaf pages. section 11.1
• Use PAD_INDEX = ON only when intermediate-level space also matters. It applies the same fill factor to intermediate pages; the single root page is unaffected. section 11.1
• Consider PAD_INDEX for very large transactional indexes suffering page splits, because a split can propagate upward through the B-tree. section 11.1
• Do not reorganize merely because external fragmentation is high. Reorganization condenses leaf pages up to fill factor but does not make already-full pages less dense. section 11.3

Measure before changing settings. sys.dm_db_index_physical_stats reports both external fragmentation through avg_fragmentation_in_percent and page density through avg_page_space_used_in_percent. Read results by B-tree level instead of averaging all levels: a sparse root page can make an index appear underfilled even when its leaf level meets the target density. section 11.3 section 11.4

ALTER INDEX ImpressionUID ON marketing.Impressions REBUILD 
WITH (
   FILLFACTOR = 90, 
   PAD_INDEX = ON
) ;

Use this ALTER INDEX example to set a 90% leaf-page target and enable the same target for intermediate pages during a rebuild. section 11.1

SELECT 
      i.name AS IndexName
    , ips.avg_page_space_used_in_percent 
        AS PageDensity                       #1
    , ips.index_level                        #1
    , ips.page_count                         #1
    , ips.record_count                       #1
FROM sys.dm_db_index_physical_stats(
                  DB_ID('Marketing')
                , OBJECT_ID('marketing.Impressions')
                , NULL
                , NULL
                , 'DETAILED'
    ) ips
INNER JOIN sys.indexes i
    ON ips.index_id = i.index_id
    AND ips.object_id = i.object_id 
    AND i.name = 'ImpressionUID' ;

Use the level-by-level diagnostic query to compare page density, page count, and record count instead of relying on a misleading index-wide average. section 11.4