Explanation of Find Fill Factor of All Indexes in a Database in SQL Server

We gives an in-depth instruction on way of finding the fill factor of every index in a SQL Server database. Fill factor is a vital configuration which can influence index performance and storage use. Fill factors and how to understand them play a major role in maximizing database performance. This tutorial is going to show you the required SQL queries and some example to make it clearer.

Understanding Fill Factor

Fill factor is expressed in percentages and is enabled to control the amount of space on the index pages occupied by information when creating or rebuilding of the index. The smaller is the fill factor, the greater is the number of free spaces on every page, which in the future data updates can minimize the number of page splits. The bigger fill factor has greater use of storage but has the maximum risk of page break.

Why Check Fill Factor?

Performance Tuning: Understanding fill factor will lead to the presence of indexes that might be causing bottlenecks in the performance because of the high rate of page splitting or the ineffective utilization of pages stored in the index.
Storage Management: Knowledge on fill factor helps you manage storage space by modifying it in line with the changing data modification pattern of the table.
Index Maintenance: Index maintenance includes frequent monitoring and tweaking of the fill factor and hence provides the best results in the long term.

SQL Query to Find Fill Factor of All Indexes
The next SQL statement helps to download the fill factor of each index of a given database. System catalog views are being used as source of the information to address this query.

SELECT
DB_NAME() AS DatabaseName,
OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
i.fill_factor AS [FillFactor]
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.object_id = i.object_id
WHERE
i.type > 0-- Exclude heaps
ORDER BY
SchemaName,
TableName,
IndexName

Explanation of the Query

DB_NAME(): returns the name of current database.
OBJECT_SCHEMA_NAME(t.object_id): It gives the name of the schema of the table.
sys.tables t: Information about tables is located in this system catalog view.
sys.indexes i: Line box controller comprising information about indexes.
t.object_id = i.object_id: Unites the views and tables through the object ID.
i.type>0: Removes heaps (tables that do not have clustered index).
i.fill_factor: Queries the value of fill factor of the index. Value of 0 would refer to the default of server-level fill factor.
ORDER BY: This colors the output in order that you understand it easily.

Interpreting the Results

FillFactor = 0: It means that index is default in fill factor settings on the server. The default fill factor may also be tuned at the server level.
FillFactor = 1-100: It shows the given percentage of fill to be applied when the index is being created or rebuilt.

Modifying the Fill Factor

When you find that the fill factor requires a change, you should re-create the index working on a new fill factor by means of the ALTER INDEX statement.

ALTER INDEX IndexName ON TableName  
REBUILD WITH (FILLFACTOR = NewFillFactor)  

Change IndexName, TableName and NewFillFactor with its respective values.

For example:

ALTER INDEX IX_Customers_LastName ON Customers  
REBUILD WITH (FILLFACTOR = 80)  

This declaration re-constructs the IX_Customers_LastName index on the Customers table having a fill factor of 80 percent.

Note: Building indexes back can be a costly task in terms of resources, particularly on big tables. They suggest that one should conduct index maintenance during non-peak hours.

Considerations for Choosing a Fill Factor

Read-heavy loads: Read-heavy workloads (e.g. with few data changes) have a workload requiring a higher fill factor (e.g. 90-100%). This increases the use of storage and minimizes the number of pages that have to be read.
Write-Heavy workloads: A lower fill factor (e.g. 70-80%) is advantageous to workloads with write heavy characteristics where data change often happens. This minimizes the page splits and write performance is enhanced.
Mixed Workloads: In case of mixed workload, a fill factor of 80-90% can form a good compromise.
Server Default: When you do not specify a fill factor when creating or rebuilding an index, the fill factor default set by the server is taken. The default value of the server can be done using sp_configure.

Example Scenario

You can run the query and discover that some of the indexes on the table that is frequently updated are set to a fill factor of 100 percent. This may be resulting in too many pages being split and affecting write performance. You opt to recreate these indexes with fill factor set to 80%.
1. Determine the indexes where the fill factor of the frequently update table is 100 percent.

2. Account the ALTER INDEX statement on each of the indices:

 ALTER INDEX IndexName1 ON TableName  
 REBUILD WITH (FILLFACTOR = 80);  
 ALTER INDEX IndexName2 ON TableName  
 REBUILD WITH (FILLFACTOR = 80);  
-- Repeat for all identified indexes  

3. Keep on monitoring performance subsequent to the rebuild of the index to check if the writes are not only performing better but the intervals of page splits are fewer.

Conclusion:

Index fill factor is one of the most important components of SQL Server database performance optimization. All that you need to do is to run the given SQL query in order to determine the fill factor of all the indexes in a database. Depending on how your tables are modified, you can then change the fill factor to maximize the use of storage space, and use it towards better performance of the database as a whole. Consistently checking and keeping indexes as well as their fill levels will guarantee that your database will be responsive and efficient as time goes on. Do not forget about trade-offs of storage efficiency and write performance during selection of fill factor.