In SQL Server, a fill factor is a percentage value that determines how much space on each index page should be left empty, or "free," when the index is created or rebuilt. The fill factor is used to control the amount of fragmentation in the index and can affect the performance of queries that use the index. For example, if the fill factor is set to 80%, each index page will be 80% full when it is created or rebuilt. This means that 20% of the space on each page will be left free, to accommodate new rows as they are added to the index. The remaining 80% of the space will be used to store data, which can help to improve query performance. A lower fill factor can reduce the amount of fragmentation in an index, but it can also increase the size of the index and slow down insert operations. A higher fill factor can make the index smaller and improve insert performance, but it can also increase the likelihood of fragmentation and slow down query performance. It's important to choose an appropriate fill factor based on the characteristics of your data and workload. You can set the fill factor when you create or rebuild an index using the FILLFACTOR option.
The default fill factor for SQL Server is 0 (zero).
When the fill factor is set to 0, it means that SQL Server will use the fill factor setting that is specified in the Server Configuration Options for the instance of SQL Server. By default, the Server Configuration Option for the fill factor is set to 100, which means that all index pages will be filled to 100% capacity.
What is the recommended setting for fill factor
The recommended setting for fill factor in SQL Server can vary depending on your specific database and workload. Generally, a fill factor between 70-90% is considered a good starting point for most workloads. If your database is mostly read-heavy, you may benefit from a higher fill factor, such as 90%, to reduce the amount of fragmentation in the index and improve query performance. On the other hand, if your database is write-heavy with a lot of insert or update operations, you may benefit from a lower fill factor, such as 70%, to reduce the overhead of maintaining the index and improve insert performance. It's also important to consider the size of your data and the growth rate of your database when choosing a fill factor. If your data is relatively static and your database is not expected to grow significantly, a higher fill factor may be more appropriate. However, if your data is expected to grow rapidly or your database is frequently updated, a lower fill factor may be more suitable to avoid frequent index maintenance and reduce fragmentation. Ultimately, the best fill factor setting will depend on the specific characteristics of your database and workload. It's recommended to test different fill factor settings and monitor the performance of your database to determine the optimal value for your environment.
How can I check current fill factor for every index
You can check the fill factor for every index in a SQL Server database by running a query against the sys.indexes catalog view. Here's an example query that returns the name of each index in the database, along with its fill factor:
SELECT OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
i.fill_factorFROM sys.indexes i
WHERE i.index_id > 0This query selects the TableName, IndexName, and fill_factor columns from the sys.indexes view, which contains metadata about all indexes in the database. The WHERE clause filters out any system-defined indexes, which have an index_id of 0.
The results of this query will include every index in the database, along with its fill factor. You can use this information to identify any indexes with a suboptimal fill factor and adjust the setting as needed.
How query optimal fill factor for each index
To query the optimal fill factor for each index in a database using T-SQL, you can use the following script:
SELECT
DB_NAME() AS DatabaseName,
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
100 - ((SELECT COUNT(*) FROM sys.dm_db_index_physical_stats(DB_ID(), i.object_id, i.index_id, NULL, NULL)) * 100 / (SELECT COUNT(*) FROM sys.dm_db_index_physical_stats(DB_ID(), i.object_id, NULL, NULL, NULL))) AS OptimalFillFactor
FROM
sys.indexes i
WHERE
i.type_desc <> 'HEAP';
This script uses the sys.indexes and sys.dm_db_index_physical_stats views to retrieve information about each index in the database, including the table name, index name, and number of pages used. It then calculates the optimal fill factor based on this information and returns the results.
Set Optimal Fill Factor For Each Index
To set the optimal fill factor for each index in a database using T-SQL, you can use the following script:
DECLARE @DatabaseName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @FillFactor INT;
DECLARE curIndexes CURSOR FORSELECTDB_NAME() AS DatabaseName,
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexNameFROMsys.indexes iWHEREi.type_desc <> 'HEAP';
OPEN curIndexes;
FETCH NEXT FROM curIndexes INTO @DatabaseName, @TableName, @IndexName;
WHILE @@FETCH_STATUS = 0BEGINSET @FillFactor = 100 - ((SELECT COUNT(*) FROM sys.dm_db_index_physical_stats(DB_ID(@DatabaseName), OBJECT_ID(@TableName), OBJECT_ID(@IndexName), NULL, NULL)) * 100 / (SELECT COUNT(*) FROM sys.dm_db_index_physical_stats(DB_ID(@DatabaseName), OBJECT_ID(@TableName), NULL, NULL, NULL)));
IF @FillFactor < 90SET @FillFactor = 90;
PRINT 'Setting fill factor of ' + CAST(@FillFactor AS NVARCHAR(10)) + ' for index ' + @IndexName + ' on table ' + @TableName + ' in database ' + @DatabaseName + '...';
ALTER INDEX @IndexName ON @TableName REBUILD WITH (FILLFACTOR = @FillFactor);
FETCH NEXT FROM curIndexes INTO @DatabaseName, @TableName, @IndexName;
ENDCLOSE curIndexes;
DEALLOCATE curIndexes;
This script uses a cursor to iterate through each index in the database and calculates the optimal fill factor based on the number of pages used and the total number of pages. It then sets the fill factor for each index using the ALTER INDEX statement.
Note that the script sets a minimum fill factor of 90. You can adjust this value to suit your needs. Also note that setting a fill factor too low can lead to excessive page splitting and fragmentation, while setting it too high can lead to wasted space. Therefore, it's important to choose a fill factor that balances these concerns and meets the needs of your specific workload.
Best Practices With Fill Factor
There are several ways people can mess up the fill factor in SQL Server:
Setting the fill factor too high: Setting the fill factor too high can cause SQL Server to waste a lot of space on index pages. This can lead to increased disk I/O and slower performance, especially on read-heavy workloads.
Setting the fill factor too low: Setting the fill factor too low can cause SQL Server to have to split pages frequently as new rows are added, which can lead to increased fragmentation and slower performance, especially on write-heavy workloads.
Not adjusting the fill factor over time: Over time, the characteristics of your workload may change, and the fill factor that was optimal when the index was created may no longer be appropriate. It's important to monitor the performance of your database and adjust the fill factor as needed to ensure optimal performance.
Applying the same fill factor to all indexes: Not all indexes are created equal, and different indexes may require different fill factors to achieve optimal performance. It's important to consider the characteristics of each index and choose an appropriate fill factor based on its workload.
Forgetting to set the fill factor when creating an index: If you don't specify a fill factor when creating an index, SQL Server will use the default fill factor of 0, which means the fill factor will be determined by the server configuration option index fill factor (fillfactor). This can lead to suboptimal performance if the default fill factor is not appropriate for your workload.
To avoid these pitfalls, it's important to carefully consider the characteristics of your workload and adjust the fill factor as needed to achieve optimal performance.
Comments