SQL SERVER – What is Fill Factor and What is the Best Value for Fill Factor

Working in performance tuning area, one has to know about Index and Index Maintenance. For any Index the most important property is Fill Factor.

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.

I have written following two article about Fill Factor.

[Articles are relocated so links are disabled]

What is Fill factor? – Index, Fill Factor and Performance – Part 1

What is the best value for the Fill Factor? – Index, Fill Factor and Performance – Part 2

I strongly encourage read them and provide your feedback.

Here is a quick video about how to change fill factor:

Reference: Pinal Dave (http://blog.SQLAuthority.com)

9 thoughts on “SQL SERVER – What is Fill Factor and What is the Best Value for Fill Factor

  1. hi Pinal, 1st thanks for all your great posts!
    I have a funny situation on a table and will appreciate any suggestions:

    I have a very simple link table in my DB, 3 columns: 1st primary key clustered index bigint, 2nd and 3rd are foreign keys (bigint).
    The table is read 99.99% of the time with very few inserts so I use a fill factor of 100%.
    It is used in a lot of join statements.
    Only 2 indexes on the table.
    Problem is the fragmentation gets very high, very quickly.
    I have set the fill factor from 100% to 50% but the indexes still get fragmented in less than 24 hours on the SQL 2008 DB.
    There is a sql agent script defragmenting/reindexing the indexes every 24 hours too.
    There is also no disk fragmentation. Similar tables on my DB have 0% fragmentation.


  2. Hi Sir,

    I have a query that I was not able to find answer to so came to seek help, can we connect two diffrenct database say MySql 4.1 and SqlServer 2005, as well is there any possible way for the data migration between the two.


  3. Pingback: SQL SERVER – Correct Value for Fillfactor – Quiz – Puzzle – 24 of 31 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s