November 12, 2010 at 5:44 pm
Just out of curiosity...
Lets say the fill factor is set to 80 and I have a non-clustered index without covering columns. When a new record is inserted (with the index key size = 900 bytes) and a new leaf node is created, the fill factor is pointless, right?
Since non-clustered indexes without covering columns will always only store the index key (max 900 bytes) and a pointer, fill factor is only useful for clustered indexes or covering indexes. Do I understand this right?
Does anyone know what happens if you set the fill factor so small that an index key can't fit in the leaf node? (is this even allowed) Just came to my mind and thought I'll ask...Will try myself in a bit 🙂
November 12, 2010 at 9:10 pm
caladba (11/12/2010)
Just out of curiosity...Lets say the fill factor is set to 80 and I have a non-clustered index without covering columns. When a new record is inserted (with the index key size = 900 bytes) and a new leaf node is created, the fill factor is pointless, right?
Since non-clustered indexes without covering columns will always only store the index key (max 900 bytes) and a pointer, fill factor is only useful for clustered indexes or covering indexes. Do I understand this right?
Does anyone know what happens if you set the fill factor so small that an index key can't fit in the leaf node? (is this even allowed) Just came to my mind and thought I'll ask...Will try myself in a bit 🙂
When fill factor is small (say30%) you are leaving more room for insertions. When fill factor is higher, say 90%, you are having about 10% of the size in each page as free for future insertions. When a record cannot be inserted in a particular page (clusterd index), page split happens which is not good as it leads to fragmentation.
this depends on type of database. For lower insertions happening in the database, you want to keep the fill factor on a higher side so that pages are mostly full, hence read operations will be faster as lesser number of pages need to be read in memory. However if there are many update/inserts happening, you may want to keep it little lower to accomodate for future updates/inserts to prevent page splits.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply