patternsqlModerate
When should you specify PAD_INDEX?
Viewed 0 times
youpad_indexshouldspecifywhen
Problem
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?Solution
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.
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.
Context
StackExchange Database Administrators Q#17035, answer score: 11
Revisions (0)
No revisions yet.