[WEBINAR] The New Normal: Dealing with the Reality of an Unsecure World

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.

Techopedia Deals

Connect with us

Techopedia on Linkedin
Techopedia on Linkedin
"Techopedia" on Twitter

Sign up for Techopedia's Free Newsletter!

Email Newsletter

Join thousands of others with our weekly newsletter

Free Whitepaper: The Path to Hybrid Cloud
Free Whitepaper: The Path to Hybrid Cloud:
The Path to Hybrid Cloud: Intelligent Bursting To Amazon Web Services & Microsoft Azure
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.