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.

How do you know how much "Padding" an index should have?

share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

If you're referring to the fill factor of the index, the only legitimate answer that you can get is "it depends." The fill factor of an index can be varied based on how much you'll be inserting into the existing leaf level pages of the index.

It's going to be best to create your indexes using the default settings and monitor the level of fragmentation over a period of time. You should also be routinely performing index maintenance using an index maintenance script. These scripts will automate the process of keeping your indexes in good operating condition.

share|improve this answer
    
thanks. So the more fragmentation I see the larger fill factor I should set right? –  Beth Whitezel Jan 4 '11 at 4:48
2  
Not really. Fill factor is really only going to help you if you are doing a lot of inserts evenly throughout an index. Even then, you'd be better off with an index maintenance plan instead of increasing the fill factor. That empty space in the index still takes up space and must be read by the database. More empty space = more space = slower reads. Everything you never wanted to know about indexes and fill factor is here: sqlskills.com/blogs/paul/category/Indexes-From-Every-Angle.aspx –  Jeremiah Peschka Jan 4 '11 at 12:10
    
great, thanks again. –  Beth Whitezel Jan 4 '11 at 17:29
add comment

I believe that "fill factor" is overrated.

I believe that you should leave room for one more item. That way, every block is ready to receive one more row without having the overhead of a block split. As you proceed to do stuff, block splits will happen. If things are random, and one block turns into 2 (not something more sophisticated), then eventually the average block will become 69% full.

share|improve this answer
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.