Fill factor for SQL pages

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!

Subscribe

0 comments