What is Fill Factor? Index, Fill Factor and Performance

A QUICK INTRODUCTION: WHAT IS FILLFACTOR, AND HOW IS IT SET?

“Fillfactor” is a setting for indexes in SQL Server. When you create or rebuild an index, you can tell SQL Server what percentage of each 8K data page used in the “leaf” level of the index it should fill up.

In other words, if you set a 90% fillfactor when you rebuild a clustered index, SQL Server will try to leave 10% of each leaf page empty. The empty space will be present on the page as it’s written on disk, and it’ll take up the same amount of space as it sits in memory.

By default, SQL Server uses a 100% fillfactor and tries to fill up all the pages in indexes as close to full as it can. Depending on how many rows actually fit on the page, your mileage may vary.

There are two ways to set fillfactor in SQL Server:

• At the SQL Server instance level using a sys.configurations setting for fill factor. (I don’t recommend using this option, for reasons I’ll describe below.)
• At an individual index level by specifying fillfactor when you create or rebuild the index.

Index maintenance jobs sometimes automate the second of these options. If you’re using an index maintenance stored procedure you picked up from around the web, you may be setting fillfactor without realizing it.

WHY DO PEOPLE LOVE TO SET FILLFACTOR?

DBAs and developers often read that lowering the fillfactor improves performance by reducing page splits. Perhaps they’re trying to fix a performance problem, or perhaps they’re feeling paranoid. They either lower fillfactor too much on some indexes, or apply a fillfactor change to all indexes.

Here’s the scoop: it’s true that the default fillfactor of 100% isn’t always good. If I fill my pages to the brim, and then go back and need to insert a row onto that page, it won’t fit. To make the data fit and preserve the logical structure of the index, SQL Server will have to do a bunch of complicated things (a “bad” type of page split), including:

• Add a new page
• Move about half the data to the new page
• Mark the data that was moved on the old page so it’s not valid anymore
• Update page link pointers on existing pages to point to the new page

And yep, that’s a lot of work. It generates log records and causes extra IO. And yes, if you have this happen a lot, you might want to lower the fillfactor in that index a bit to help make it happen less often.

WHERE DO PEOPLE MESS UP FILLFACTOR?

Here’s the thing: having a bunch of empty space on your data pages is ALSO bad for performance. Your data is more spread out so you probably have to read more pages into memory. You waste space in cache that’s just sitting there empty. That’s not only not awesome, it can be TERRIBLE in many cases.

This is particularly wasteful because not all indexes are prone to “bad” page splits. Let’s say I have a clustered index on an incrementing INT or BIGINT identity value. I insert loads of new rows and values are rarely updated or deleted. In this case I can fill my pages very full because I’m always putting new rows at the “end” of the index. Adding these new pages aren’t bad page splits– although unfortunately they are counted in the “page splits/sec” performance counter, which makes it very tricky to find just the “bad” splits.

I frequently find that people have put a fillfactor setting of 80 or below on all the indexes in a database. This can waste many GB of space on disk and in memory. This wasted space causes extra trips to storage, and the whole thing drags down the performance of your queries.

BEST PRACTICES FOR SETTING FILLFACTOR

Here’s some simple advice on how to set fillfactor safely:

1. Don’t set the system wide value for fillfactor. It’s very unlikely that this will help your performance more than it hurts.
2. Get a good index maintenance solution that checks index fragmentation and only acts on indexes that are fairly heavily fragmented. Have the solution log to a table. Look for indexes that are frequently fragmented. Consider lowering the fillfactor gradually on those individual indexes using a planned change to rebuild the index. When you first lower fillfactor, consider just going to 95 and reassessing the index after a week or two of maintenance running again. (Depending on your version and edition of SQL Server, the rebuild may need to be done offline. Reorganize can’t be used to set a new fillfactor.)

This second option may sound nitpicky, but in most environments it only takes a few minutes to figure out where you need to make a change. You can do it once a month. And it’s worth it– because nobody wants their database performance to slow down and realize that they’ve been causing extra IO by leaving many gigabytes of space in memory needlessly empty.

Now that we’ve covered the basics, how about those five things? Here’s the fine print on how fillfactor is implemented, and what it does and doesn’t impact.

What is the best value of Fill Factor?

This is complicated question, and it is very difficult to answer without checking the workload of the system.

