SQL SERVER – Change Default Fill Factor For Index

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

About these ads

7 thoughts on “SQL SERVER – Change Default Fill Factor For Index

  1. 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

  2. Hey Pinal.. Will the above query u mentioned changes the Fill Factor of all the Indexes existing on the Server ????

  3. Pingback: SQL SERVER – Correct Value for Fillfactor – Quiz – Puzzle – 24 of 31 « SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

  5. If I change my default index fill factor to 90, do I need to set all my indexes to 0/100? or will they grab the default server value?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s