fill factor

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  February 28, 2007 at 8:00 am

  #115997

  guys,

  When I create an Index I will have to specify fill factor. HOw does the fill factor effect the performance of the index

  If I have an index on unique column values what shoulf be the value of the fil factor

  and how does the value change when an index is created on 3 or more columns.

  In sql server 2005 when I created a primary key on an clustered/non clustered index the fill factor by default is 0. Should it changed??

  any suggestions/inputs would help

  Thanks

• vambati

  SSCrazy

  Points: 2769

  More actions

  February 28, 2007 at 8:33 am

  #692318

  Its is always advisable to leave the fill factor at default value unless u want to use it. Fill factor affects the fragmentation of index pages if its wrongly calculated. By saying FF of 80 it means u are leaving 20% of space on each index/data page for inserts/updates/deletes once that space is filled the page splits to accomidate new data. Unless u very active tables that get lots of inserts/updates/deletes do not mess with FF. Hope this clarifies u'r query.

• JeremyE

  SSCoach

  Points: 16259

  More actions

  March 1, 2007 at 2:18 pm

  #692716

  A fill factor of 0 is the default which will fill the pages 100% This is great for static tables as it conserves disk space and reduces I/O. However, on very dynamic tables (many Inserts and Deletes) this will cause page splitting and a fill factor other than 100% should be choosen.