DBCC SHOWCONTIG - what's relevant?

It's my understanding that with DBCC SHOWCONTIG WITH FAST,
TABLERESULTS I'll
get back:
ObjectName
ObjectId
IndexName
IndexId
Pages
ExtentSwitches
ScanDensity
BestCount
ActualCount
LogicalFragmentation
My question is what's relevant for a heap? I've noticed that I'll get
back values for all the columns (the ones mentioned above plus Rows,
MinimumRecordSize, MaximumRecordSize, AverageRecordSize,
ForwardedRecords, Extents, AverageFreeBytes, AveragePageDensity, and
ExtentFragmentation) when it's a heap. Are any of these values "real"
for the heaps?
Also, I know that Logical Fragmentation isn't relevant for heaps- are
there any other metrics that aren't relevant? Are there any metrics
that aren't relevant for clustered tables? How about non-clustered
indexes on heap tables vs. ones on clustered tables?I think all the fields still have meaning, it is just there isn't much you
can do about bad numbers, short of creating/dropping a clustered index.
Books on line reports some of these fields as not relevant, but it is more
accurate to say that the value will always be the "best" possible value due
to the way heaps are scanned... Take logical fragmentation, it represents
the number of out of order pages.. When reading through the linked list of a
clustered index, an out of order page is where the logical order of pages in
the linked list does not matcht the physical order of the pages on disk...
When SQL reads a heap, it reads using the physical order on disk, so there
is no logical fragmentation. Scan density will be a good number for the same
reason. I would guess the best information would be relative to how full the
pages are, and how many forwarding pointers there are...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sleepy" <sleepysqlgirl@.yahoo.com> wrote in message
news:bf62573b.0411291644.47cef884@.posting.google.com...
> It's my understanding that with DBCC SHOWCONTIG WITH FAST,
> TABLERESULTS I'll
> get back:
> ObjectName
> ObjectId
> IndexName
> IndexId
> Pages
> ExtentSwitches
> ScanDensity
> BestCount
> ActualCount
> LogicalFragmentation
> My question is what's relevant for a heap? I've noticed that I'll get
> back values for all the columns (the ones mentioned above plus Rows,
> MinimumRecordSize, MaximumRecordSize, AverageRecordSize,
> ForwardedRecords, Extents, AverageFreeBytes, AveragePageDensity, and
> ExtentFragmentation) when it's a heap. Are any of these values "real"
> for the heaps?
> Also, I know that Logical Fragmentation isn't relevant for heaps- are
> there any other metrics that aren't relevant? Are there any metrics
> that aren't relevant for clustered tables? How about non-clustered
> indexes on heap tables vs. ones on clustered tables?|||I see what you're saying, but there's some wierdness here. With DBCC
SHOWCONTIG WITH FAST,TABLERESULTS I'll get back the columns I
mentioned before for clustered tables, but for heap tables I'll get
back numbers for the other columns as well- the ones that aren't
supposed to be returned. Here's an example from Northwind- I just
included results from two tables but it's the same query. (I've
retyped so it's easier to read):
ObjectName: OrderDetails
ObjectId: 325576198
IndexName: PK_Order_Details
IndexId: 1 <- A clustered table
Level: 0
Pages: 9
Rows: NULL <- not provided with FAST
MinimumRecordSize: NULL <- not provided with FAST
MaximumRecordSize: NULL <- not provided with FAST
AverageRecordSize: NULL <- not provided with FAST
ForwardedRecords: NULL <- not provided with FAST
Extents: 0 <- not provided with FAST
ExtentSwitches: 5
AverageFreeBytes: NULL <- not provided with FAST
AveragePageDensity: NULL <- not provided with FAST
ScanDensity: 33.33333333
BestCount: 2
ActualCount: 6
LogicalFragmentation: 11.11111069
ExtentFragmentation: NULL <- not provided with FAST
ObjectName: Region
ObjectId: 885578193
IndexName:
IndexId: 0 <- A heap table
Level: 0
Pages: 1
Rows: 4 <- not provided with FAST
MinimumRecordSize: 111 <- not provided with FAST
MaximumRecordSize: 111 <- not provided with FAST
AverageRecordSize: 111 <- not provided with FAST
ForwardedRecords: 0 <- not provided with FAST
Extents: 1 <- not provided with FAST
ExtentSwitches: 0
AverageFreeBytes: 7644 <- not provided with FAST
AveragePageDensity: 5.55967378616333 <- not provided with FAST
ScanDensity: 100
BestCount: 1
ActualCount: 1
LogicalFragmentation: 0
ExtentFragmentation: 0 <- not provided with FAST
Northwind isn't the best example, as on other tables I've gotten an
ExtentFragmentation value above 0 on heaps, but let's ignore that for
now. My real point is that on heap tables, it's returning values that
it shouldn't, by definition of WITH FAST. So... are these "real"
values for the heaps? Is this a SQL bug?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<#QMjD4t1EHA.824@.
TK2MSFTNGP11.phx.gbl>...
> I think all the fields still have meaning, it is just there isn't much you
> can do about bad numbers, short of creating/dropping a clustered index.
> Books on line reports some of these fields as not relevant, but it is more
> accurate to say that the value will always be the "best" possible value du
e
> to the way heaps are scanned... Take logical fragmentation, it represents
> the number of out of order pages.. When reading through the linked list of
a
> clustered index, an out of order page is where the logical order of pages
in
> the linked list does not matcht the physical order of the pages on disk...
> When SQL reads a heap, it reads using the physical order on disk, so there
> is no logical fragmentation. Scan density will be a good number for the sa
me
> reason. I would guess the best information would be relative to how full t
he
> pages are, and how many forwarding pointers there are...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>|||As Wayne points out some things like the average Page density and Free Bytes
may be accurate. But the bottom line is that with a heap the rest is
meaningless or even useless for the most part. Without a clustered index
there is nothing you can do about fragmentation unless you BCP out all the
data (in some order), truncate the table and BCP it back in. And even then
you have absolutely no control over how it gets loaded back into the
database file(s). With very few exceptions, every table should have a
clustered index if for no other reason so you can control fragmentation. Of
coarse there are lots of others as well.
Andrew J. Kelly SQL MVP
"Sleepy" <sleepysqlgirl@.yahoo.com> wrote in message
news:bf62573b.0411301340.20190b5f@.posting.google.com...[vbcol=seagreen]
>I see what you're saying, but there's some wierdness here. With DBCC
> SHOWCONTIG WITH FAST,TABLERESULTS I'll get back the columns I
> mentioned before for clustered tables, but for heap tables I'll get
> back numbers for the other columns as well- the ones that aren't
> supposed to be returned. Here's an example from Northwind- I just
> included results from two tables but it's the same query. (I've
> retyped so it's easier to read):
> ObjectName: OrderDetails
> ObjectId: 325576198
> IndexName: PK_Order_Details
> IndexId: 1 <- A clustered table
> Level: 0
> Pages: 9
> Rows: NULL <- not provided with FAST
> MinimumRecordSize: NULL <- not provided with FAST
> MaximumRecordSize: NULL <- not provided with FAST
> AverageRecordSize: NULL <- not provided with FAST
> ForwardedRecords: NULL <- not provided with FAST
> Extents: 0 <- not provided with FAST
> ExtentSwitches: 5
> AverageFreeBytes: NULL <- not provided with FAST
> AveragePageDensity: NULL <- not provided with FAST
> ScanDensity: 33.33333333
> BestCount: 2
> ActualCount: 6
> LogicalFragmentation: 11.11111069
> ExtentFragmentation: NULL <- not provided with FAST
> ObjectName: Region
> ObjectId: 885578193
> IndexName:
> IndexId: 0 <- A heap table
> Level: 0
> Pages: 1
> Rows: 4 <- not provided with FAST
> MinimumRecordSize: 111 <- not provided with FAST
> MaximumRecordSize: 111 <- not provided with FAST
> AverageRecordSize: 111 <- not provided with FAST
> ForwardedRecords: 0 <- not provided with FAST
> Extents: 1 <- not provided with FAST
> ExtentSwitches: 0
> AverageFreeBytes: 7644 <- not provided with FAST
> AveragePageDensity: 5.55967378616333 <- not provided with FAST
> ScanDensity: 100
> BestCount: 1
> ActualCount: 1
> LogicalFragmentation: 0
> ExtentFragmentation: 0 <- not provided with FAST
> Northwind isn't the best example, as on other tables I've gotten an
> ExtentFragmentation value above 0 on heaps, but let's ignore that for
> now. My real point is that on heap tables, it's returning values that
> it shouldn't, by definition of WITH FAST. So... are these "real"
> values for the heaps? Is this a SQL bug?
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:<#QMjD4t1EHA.824@.TK2MSFTNGP11.phx.gbl>...