SQL Server has default value for fill factor is Zero (0). The fill factor is implemented only when the index is created; it is not maintained after the index is created as data is added, deleted, or updated in the table. When creating an index, you can specify a fill factor to leave extra gaps and reserve a percentage of free space on each leaf level page of the index to accommodate future expansion in the storage of the table’s data and reduce the potential for page splits.
I like my fill factor to 90 (Why? I like it!) I use sp_configure to change the default fill factor for the SQL Server.
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', 90
GO
RECONFIGURE
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL
Hi, am a bit new to this but please explain to me why you have the fill factor set to 90…thanx
Hi,
Is there a way to determine the current fillfactor of an index? Say the index was created with fillfactor 0, how do I determine is was changed to another value?
thanks
Hi,
I would want to reconfigure all the fill factors for each of the index. [between 75 - 90] Is it good that we have 90 fillfactor for all indexes in the table.. irrespective of the data types of the columns used in indexes
Please guide me in coming up with a fill factor strategy
Hey Pinal.. Will the above query u mentioned changes the Fill Factor of all the Indexes existing on the Server ????
[...] on Performance Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor Change Default Fill Factor For Index Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL [...]