Extents

In Informix you have to really watch your extents. If
you end up with too many extents on a table, you've
essentially "fragmented" that table too much and it will
significantly slow down performance because Informix has
to go out to so many locations on the drive.
How can I tell the equivalent in Sql Server? I just ran
a dbcc showcontig on a table that is giving me troubles
and came up with the following:
DBCC SHOWCONTIG scanning 'B0001' table...
Table: 'B0001' (610101214); index ID: 1, database ID: 99
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00%
[1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 7150.0
- Avg. Page Density (full)................: 11.66%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
Does this mean it has only two extents? (This table has
37 million records - I'm a little surprised it's in just
two extents.)
Also: this DBCC Showcontig ran in like two seconds. Is
that really that fast or did I do something wrong?Here are the results I should have posted. I ran it on
our development server accidently for my last post.
Also, in Informix extent is a "continuous" block of disk
space. That's what I meant by extent but it looks like
Sql Server just means 8 pages. I am trying to figure out
if my Sql Server extents/pages are all "glued" together
in a relatively contiguous block or blocks. Hope that
makes sense.
DBCC SHOWCONTIG scanning 'B00599601' table...
Table: 'B00599601' (2058451103); index ID: 1, database
ID: 92
TABLE level scan performed.
- Pages Scanned........................: 1145334
- Extents Scanned.......................: 143865
- Extent Switches.......................: 143866
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.51%
[143167:143867]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 6.02%
- Avg. Bytes Free per Page................: 204.0
- Avg. Page Density (full)................: 97.48%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.|||It seems that you have 2 pages which are on average ~12%
full, giving 1.92k. That's pretty impressive for 37
million records! Even with a null in a single field, the
row overhead means this is not possible. Can you do a
select count(*) from B0001 just to confirm.
Rgds,
Paul Ibison
>--Original Message--
>In Informix you have to really watch your extents. If
>you end up with too many extents on a table, you've
>essentially "fragmented" that table too much and it will
>significantly slow down performance because Informix has
>to go out to so many locations on the drive.
>How can I tell the equivalent in Sql Server? I just ran
>a dbcc showcontig on a table that is giving me troubles
>and came up with the following:
>DBCC SHOWCONTIG scanning 'B0001' table...
>Table: 'B0001' (610101214); index ID: 1, database ID: 99
>TABLE level scan performed.
>- Pages Scanned........................: 2
>- Extents Scanned.......................: 2
>- Extent Switches.......................: 1
>- Avg. Pages per Extent..................: 1.0
>- Scan Density [Best Count:Actual Count]......: 50.00%
>[1:2]
>- Logical Scan Fragmentation ..............: 50.00%
>- Extent Scan Fragmentation ...............: 50.00%
>- Avg. Bytes Free per Page................: 7150.0
>- Avg. Page Density (full)................: 11.66%
>DBCC execution completed. If DBCC printed error
messages,
>contact your system administrator.
>Does this mean it has only two extents? (This table has
>37 million records - I'm a little surprised it's in just
>two extents.)
>Also: this DBCC Showcontig ran in like two seconds. Is
>that really that fast or did I do something wrong?
>.
>|||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|||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.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|||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