Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

What Is the Best Value for Your Fill Factor?

DZone's Guide to

What Is the Best Value for Your Fill Factor?

Learn how to find your tables' index fragmentation ratio and how that helps determine a proper fill factor value to help with efficient indexing.

· Database Zone ·
Free Resource

MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

The fill factor is directly related to indexing, and indexing is directly related to performance. So, for performance, your fill factor plays a very important role.

The fill factor determines the percent of each leaf-level page that will be filled with data. By default, it is 100 percent. That means all most all the space of an 8KB leaf-level page is filled with data.

Here in this article, I am not going to discuss what the fill factor is and how it reduces index fragmentation. That is what Google is for.

Here, I am going to discuss the best value that we can assign as your fill factor.

As a SQL developer, we always face this type of scenario. What is the value we should choose for the fill factor? Usually, we just go to the DBA and ask them. Sometimes, they say 70% or 80%.

But they don’t provide any explanation for it. Why did they choose that value without seeing a proper page split? It is really a mystery to me. Anyway, they are the boss, and they have full authority to manage the database.

But here, I am going to find and share what, in my personal opinion, the correct fill factor should be. You can try this or go with the DBA’s definition.

There is no calculative method or mathematical formula to find the correct fill factor. It totally depends on implementation and experience with indexing.

To understand it properly, here are a couple of scenarios.

Scenario 1

I have a table in an OLAP environment and there are no DELETE or UPDATE operations going on. New records are just inserted.

I suggest that there is no need to implement a fill factor, or go with the default fill factor of 100 percent.

Sometimes, an improper fill factor decreases the performance.

Now, let us assume that there is Table 1, which contains 1,000 pages worth of data. All these pages are 100% filled. If I run a query to retrieve all the data from the SQL Server, it should retrieve all 1,000 pages.

If pages are only 50% ready to accommodate Table 1, it 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.

Scenario 2

I have a table on OLTP environment and our INSERT/UPDATE/DELETE operations are huge.

We can find the fill factor of an existing table/index by

SELECT OBJECT_NAME(OBJECT_ID) Name, type_desc, fill_factor
FROM sys.indexes


First, don’t set any fill factor. Just create the index. After one or two weeks, observe the average index fragmentation.

If the table is static and no values changed, then there is no need to set any fill factor value.

If the table is not often updated, then set the fill factor value at 95%.

If the table is frequently updated, then set the fill factor value at 70 to 90%.

Situation for Fill Factor Fill Factor %
Static Table – The value of the table never changed 100
Tables Updated Less Often 95
Frequently Updated Table

70 to 90


But all this depends on the ratio of index fragmentation. We must observe the regular index fragmentation ratio and decide the fill factor accordingly — not to assign an arbitrary value our fill factor.

We can find the index fragmentation ratio by using this SQL statement:

SELECT b.name As [Table Name], c.name As [Index Name], avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('Practice'), NULL, NULL, NULL , NULL)a
INNER JOIN sys.tables b WITH (nolock) ON a.OBJECT_ID = b.OBJECT_ID
INNER JOIN sys.indexes c WITH (nolock) ON a.OBJECT_ID = c.OBJECT_ID
AND a.index_id = c.index_id
WHERE b.name = 'ORDER_PRICE'
AND c.name = 'PK_OrderProduct';

 

I hope this was informative!

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

Topics:
database ,indexing ,sql ,fill factor ,database performance ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}