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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

So, you can use FILLFACTOR to leave space in leaf index pages. Specifying PAD_INDEX also leaves space in intermediate nodes. What scenarios should you specify PAD_INDEX, and what benefit does it provide to the index?

share|improve this question

From Kimberly Tripp's post:

Typically, PAD_INDEX is not needed unless you have extremely unbalanced activity and are unable to rebalance the tree (meaning you need to achieve high availability).

Read more:

What she means by high availability is that you don't have the ability to run index maintenance jobs that would lock the table. Personally, I wouldn't recommend ever setting this by default - leave it to DBAs who find that they're unable to manage index maintenance appropriately with any other methods, like using Ola Hallengren's maintenance scripts during off hours. His scripts are genius - they'll do as much as possible online.

share|improve this answer
How would PAD_INDEX help if you are unable to regularly rebuild the index to enforce the FILLFACTOR on the non-leaf nodes of the index in the first place? – Nick Chammas Apr 25 '12 at 21:55
You rebuild it less often, but with much more free space available. – Brent Ozar Apr 26 '12 at 11:14

Your Answer


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.