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. Let us learn about how to change default fill factor of the index.

SQL SERVER - Change Default Fill Factor For Index fillfactorinsql

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

In simple words, it keeps the empty space on the table to accommodate new rows which are inserted into the table. If there are not empty space in the table, the page split will occur and it may not be optimal for your database performance. There are two different ways to change this value. Let us assume that we want our fill factor as 90.

Let me know what you think of this blog post. Here is the relevant video on this subject.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 Understanding Trigger Recursion and Nesting with examples
Next Post
SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

Related Posts

8 Comments. Leave new

  • Hi, am a bit new to this but please explain to me why you have the fill factor set to 90…thanx

    Reply
  • Wilfred van Dijk
    July 18, 2007 4:03 am

    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

    Reply
  • Chakravarthy
    March 5, 2009 9:25 am

    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

    Reply
  • Santosh Gadila
    June 30, 2010 11:33 pm

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

    Reply
  • 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?

    Reply

Leave a Reply

Menu