SQL SERVER – Fillfactor, Index and In-depth Look at Effect on Performance

I would like to start this post with an interesting question:

Where in MS SQL Server is “100” equals to “0”?  And I am not talking about data types now..

Today I will be presenting the answer to this question and some topics related to it.

Creating Indices in SQL Server is one of the most important tasks of any SQL DBA. Performance of your database is directly depends on your skills and proficiency in creating and maintaining the right number and quality of indices..

As a DBA, you can use “FILLFACTOR,” which is one of the important arguments that can be used while creating an index.

Now, the answer to the above mentioned question–

Fill-factor settings of 0 and 100 are equal!

Points to remember while using the FILLFACTOR argument:

1. If fill-factor is set to 100 or 0, the Database Engine fills pages to their capacity while creating indexes.

2. The server-wide default FILLFACTOR is set to 0.

3. To modify the server-wide default value, use the sp_configure system stored procedure.

4. To view the fill-factor value of one or more indexes, use sys.indexes.

5. To modify or set the fill-factor value for individual indexes, use CREATE INDEX or ALTER INDEX statements.

6. Creating a clustered index with a FILLFACTOR < 100 may significantly increase the amount of space the data occupies because the Database Engine physically reallocates the data while building the clustered index.

More about FILLFACTOR

According to MSDN, FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild.

Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.

Specifying a fill-factor value of 70 would implies that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table.

The empty space is reserved between the index rows rather than at the end of the index.

The fill-factor setting applies only when the index is created or rebuilt.

The SQL Server Database Engine does not keep the specified percentage of empty space in the pages AFTER the index is created. Trying to maintain the extra space on the data pages would be counterproductive because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered. What it means for you as SQL DBA is that when you set FILLFACTOR at 80 and create Index with these settings, your index has 20% space to grow freely without need for page splits that affect the performance!

You can avoid serious performance issues by providing extra space for index expansion when data is added to the underlying table,.Usually, when a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This is known as a page split.

While making room for new records, page split can take time to perform; it is a resource intensive operation. Also, it can cause fragmentation that leads to increased I/O operations. When frequent page splits occur, it is advisable to rebuild the index while setting an appropriate fill-factor value to redistribute the data. Although a low fill-factor value (>0) may reduce the page splits as the index grows, the index may require more storage space. It will keep creating new, “half-empty” pages, and it can eventually impair performance of your “SELECTs”. For example, a fill-factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, thus increasing the disk IO operations required to retrieve the data. Specifying a fill factor other than the default can decrease database read performance by an amount inversely proportional to the fill-factor setting.

Summary

It is recommended to keep default fill-factor for the databases that have prevalence of SELECT queries running against them such as in Data Warehouses, and it is highly advisable to consider changing your default fill-factor settings if you are managing database with a large number of INSERT and UPDATE queries hitting it. In such databases, a nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table.

Consider a situation wherein the existing rows are updated with data that lengthens the size of the rows; for instance, scenarios when you add extra columns to the table. You should consider the use of a fill factor that is less than 100. The extra space on each page will help to minimize page splits caused by extra length in the rows. However, if all the data is added to the end of the table, the empty space in the index pages will not be filled. For example, if the index key column is an IDENTITY column, the key for new rows is always increasing and the index rows are logically added to the end of the index. In such a situation, you should set your FILLFACTOR to 100.

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

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

28 thoughts on “SQL SERVER – Fillfactor, Index and In-depth Look at Effect on Performance

  1. Hi Pinal, Great Post. I totally agree “if the index key column is an IDENTITY column, the key for new rows is always increasing and the index rows are logically added to the end of the index”
    I just believe there is not a rule to say that the if index key column is an IDENTITY column, you can configure the fillfactor with 100%
    Even if you have index key column is an IDENTITY and configuring fillfactor to 100% if you have variable-length columns (varchar, nvarchar, varbinay) and they suffer constant change or change more than insertion, you will probably have problems with page splits . In these cases perhaps, even though the identity column, leaving a smaller fillfactor may be more appropriate.

    Like

  2. Pinal,That is very clear explanation. before I never know why we care about fillfactor. I ‘ve always used your site for help. Thank you so much for your all very awesome articles.

    Like

  3. Hi Pinal,

    Nice Post.

    But, I have a question here. In order to avoid page splits we use fill factor. If we don’t specify fill factor on a table, inorder to accommodate a new record page split happens, and while page split happening, it moves half of the page to another page and this is costly.

    Lets say if i have mentioned as fillfactor 80, means it will reserve 20% of every page for new records. what if this 20% of space also filled with new records, what will happen at that time?

    If it results in a page split, what is the difference of having a page split at the insertion or very first record or after sometime?

    Please answer my question…

    Like

  4. Hello Srinivas,

    Yes, when free space specified by fillfactor, fills after few insertion, page split wll occure. The benifit of fillfactor is to allow insert/update until the free space not filled. The free space specified by fill factor is reserved everytime the index is rebuild or defragmented. So we just have to plan a proper schedule to degrag/rebuild the indexes to maintain the free space and to avoid the page split.

    Kind Regards,
    Pinal Dave

    Like

  5. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 20 of 31 Journey to SQLAuthority

  6. Hey, I am in the process of preparing for 70-450 exam and I came across this question on the net. I eliminated the two answers, B,C. Index is performing fine, so no need to rebuild or reorganize. Can you please help.

    QUESTION::::
    You are designing index maintenance strategies. One large table has a clustered index which never has a fragmentation percentage of more than 2 percent. There is an XML column in the table, and INSERTs are run frequently against this table. You want to minimize the impact of reindexing operations on performance.

    A) Drop the clustered index on the large table.
    B) Rebuild the clustered index of the large table monthly.
    C) Exclude the clustered index from reorganizing and rebuilding.
    D) Reduce the fillfactor on the clustered index.

    Like

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

  8. Let me know how sql index is performing during select and insert into.
    For query optimization we are using SQL Index. When we insert value into the database with or without using sql index. Which one will be faster..using sql index to insert or without index to insert?

    Like

  9. Hi Pinal,

    This is very use full information about fill factor, can you please give me some tips for the selectivity of non clustered indexes on very big table in a data warehouse.

    I have one table with approx 90 GB data size & 150 GB index size. Should I remove some nonclustered indexes from my table because all scripts are taking long time to run. Doing horizontal data partitioning on this table with partitioned indexing.

    Like

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  11. I have several indexes that seem to stay above 80% fragmentation. I can rebuild them and get them down around 30%, but within minutes they are back above 80%. I noticed that all of these indexes have a FillFactor of 0. Could this be the problem?

    Like

  12. you are copying text directly from Microsoft’s website. While I know at one point in this article, you said according to Microsoft. If you are going to quote them, please put quotes around it. Otherwise it is a copyright infringement.

    Like

  13. Thank you , as always trying to explain simple,
    that can be understood well. instead of posting complex explanations that is hard to understand.

    Like

  14. Pingback: SQL SERVER – Change Fill Factor – SQL in Sixty Seconds #069 | Journey to SQL Authority with Pinal Dave

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