Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Understanding SQL Server Index Fill Factor Setting


By:   |   Read Comments (6)   |   Related Tips: More > Indexing

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

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.

Solution

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.


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.

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.

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.

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%.

The resulting index will be created with pages being 50% full. I check and confirm this by running sp_spacused 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.

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



Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

Hi,


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

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

Well explained.

 

Thanks!


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

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

Thank you.

This is very good.

 


Friday, June 01, 2012 - 7:58:03 AM - Navnit Shukla Back To Top

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


Learn more about SQL Server tools