Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 '13 at 17:14
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
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 '13 at 11:56
1  
Since we are talking about an unlikely hypthetical update: by all means, stay at 100%. –  Twinkles Sep 30 '13 at 12:23
add comment

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.

share|improve this answer
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 '13 at 11:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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