How do you know how much "Padding" an index should have?
Add a comment
|
2 Answers
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.
-
thanks. So the more fragmentation I see the larger fill factor I should set right? Commented Jan 4, 2011 at 4:48
-
3Not 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– anonCommented Jan 4, 2011 at 12:10
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.
answered Jun 8, 2011 at 23:53
