Abrar Ahmad_ wrote:
Yes, a Deep Dive thread!
Do you can also comment of PAD_INDEX settings in this all POC discussion? Currently in either fill factor settings your script is setting PAD_INDEX=OFF .
And please correct; in overall POC the Fill factor on average laid on 95%+ for concerned indexes?
Since you pinged me on this with a PM, I have to ask, who are you asking? The author of this article or me?
As for my thoughts on the subject of PAD_INDEX, while padding the intermediate levels of a B-Tree Index can help reduce page splits, it pales in comparison to preventing page splits in the leaf level, which would also prevent page splits in the intermediate levels.
I'll let the author of the article explain the apparent "Fill factor on average laid on 95%+ for concerned indexes" queestion because I'll say "It Depends" entirely on the insert/update pattern and I follow much different methodologies. For example, a 95%+ Fill Factor for GUIDs will probably not be the way to go but is absolutely the way to go for ever-increasing keyed indexes with the caveat that it won't prevent bad page splits during expansive updates. Even setting a FILL FACTOR of 50% won't prevent page splits if the expansive updates are made on the most recent data inserted prior to the index being rebuilt.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)