June 10, 2005 at 9:37 pm
Hi All,
As I read somewhere, we can specify the Pad_Index and Fill Factor to help improving the performance of a table when creating indexed, but I'm not sure how it works. Please someone explain for me about this option? If I don't specify the Fill Factor option, will it badly affect the performance of the table when I query data out. Thanks a lot for any ideas.
Minh Vu
June 12, 2005 at 2:38 pm
The fill factor option (and pad index) can help performance since it leaves free space on each page of an index. The rows of an index are stored in index order on pages. Each page can only fit a certain amount of index rows (~8060 bytes/size of each row). When a new row needs to be inserted on a page that is already full, that page needs to be split into two pages with half of the rows on each. That split is a pretty expensive operation. By leaving empty space in an index you are making it less likely that page splits will occur (at least for a while, until the pages start to fill up) so data modifications will be faster.
For data reads fill factor does not help that much, in fact it can be bad for reads. Since more pages might need to be read the operation might take longer. But it might actaully be helpful as well, due to reasons I think is to advanced to go into here.
As always you should test if your application is helped by a specific fill factor.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply