sql server - When do we specify fill factor for DBCC DBREINDEX? -
i checked on google , found if fill factor 0 uses 100% space on index page. otherwise need specify fill factor.
my questions experts are:
- should leave blank maximum index efficiency?
i found in our databases fill factor 80, 85, 90, 100 mentioned of indexes. so, when should specify fill factor 80 or 90?
select fill_factor,* sys.indexes fill_factor <> 0
the default fillfactor 100 out of box (if hasn't been changed). when fillfactor 0, dbcc dbreindex uses last value specified index.
fillfactor designed improving index performance , data storage when index created, rebuilt, or defragged. setting fillfactor, specify percentage of space on each page filled data, reserving free space on each page future table growth. example, if fillfactor 80, 20% of each page left empty, providing space new records. when space used up, page split occurs.
microsoft recommends use default fillfactor in cases. however, if know how table used, can modify it. fillfactor of 100 tables not read-only cause page split on insert/update, 100 suitable read-only tables. tables have high amount of writing should somewhere between 50 , 70%. other tables should around 80 , 90, if selected rather inserted , updated.
you should read on page splitting. also, establish schedule rebuilding indexes. need consider whether cluster each index or not. example, clustered index low fragmentation percentage can excluded schedule save time.
some references:
