As a SQL Server DBA, one of the most important tasks is to optimize the performance of your database. One way to achieve this is by creating and maintaining the right number and quality of indices. In SQL Server, you can use the “FILLFACTOR” argument while creating an index to control how much each index page should be filled with data.

But here’s an interesting question: Where in SQL Server is “100” equals to “0”? The answer lies in the FILLFACTOR settings. Surprisingly, a FILLFACTOR value of 0 and 100 are considered equal in SQL Server.

Here are some key points to remember when using the FILLFACTOR argument:

1. If the fill-factor is set to 100 or 0, the Database Engine fills pages to their capacity while creating indexes.
2. The server-wide default FILLFACTOR is set to 0.
3. To modify the server-wide default value, use the sp_configure system stored procedure.
4. To view the fill-factor value of one or more indexes, use sys.indexes.
5. To modify or set the fill-factor value for individual indexes, use CREATE INDEX or ALTER INDEX statements.
6. Creating a clustered index with a FILLFACTOR less than 100 may significantly increase the amount of space the data occupies because the Database Engine physically reallocates the data while building the clustered index.

According to MSDN, FILLFACTOR specifies a percentage that indicates how much each index page should be filled with data during index creation or rebuild. The fill-factor option is designed to improve index performance and data storage. By setting the fill-factor value, you reserve free space on each page for future table growth.

For example, if you set a fill-factor value of 70, it means that 30% of each page will be left empty, providing space for index expansion as data is added to the underlying table. This empty space is reserved between the index rows rather than at the end of the index.

It’s important to note that the fill-factor setting applies only when the index is created or rebuilt. The SQL Server Database Engine does not keep the specified percentage of empty space in the pages after the index is created. Maintaining extra space on the data pages would be counterproductive as it would require page splits to maintain the specified fill-factor percentage.

Setting a FILLFACTOR value can have a significant impact on performance. By providing extra space for index expansion, you can avoid serious performance issues when new data is added to the underlying table. When a new row is added to a full index page, the Database Engine performs a page split, which can be a resource-intensive operation and cause fragmentation.

While a low fill-factor value (>0) may reduce page splits as the index grows, it can also increase the storage space required by the index. This can eventually impair the performance of your SELECT queries. For example, a fill-factor value of 50 can cause database read performance to decrease by two times, as the index contains more pages and increases the disk IO operations required to retrieve the data.

It is recommended to keep the default fill-factor for databases that have a prevalence of SELECT queries running against them, such as in Data Warehouses. However, if you are managing a database with a large number of INSERT and UPDATE queries, it is highly advisable to consider changing the default fill-factor settings.

In situations where existing rows are updated with data that lengthens the size of the rows, such as adding extra columns to the table, using a fill-factor less than 100 can help minimize page splits caused by the extra length in the rows. However, if all the data is added to the end of the table, the empty space in the index pages will not be filled, and it is recommended to set the FILLFACTOR to 100.

Understanding and utilizing the FILLFACTOR setting in SQL Server can greatly improve the performance of your database. By providing the right amount of space for index expansion, you can avoid resource-intensive operations and fragmentation, leading to faster and more efficient queries.

Here is a quick video that demonstrates how to change the fill factor: