Fill Factor

The Quick Scan Report has detected that your fill factor is too low.

In SQL Server, the fill factor is a configuration option that determines how much space is left on each page of a data file when it is created or reorganized. The fill factor specifies the percentage of space that will be filled on each page, with the remaining space left as free space.

The fill factor is specified as a percentage value, with a default value of 100. This means that by default, each page of a data file will be completely filled with data, with no free space left. However, administrators can specify a different fill factor value to leave some free space on each page.

Leaving free space on each page of a data file can provide several benefits, such as:

• Improving insert performance: When a new row is inserted into a page that is already full, SQL Server has to perform an operation known as a page split to make room for the new row. This can impact insert performance, as page splits can be expensive operations. By leaving free space on each page, page splits can be avoided, which can improve insert performance.
• Reducing index fragmentation: When a page split occurs, the data in the index can become fragmented, which can impact query performance. By leaving free space on each page, page splits can be avoided, which can help reduce index fragmentation and improve query performance.
• Reducing file growth: When a data file is full, SQL Server has to allocate additional space to the file to make room for new data. This can cause the file to grow rapidly, which can impact performance and consume disk space. By leaving free space on each page, the file can grow more slowly and use disk space more efficiently.

To specify the fill factor for a data file, the administrator can use the CREATE or ALTER INDEX statement, and specify the FILLFACTOR option. For example:

CREATE INDEX MyIndex
ON MyTable (MyColumn)
WITH FILLFACTOR = 80;

In this example, the fill factor for the MyIndex index on the MyTable table is set to 80%, which means that each page of the index will be filled with data to 80% capacity, with 20% free space left.

By carefully choosing the fill factor value for data files, administrators can improve performance and use disk space more efficiently. This can help ensure that the database is running smoothly and efficiently.

We recommend setting the default fill factor value to something higher than 90%.

Enroll Today!

---

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!