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

Fill Factor is a setting that determines how much space to leave in each index page when an index is created or rebuilt. It affects performance, especially in systems with frequent INSERT or UPDATE operations.

A Fill Factor of:

• 100 means pages are fully filled (best for read-heavy tables).

• Less than 100 leaves space to reduce page splits (good for write-heavy tables).

You can use a script to list the Fill Factor of all indexes in a database.

1. SELECT   
2.     OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,  
3.     OBJECT_NAME(i.object_id) AS TableName,  
4.     i.name AS IndexName,  
5.     i.index_id,  
6.     i.fill_factor  
7. FROM   
8.     sys.indexes i  
9. WHERE   
10.     i.type IN (1, 2) -- clustered and non-clustered  
11.     AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1  
12. ORDER BY   
13.     SchemaName, TableName, IndexName;  

Conclusion:

Knowing the Fill Factor of indexes helps in fine-tuning database performance. This script gives a clear view of how your indexes are configured so you can decide if adjustments are needed—especially for write-heavy or fragmented tables.