Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cognos79
Posting Yak Master
241 Posts |
 Posted - 2007-01-04 : 09:40:23
|
| I do have clustered and non-clustered indexes on a table. i am using sql server 2005. by default the fill factor is 0 for these indexes. do i have to change the fill factor value??? |
|
|
MuadDBA
628 Posts |
Posted - 2007-01-04 : 10:09:22
|
| You don't have to, but it's a good idea to evaluate how your tables are going to be used and set your fillfactor based on its usage. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-04 : 10:13:10
|
| for oltp (lots of inserts) 70 is a good starting point |
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-01-04 : 10:16:52
|
| this table can grow up to 10million records...how do i decide if 70 is enough or need more??? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-04 : 11:52:58
|
| "for oltp (lots of inserts) 70 is a good starting point"Except for IDENTITY Primary Key, or any other inserts of ascending-only keys which would waste 30% of the index space!"this table can grow up to 10million records...how do i decide if 70 is enough or need more???"Depends on the insert volume and key selectivity.As above if you are adding ascending keys then 100% fill makes sense.If you are adding relatively few records per day you may still go for 100% - i.e. keep the index as small as possible and suffer a page split for the first addition to each page (each day). Then have an overnight index defrag job to re-optimise.Also, if you insert lots of records in a day, but the keys are locally clustered - for example you insert Sales Order Items indexes by Product Code and "Now" Date/time - then you may still want 100% fill - the first insert for a given product will cause a page split, but the rest of the inserts for that product today will not - whereas a 70% fill is going to still need a page split today (if you insert enough of that product code)If the inserts are totally random then probably a small amount of free space is enough - you are unlikely to insert more than one or two in a page in a given day.Its a balance between keeping the index small (which speed up searching) and having too many page splits (which slows inserts, and slows searching over time).The other balancing act is to reoptimize the index often enough. If you have slack time overnight I see no reason NOT to do this EVERY night - the database is doing nothing, you might as well use that time to make it as fast as possible for tomorrow's users.Kristen |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-04 : 12:12:02
|
"As above if you are adding ascending keys then 100% fill makes sense"not quite. do not use fill factor 100 unless the data is primarily read-only. 100 is going to cause more page splits.there will be a performance hit setting fill factor too low, but 100 is not going to be appropriate for oft-updated data.from BOLquote:
A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table.
|
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-01-04 : 12:23:15
|
| Russell...the data is not updated in this table...always insertions and selections from this table. fill factor of 70 means 70% of the page will only be filled and leaves space for new insertions right??? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-04 : 13:29:59
|
"100 is going to cause more page splits."How is that? The fill-factor only applies to when the index is initially built, and for any reindex. Once you are adding continuously at one end of the index you are going to fill each page 100% full whatever the fill factor is."but 100 is not going to be appropriate for oft-updated data."Sorry, I don't get that either - unless you were meaning where the index key itself is going to be changed (rather than the rest of the record)??"fill factor of 70 means 70% of the page will only be filled and leaves space for new insertions right???"Yes, but that is only appropriate, IMO, if you will be filling up some of the 30% which is spare in EVERY page in the index - i.e. the pattern of the keys for the index is random enough to mostly fill the 30% slack and not cause lots of further page splits by filling the pages a-bit-more-than-30% on average  Kristen |
|
|
|
|
|
|
|
|
index fill factor