Tuesday, February 21, 2012

reporting on a heap (index id = 0)

.
Hi, would someone know what the "heap (Index ID = 0 )" mean and be
able to explain to me? I read it
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EFAA
In section: Using DBCC SHOWCONTIG to Identify Fragmented Indexes.
When I ran DBCC SHOWCONTIG on my table it's shows index id = 1, when would I
get index id = 0? thanks!!
"High values for logical scan fragmentation can lead to degraded
performance of index scans. In the tests, workload performance increased
after defragmenting when clustered indexes had logical fragmentation greater
than 10 percent, and significant increases were attained when logical
fragmentation levels were greater than 20 percent. Consider defragmenting
indexes with 20 percent or more logical fragmentation. Remember that this
value is meaningless when reporting on a heap (Index ID = 0)."IndexID of 0 means that the table is a heap. A heap is a table without a
clustered index. It is recommended that in general all tables have a
clustered index so that you can control the fragmentation.
--
Andrew J. Kelly SQL MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:%23ot5iTQeFHA.3836@.tk2msftngp13.phx.gbl...
> .
> Hi, would someone know what the "heap (Index ID = 0 )" mean and be
> able to explain to me? I read it
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EFAA
> In section: Using DBCC SHOWCONTIG to Identify Fragmented Indexes.
> When I ran DBCC SHOWCONTIG on my table it's shows index id = 1, when would
> I
> get index id = 0? thanks!!
> "High values for logical scan fragmentation can lead to degraded
> performance of index scans. In the tests, workload performance increased
> after defragmenting when clustered indexes had logical fragmentation
> greater
> than 10 percent, and significant increases were attained when logical
> fragmentation levels were greater than 20 percent. Consider defragmenting
> indexes with 20 percent or more logical fragmentation. Remember that this
> value is meaningless when reporting on a heap (Index ID = 0)."
>
>

No comments:

Post a Comment