I recently learnt about the term Fill Factor while looking at indexing. Turns out it’s an important thing to know!
So, here’s a definition I read online
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. – http://sqlmag.com
So, there are two considerations you need to have when determining the correct percentage
- If you have an index where the key column could allow reordering
- If you have an index where the key column will always be the same order
Why do we need to consider these factors? Well if you have a key column that requires reordering (such has a date), you will want to leave some space at the end of each page so that new records can be attached to the end of the page. But when it comes to a column that is always the same order (such as an identity column. Perhaps an FK), then you should make use of all the space on the page. So go with a fill factor value of 100 for these.
Here’s an example of the syntax below
IF EXISTS (SELECT * FROM sys.indexes WHERE name='ix_MyTable_MyIndex' AND object_id = OBJECT_ID('MyTable')) BEGIN ALTER INDEX [ix_MyTable_MyIndex] ON [dbo].[Case] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 95 , PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , ONLINE = OFF , SORT_IN_TEMPDB = OFF ); END GO
I hope that all makes sense and helps you out!