Fill
factor is the value that determines the percentage of space on each leaf-level
page to be filled with data. In an SQL Server, the smallest unit is a page,
which is made of Page with size 8K. Every page can store one or more rows based
on the size of the row. The default value of the Fill Factor is 100, which is
same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server
to fill the leaf-level pages of an index with the maximum numbers of the rows
it can fit. There will be no or very little empty space left in the page, when
the fill factor is 100.
Fill
Factor is directly related to Indexes. Every time we all here the word ‘Index,’
we directly relate it to performance.
If the
page is completely filled and new data is inserted in the table which belongs
to completely filled page, the “page split” event happens to accommodate new
data. When new data arrives, SQL Server has to accommodate the new data, and if
it belongs to the page which is completely filled, SQL Server splits the page
in two pages dividing the data in half. This means a completely filled page is
now two half-filled pages. Once this page split process is over, the new data
is inserted at its logical place. This page split process is expensive in terms
of the resources. As there is a lock on the page for a brief period as well,
more storage space is used to accommodate small amounts of data.
Fill
factor is usually measured at the server level as well as table level. Below we
have the scripts for the same.
SELECT *
FROM sys.configurations
WHERE name ='fill factor (%)'
|
We can
measure the Fill Factor at the table/index level using below sql script.
USE AdventureWorks2012
SELECT OBJECT_NAME(OBJECT_ID) Name, type_desc, fill_factor
FROM sys.indexes
|
