Understanding SQL Server Index Fill Factor Setting

By:   |   Comments (6)   |   Related: > Indexing


Every database user, be it an end user, a developer or an administrator knows that indexes are one of the primary ways of making a query perform better. It is known that having indexes on large tables helps the Database Management System by using the existing indexes when queries need them by doing index seeks vs scans. When creating or rebuilding indexes you have the option of specifying a fill factor. What is an index fill factor? What is the significance of the value, when and why should it be changed? I was asked these questions quite a few times in training sessions and also found these questions in many forum posts. In this tip I will go over what fill factor is and how it impacts performance.


Introduction to Fill Factor

The Fill Factor specifies the % of fullness of the leaf level pages of an index. When an index is created or rebuilt the leaf level pages are written to the level where the pages are filled up to the fill factor value and the remainder of the page is left blank for future usage. This is the case when a value other than 0 or 100 is specified. For example, if a fill factor value of 70 is chosen, the index pages are all written with the pages being 70 % full, leaving 30 % of space for future usage.

For this example I am using a table named tblTransactionTable. The image below displays the sp_spaceused command and the output from the command to display table information such as space used for the table and the underlying indexes. This table has 11.7 million rows and there are no indexes currently on this table.

sql query

Step 1 - High Fill Factor Value

From SQL Server Management Studio, I am creating a non-clustered index with one column, CustomerID, as the Index Key.

new index

I then move to the Options page and set the Fill Factor to 100 %. If the value for the fill factor is not set, the index is created using the value from the default index fill factor value set at the instance level. Remember that a value of 0 and 100 are both same and the page will be completely filled. For this example we will ignore the other options on this page. I finish creating the index by clicking OK.

new index

After creating the index, I run sp_spaceused again to view the table information and find that the index size is now 327040 KB, where before it was 72KB before we created the index.

sql query

You might choose a high fill factor value when there is very little or no change in the underlying table's data, such as a decision support system where data modification is not frequent, but on a regular and scheduled basis. Such a fill factor value would be better, since it creates an index smaller in size and hence queries can retrieve the required data with less disk I/O operations since it has to read less pages.

On the other hand if you have an index that is constantly changing you would want to have a lower value to keep some free space available for new index entries. Otherwise SQL Server would have to constantly do page splits to fit the new values into the index pages.

Step 2 - Low Fill Factor Value

Next, I drop the index and create the same index with a change in the fill factor value set to 50. When I created the index earlier (in Step 1), I chose to script the create index task which is used in this step (image below) which will change the fill factor value to 50%.

sql query

The resulting index will be created with pages being 50% full. I check and confirm this by running sp_spaceused again which displays the table details. The image below is the result set displayed when the query is executed. Notice that the index size is now 651640 KB which is approximately twice the size of the index created earlier using fill factor as 100. Which makes sense since we left the pages half empty.

sql query

Using a fill factor value of less than 100 would be necessary in situations where data is added to the underlying indexes more frequently. With new data records added, the index pages need to have sufficient space to take the new entries. When there is not enough space a page split needs to occur which could impact performance depending on how frequently page splits need to occur.

Some points to be remembered

  • When 0 or 100 is specified, the page is filled to the maximum possible, not 100 % since a page may not accommodate the Index Key the final time and results in leaving that much empty space unused.
  • The size of the index is proportional to the columns that are used in the index, hence more columns (the limit being 16) will create a larger index and therefore require more index pages to store the index
  • Since indexes need storage space, creating an appropriate index along with the fill factor should be well planned.
  • Before finalizing a fill factor value, it should be tested and analyzed before deploying on the actual SQL Server.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Bru Medishetty Bru Medishetty focuses on Performance Monitoring and Tuning as well as planning and implementing Disaster Recovery solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, July 22, 2014 - 8:45:18 PM - syed Back To Top (32831)


Can you please guide me, I have a single instance with 2 databases, One is used for online transactions and other for reporting purpose...

How to set different fill factor values for two different databases on a single instance?

Wednesday, June 18, 2014 - 9:03:04 AM - vijay Back To Top (32293)

Hi Bru Medishetty,


Nice Explanation

I would like to know how the free space will be filled in future we need to increase fill factor manually?

Or sql server will automatically adjust the fill factor when the new data is coming into the table.



Can you please give me some explanation on this?

Sunday, July 28, 2013 - 8:19:10 PM - Elvin Back To Top (26037)

Well explained.



Monday, July 22, 2013 - 10:29:51 AM - Rajasekhar Back To Top (25937)

How can we Reduce the page splits by doing of which one of the fallowing(A or B)?

A)By Giving 0 or 100 as Fill factor

B)By Giving Fill factor as 0<Fill factor<100

Monday, June 25, 2012 - 10:23:24 PM - choim Back To Top (18197)

Thank you.

This is very good.


Friday, June 1, 2012 - 7:58:03 AM - Navnit Shukla Back To Top (17773)

Its a really very nice artilce , Could you please guide me how can i implement and test fill factor effect in our QA enviroement.

get free sql tips
agree to terms