Fill Factor of a Table

Query:

Table Name : data

SELECT
   name AS index_name,
   (avg_page_space_used_in_percent/100) AS fill_factor
FROM
   sys.dm_db_index_physical_stats (DB_ID(), 
   OBJECT_ID('data'), NULL, NULL, 'DETAILED')
   JOIN sys.indexes ON indexes.object_id = 
   OBJECT_ID('data') AND indexes.index_id = index_id
WHERE
   index_level = 0;
 

Output:

index_name  fill_factor
---------- -----------
PK_orders   0.75

Conclusion

Deciding the correct and the required fill factor will cause reduce in page splitting and reduce overall cost and will also lower the use of memory and CPU and ultimately increasing the performance. 

In SQL when a index is created or rebuilt, the fill factor value determines the percentage of space on each leaf-level page to be filled with data. By setting the fill factor you can control the amount of space initially allocated to table ‘s data page. Fill factor option is provided for fine tuning index and data storage and performance.