• David,

    No, though that's the impression I got at first. The key to it is that the FILLFACTOR determines how much of the index is filled ***when it's first built***. After that, the index gets filled until the point where no further entries can be made, at which point a page split occurs, and as you rightly say, half of the records get copied into the new page, and the record pointers adjusted accordingly.  Only when the index is rebuilt does the FILLFACTOR setting get re-applied to the index, and it then becomes 20% full again.  Otherwise, regardless of the its setting, you'd always end up getting page splits the moment a new record was inserted if the FILLFACTOR was constantly maintained, 'on-the-fly'.

    With regard to the page splits, it's expensive because a new page has to be allocated, which may also mean a new extent has to be allocated if the current one is full. In addition, the records have to be moved (ie: both copied AND marked as deleted), and the pointers to the records have to be re-adjusted - all lots of performance sapping disk I/O.


    Jon