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

In the first part, Beginning Performance Tuning with SQL Server, I discussed the common questions I usually ask my clients for understanding their server settings. In the second part, Quick Look at SQL Server Configuration for Performance Indications, I discussed about eight configurations which I went through quickly. I will be briefly discussing my opinion about these configurations in following blog post.

Today, we will talk about the very first factor which I start looking at from configuration values – Fill Factor. Fill Factor is directly related to Indexes. Every time we all here the word ‘Index,’ we directly relate it to performance. Index enhances performance ‑ this is true, but there are a several other options along with it.

This reminds me of a very funny incident; I have created a cartoon based on the same scenario (SQL SERVER – A Funny Cartoon on Index). In this blog post, we will go over the Fill Factor and my overall and “quick” opinion about the same.

Introduction to Fill Factor

The intention of this blog post series is to give a practical approach to the topic and not to discuss the theory or go behind the scene of the technology. I will limit my theory in one paragraph, if possible, to explain the subject and continue with the practical approach of the feature.

The two major questions related to Fill Factor are as follows:

For ease, we will divide the blog post in two parts. We will discuss “what is Fill Factor” in this blog post and “best value for the Fill Factor” in next blog post.

What is Fill Factor and what is the best value for it?

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. 

If the page is completely filled and new data is inserted in the table which belongs to completely filled page, the “page split” event happens to accommodate new data. When new data arrives, SQL Server has to accommodate the new data, and if it belongs to the page which is completely filled, SQL Server splits the page in two pages dividing the data in half. This means a completely filled page is now two half-filled pages. Once this page split process is over, the new data is inserted at its logical place. This page split process is expensive in terms of the resources. As there is a lock on the page for a brief period as well, more storage space is used to accommodate small amounts of data. The argument usually is that it does not matter as storage is cheaper now a day. Let us talk about Fill Factor in terms of IO in the next paragraph.

Fill Factor and IO

It is absolutely true that storage is getting cheaper every day. I remember few years ago, I purchased 256-GB Hard Drive at the price of USD 128. Yesterday I came across advertisement where 1-TB hard disk was sold at USD 99 (Please do not ask me where it was sold as the offer was valid for a very limited period of time). Yes, I agree that storage is cheaper nowadays, but I disagree with the argument of cheap storage. SQL Server is a database system, and it is not only a storage system. We read a large amount of the data from the SQL Server. Now let us assume that there is Table 1 which contains the data worth of 1000 pages. All these pages are 100% filled. If we run a query to retrieve all the data from the SQL Server, it should retrieve all the 1000 pages. If pages are only 50% filled to accommodate Table 1, we will need 2000 pages, which means SQL Server has to read twice the amount of the data from the disk, leading to higher usage of memory, CPU and IO bandwidth.

Reading previous paragraph gives us an impression that having Fill Factor 100 is the best option as there won’t be any empty space in the page and IO will be always optimal. Again, this is true if we never do any insert, update or delete in the table. If we insert new data, ‘Page Split’ will happen and there will be few pages which will have 50% Fill Factor now. An OLTP system which is continuously modified ‑ this has always been a challenge with regard to having the right Fill Factor. Additionally, note that Fill Factor only applies to a scenario when the index was originally created or index was rebuilt; it does not apply subsequently when page split happens. As discussed earlier, page split increases IO as well storage space.

Measuring Counters

All of the above discussions lead us to think that a higher Fill Factor and high transaction server implies higher page split. However, Fill Factor can help us to reduce the number of the page splits as the new data will be accommodated in the page right away without much difficulty and further page splits. We can measure the page split by the watching performance monitor counter “SQLServer:AccessMethods:Page Splits/Sec”.

Additionally, you can measure the same using the following sys query.

SELECT cntr_valueFROM MASTER.dbo.sysperfinfoWHERE counter_name ='Page Splits/sec' ANDOBJECT_NAME LIKE'%Access methods%'

Fill factor is usually measured at the server level as well as table level. Below we have the scripts for the same.

Here is the script to measure the Fill Factor at the server level:

SELECT *FROM sys.configurationsWHERE name ='fill factor (%)'

And, here is the script to measure the Fill Factor at the table/index level:

USE YourDatabaseName;SELECT OBJECT_NAME(OBJECT_ID) Name, type_desc, fill_factorFROM sys.indexes

I think this is good logical stop for this blog post today. In this blog post, we have tried to understand concepts such as Fill Factor, its significance, and page splits. We have also learned how to get the value for the same. In the next blog post, I will cover the most important question: “What is the Best Value for the Fill Factor?”