Fill Factor
Fill factor is a value that determines the percentage of space on each leaf level page to be filled with data. In SQL Server data is stored in the pages the size of page is 8kb, depending on the row size the number of rows will be stored in the page. The default value for fill factor is 0 or 100, which means there will be no free space left in the page. If fill factor value set to 80% then while rebuilding index 20% SQL Server will leave 20% of free space in each page which provides expansion when new data is added to the underlying table.
If Fill Factor value is set to 100% or 0 then there is no space on the page for index expansion. In that case SQL Server will move 50% of data into new page and page split will happen and SQL Server need to read more number of pages to retrieve same number of records hence performance decreased due to more IO.
Fill factor can be set in two ways
1. At server level
2. At Index level
Create Index index_name on table_name with (fillfactor = 80)
Alter Index Index_name on table_name rebuild with (fillfactor = 80)
Check out the best practices of setting fill factor value from BOL or from expert’s blogs.
No comments: