3

From what I've read, typically for indexing the recommendation is to leave the fill factor at 70-80, and the logic behind this seems that new data may be inserted into a table between existing values thus fill factor shouldn't be set as 100. In a hypothetical example of usernames, new names may be added to the name field, so a fill factor of 100 could create a problem because, even though the pages are full, the new data won't be organized appropriately with the other data.

My question involves a table where data are organized by date (clustered index) and ID (non clustered index); each order has a date and an ID thus no "new" orders can occur between existing orders and no new IDs can appear between existing IDs - these happen sequentially. For a table like this, would it be appropriate from a performance perspective to set the fill factor to 100 and reindex at 100, meaning are there performance advantages to having the pages full since no new data will appear between existing data?

1
  • 2
    For a table like this, would it be appropriate from a performance perspective to set the fill factor to 100 and reindex at 100 -- for those two indexes, yes.
    – Jon Seigel
    Sep 30, 2013 at 17:14

2 Answers 2

4

Have you considered updates? If you change a row and the new data does not fit in the same space the row would have to be moved to a new page. If you can rule this out, filling the pages to 100 percent will reduce the space the table uses and consequentially give you a slight performance improvement.

2
  • Thanks @Twinkles; I can rule out updates, but if I suppose that I may have an update in the future (highly unlikely), would filling the pages to 95% be beneficial?
    – user123
    Sep 30, 2013 at 11:56
  • 1
    Since we are talking about an unlikely hypthetical update: by all means, stay at 100%.
    – Twinkles
    Sep 30, 2013 at 12:23
4

The default fill factor is zero, which is identical to 100. As per the product documentation,

The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.

Trying to find an opitmal value for fill factor is a well-researched problem. In the described scenario, there is not much to be done unless the table doesn't use default values.

1
  • 1
    Also, I think it is important to quote this from the documentation: "Therefore, specifying a fill factor other than the default can decrease database read performance by an amount inversely proportional to the fill-factor setting. For example, a fill-factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, therefore increasing the disk IO operations required to retrieve the data."
    – Twinkles
    Sep 30, 2013 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.