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.

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
add comment

1 Answer

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: http://www.sqlskills.com/BLOGS/KIMBERLY/post/MSDN-Webcast-QA-Index-Defrag-Best-Practices-Fragmentation-Questions-Part-I.aspx

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
1  
You rebuild it less often, but with much more free space available. –  Brent Ozar Apr 26 '12 at 11:14
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.