17

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?

10

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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