[WEBINAR] Better to Ask Permission? Best Practices for Privacy and Security

Fill Factor

Definition - What does Fill Factor mean?

In SQL Server, the basic unit of storage is a leaf-level page. The fill factor determines what percentage of space on each page to fill with data. The fill factor value can be defined as a percentage from 1 to 100. Values of 0 and 100 are the same in all respects; both values indicate that the page is completely filled with data, and the default value is 0. If some other value is specified as the fill factor, for example 70, then it means that 70 percent of the leaf-level page is filled and 30 percent is left for future data. Therefore, if the fill factor is less than 100, then the remaining space is utilized for future data growth.

Techopedia explains Fill Factor

Fill factor is very important for database performance. It determines how the leaf-level data is stored and organized. A proper fill factor setting can improve database performance. Fill factor can be set in two ways: one is a generic fill factor set at server level, applied to all tables and indexes. It can also be set at an individual table/index level. For example, if the fill factor is set to 60 percent, then 60 percent of the page is filled with data and the remaining 40 percent will be saved for future data growth. Then, if new relevant data comes in which belongs to the same page, it is first saved within that 40 percent space.

However, if the page is completely filled, then the new data is accommodated by a page-split process. In a page split, the data is divided into two pages to accommodate the new data. Page splitting reduces database performance, as the retrieval of scattered data is expensive. There is no perfect formula to set a correct fill factor, but it varies for different applications and should be decided based on requirements.

Share this:

Connect with us

Email Newsletter

Join thousands of others with our weekly newsletter

The 4th Era of IT Infrastructure: Superconverged Systems
The 4th Era of IT Infrastructure: Superconverged Systems:
Learn the benefits and limitations of the 3 generations of IT infrastructure – siloed, converged and hyperconverged – and discover how the 4th...
Approaches and Benefits of Network Virtualization
Approaches and Benefits of Network Virtualization:
Businesses today aspire to achieve a software-defined datacenter (SDDC) to enhance business agility and reduce operational complexity. However, the...
Free E-Book: Public Cloud Guide
Free E-Book: Public Cloud Guide:
This white paper is for leaders of Operations, Engineering, or Infrastructure teams who are creating or executing an IT roadmap.
Free Tool: Virtual Health Monitor
Free Tool: Virtual Health Monitor:
Virtual Health Monitor is a free virtualization monitoring and reporting tool for VMware, Hyper-V, RHEV, and XenServer environments.
Free 30 Day Trial – Turbonomic
Free 30 Day Trial – Turbonomic:
Turbonomic delivers an autonomic platform where virtual and cloud environments self-manage in real-time to assure application performance.