Maintaining Indexes - Microsoft SQL Server 2012 Performance Tuning - page 230

The default Fill Factor value is 0 (zero), which means that it will not keep any free space on
the page.
How to do it...
Once it is decided which table or index needs to set the Fill Factor, change it with the following
T-SQL query:
--altering Index for FillFactor 80%
ALTER INDEX [idx_refno] ON [ordDemo]
REBUILD WITH (FILLFACTOR= 80)
GO
-- If there is a need to change the default value of Fill
-- Factor at server level, use the following T-SQL
--setting default value server-wide for Fill Factor
--turning on advanced configuration option
Sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
--setting up default value for fill factor
sp_configure 'fill factor', 90
GO
RECONFIGURE
GO
How it works...
As already defined in the discussion in the introduction of this recipe, Fill Factor is nothing but
the value in percentage that is used while initial filling up of data pages. If the Fill Factor value
is 90, the data page will be filled up to 90 percent initially, keeping 10 percent of data page
blank for future use, so that while executing DML commands, it finds room for new records
and can avoid page splits at the time of DML execution preventing I/O overheads.
It is good that we can avoid page split by using Fill Factor, but again, use it
wisely, because if we keep more space blank in data pages, it will increase
the number of data pages, and while executing the SELECT statement, more
data pages will need to be scanned.