• If your system static and just read only, a default Fill Factor of 100 (or 0) is ideal. As there is no insert, update or delete, having all the pages filled up makes sense.
•  If your system has lots of OLTP transactions, then a lowering Fill Factor (between 70 to 90) provides better result
•  If you are feeling that I did not give you any specific and clear answer, I will surely feel guilty. Yes, I did not give you a clear cut answer, but honestly, I cannot give you any answer regarding what is the right Fill Factor. In fact, the answer solely depends on you. You are the best judge regarding the appropriate Fill Factor that suits your system.

My Methodology for Fill Factor

Fill Factor matters at the two granular levels – server level and table/index level. We will discuss both the levels, one at a time.

Server Level Fill Factor

Before I start the project, I noted the counter “SQLServer:AccessMethods:Page Splits/Sec”. I lower the Fill Factor values to 95, 90, 85 and 80 and observed the counter over several days. Please note this process requires to rebuild all the indexes which may not be recommended. When I find the optimal (least) value for the counter, I consider that particular value as the optimal value for my Fill Factor. Please note this is not the best practice but something you should be aware of it.  

Thus far, all the discussions focused on the server level, and we assume that one server level setting will fix all the issues. Just like everything, there are many more things to discuss at the object level.

We are currently trying to find an optimal Fill Factor which leads us to least number of page splits and conservative usage of the space as well. The method which I have discussed earlier surely works at the server level if it is combined with the understanding of the index-level Fill Factor.

Earlier, I have blogged regarding how to change the server level Fill Factor. Click the following to read the article: Set Server Level FILLFACTOR Using T-SQL Script.

Index Level Fill Factor

We can set one generic server level Fill Factor, but that setting can be over-ridden by us at each index level. If we do not over ride it, then all the index will follow the server-level settings.

Now, I am going to roughly categorize the user tables into the following:

• Static tables – There are always few tables which are very static and do not get updated often, e.g., master tables.
• Tables Updated Less Often – There are often few tables which are summary tables and do not get updated often. However, at regular intervals, they have entries in the table.
• Frequently updated Tables – These are the most common tables in OLTP system. These tables are heavily updated very often.
• Tables with clustered index on the identity column – Here, all the new rows are inserted at the end of the table

analyze the entire database tables keeping the above four categories in my mind. Based on my categorization, I set the Fill Factor.

Static Tables – Set Fill Factor at 100 (or default server fill factor),

As these tables are never changing, keeping the Fill Factor at 100 is the best option. They conserve the space, and also there is no fragmentation.

Tables Updated Less Often – Set Fill Factor at 95.

These tables are more or less having characteristics like static tables. As they are not updated often, they do not encounter much issues. To accommodate the updates, I like to keep the Fill Factor 95. Honestly, if you are rebuilding the indexes at regular intervals, then I would prefer a Fill Factor of 95.

Frequently updated Tables – Set Fill Factor between 70 and 90.

When I have to set the Fill Factor at the table level, I first start from 90 and observe the table for a while, If I notice that there is still a recurring issue with page split, which in turn leads to fragmentation, I lower it further down towards 70 with an interval of the 5 at one times. Fill factor has to main balance between reads/writes.

Tables with Clustered Index on Identity Column – Set Fill Factor at 100.

This is very often seen in an OLTP system. Many tables have the identity column as a clustered index. In this case, all the new data is always inserted at the end of table and a new row is never inserted in the middle of the table. In this situation, the value of Fill Factor does not play any significant role and it is advisable to have the Fill Factor set to 100.

Using SQL Server Management Studio

To specify a fill factor by using Table Designer

1. In Object Explorer, click the plus sign to expand the database that contains the table on which you want to specify an index’s fill factor.
2. Click the plus sign to expand the Tables folder.
3. Right-click the table on which you want to specify an index’s fill factor and select Design.
4. On the Table Designer menu, click Indexes/Keys.
5. Select the index with the fill factor that you want to specify.
6. Expand Fill Specification, select the Fill Factor row and enter the fill factor you want in the row.
7. Click Close.
8. On the File menu, select Save table_name.

To specify a fill factor in an index by using Object Explorer

1. In Object Explorer, click the plus sign to expand the database that contains the table on which you want to specify an index’s fill factor.
2. Click the plus sign to expand the Tables folder.
3. Click the plus sign to expand the table on which you want to specify an index’s fill factor.
4. Click the plus sign to expand the Indexes folder.
5. Right-click the index with the fill factor that you want to specify and select Properties.
6. Under Select a page, select Options.
7. In the Fill factor row, enter the fill factor that you want.
8. Click OK.