Extents

Q: How could you tell I didn't have any page splits?
And you're correct: it's a composite index with an
identity for the third column. How could you tell that?
Why would an indentity eliminate page splits? Is it
because the data will always get laid out serially if
it's on an identity which isn't the case for us since
it's composite?

>--Original Message--
>OK - this data looks more like it
>This looks like a healthy table - the pages are
>contiguous within the extents, there's practically no
>page splits and your row size allows the pages to be
very
>full. I would be concerned about such full pages if
there
>were page-splits, which would mean that the fill-factor
>should be reduced. However I suspect that your PK is an
>identity one as there aren't any page splits, so such
>fullness seems OK.
>Rgds,
>Paul Ibison
>.
>
Have a look at this page which will help you interpret these results:
http://www.sql-server-performance.co...gmentation.asp
External fragmentation occurs when pages are contiguous within extents.
Ideally, you have one extent switch every 8 pages; the degree to which you
have more than this determines the external fragmentation, but you don't
have any.
I guess it's theoretically possible to set an identity_insert to on and
insert a new identity value into a hole on an existing identity range to
cause a page split, but apart from that case it doesn't happen as the
records are always added at the end ('left' or 'right' depending on the
increment). I'm assuming a clustered index on the identity column BTW.
Interesting point about the composite index though. If your first column in
the index isn't the identity one, then I was just lucky to guess that there
was an indentity column there and the lack of fragmentation is presumably
because the data has been recently reindexed, or the index just added, or
the data has ben miraculously added in a completely sorted order. If it was
the first column out of the three, then I'd expect the corresponding lack of
page-splits as mentioned previously.
Rgds,
Paul Ibison
|||Thx. Great article.
>--Original Message--
>Have a look at this page which will help you interpret
these results:
>http://www.sql-server-
performance.com/rd_index_fragmentation.asp
>External fragmentation occurs when pages are contiguous
within extents.
>Ideally, you have one extent switch every 8 pages; the
degree to which you
>have more than this determines the external
fragmentation, but you don't
>have any.
>I guess it's theoretically possible to set an
identity_insert to on and
>insert a new identity value into a hole on an existing
identity range to
>cause a page split, but apart from that case it doesn't
happen as the
>records are always added at the end ('left' or 'right'
depending on the
>increment). I'm assuming a clustered index on the
identity column BTW.
>Interesting point about the composite index though. If
your first column in
>the index isn't the identity one, then I was just lucky
to guess that there
>was an indentity column there and the lack of
fragmentation is presumably
>because the data has been recently reindexed, or the
index just added, or
>the data has ben miraculously added in a completely
sorted order. If it was
>the first column out of the three, then I'd expect the
corresponding lack of
>page-splits as mentioned previously.
>Rgds,
>Paul Ibison
>
>.
>
|||Sorry - typo - 2nd para should read "are not contiguous"
Rgds,
Paul