can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
CoryIndex ID of 0 means that the table is a heap. In other words the table does
not have a clustered index. The only way to remove fragmentation is to
create a clustered index on that table or to export all the data, truncate
it and import it all back in again. Index ID of 1 is always the clustered
index. You will never have both only one or the other.
Andrew J. Kelly SQL MVP
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
> of my indexes with ID's 1 and on up appear to be fine, it's just these ID
> 0's that look bad.
>
> thanks,
> Cory
>
>|||Hey Andy, I'm wondering how exporting & re-importing the data helps? Doesn't
simply creating a clustered index & then dropping the clustered index
provide an effective defrag of the heap?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table
> does not have a clustered index. The only way to remove fragmentation is
> to create a clustered index on that table or to export all the data,
> truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>|||In addition, it is worth explaining what you mean by fragmentation.
When the physical order doesn't match the logical order is one type of fragm
entation this is
impossible for the datapages of a heap table (index 0) as there is no order.
This is also known as
external fragmentation.
Another is then you have lots of free spaces on the pages and extents that t
he heap/index is using.
This is also known as internal fragmentation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table doe
s not have a clustered
> index. The only way to remove fragmentation is to create a clustered index
on that table or to
> export all the data, truncate it and import it all back in again. Index I
D of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>

can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0.
Judging by what value? See my other post. There's no order for rows in a hea
p, so scan density etc.
are meaningless for a heap.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:%23NBNJIr9GHA.3344@.TK2MSFTNGP03.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>|||Hi Cory,
These tables to which you refer are heap tables, those that do not have a
clustered index defined on them. The data pages for heaps are not stored in
any particular order, which would explain why the fragmentation values are
high. Consider creating an appropriate clustered index on these tables if
you want to reduce the observed fragmentation.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Cory Harrison" wrote:

> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>

can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory
Index ID of 0 means that the table is a heap. In other words the table does
not have a clustered index. The only way to remove fragmentation is to
create a clustered index on that table or to export all the data, truncate
it and import it all back in again. Index ID of 1 is always the clustered
index. You will never have both only one or the other.
Andrew J. Kelly SQL MVP
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
> of my indexes with ID's 1 and on up appear to be fine, it's just these ID
> 0's that look bad.
>
> thanks,
> Cory
>
>
|||Hey Andy, I'm wondering how exporting & re-importing the data helps? Doesn't
simply creating a clustered index & then dropping the clustered index
provide an effective defrag of the heap?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table
> does not have a clustered index. The only way to remove fragmentation is
> to create a clustered index on that table or to export all the data,
> truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>
|||In addition, it is worth explaining what you mean by fragmentation.
When the physical order doesn't match the logical order is one type of fragmentation this is
impossible for the datapages of a heap table (index 0) as there is no order. This is also known as
external fragmentation.
Another is then you have lots of free spaces on the pages and extents that the heap/index is using.
This is also known as internal fragmentation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table does not have a clustered
> index. The only way to remove fragmentation is to create a clustered index on that table or to
> export all the data, truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>

can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0.
Judging by what value? See my other post. There's no order for rows in a heap, so scan density etc.
are meaningless for a heap.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:%23NBNJIr9GHA.3344@.TK2MSFTNGP03.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>
|||Hi Cory,
These tables to which you refer are heap tables, those that do not have a
clustered index defined on them. The data pages for heaps are not stored in
any particular order, which would explain why the fragmentation values are
high. Consider creating an appropriate clustered index on these tables if
you want to reduce the observed fragmentation.
Kind Regards
Andrew Pike
SQL Server DBA
UBS IB
"Cory Harrison" wrote:

> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>

can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
CoryIndex ID of 0 means that the table is a heap. In other words the table does
not have a clustered index. The only way to remove fragmentation is to
create a clustered index on that table or to export all the data, truncate
it and import it all back in again. Index ID of 1 is always the clustered
index. You will never have both only one or the other.
--
Andrew J. Kelly SQL MVP
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All
> of my indexes with ID's 1 and on up appear to be fine, it's just these ID
> 0's that look bad.
>
> thanks,
> Cory
>
>|||Hey Andy, I'm wondering how exporting & re-importing the data helps? Doesn't
simply creating a clustered index & then dropping the clustered index
provide an effective defrag of the heap?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table
> does not have a clustered index. The only way to remove fragmentation is
> to create a clustered index on that table or to export all the data,
> truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
>> fragmented for index ID 0. I'm assuming this is the table itself? If
>> so, what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG.
>> All of my indexes with ID's 1 and on up appear to be fine, it's just
>> these ID 0's that look bad.
>>
>> thanks,
>> Cory
>>
>|||In addition, it is worth explaining what you mean by fragmentation.
When the physical order doesn't match the logical order is one type of fragmentation this is
impossible for the datapages of a heap table (index 0) as there is no order. This is also known as
external fragmentation.
Another is then you have lots of free spaces on the pages and extents that the heap/index is using.
This is also known as internal fragmentation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Index ID of 0 means that the table is a heap. In other words the table does not have a clustered
> index. The only way to remove fragmentation is to create a clustered index on that table or to
> export all the data, truncate it and import it all back in again. Index ID of 1 is always the
> clustered index. You will never have both only one or the other.
> --
> Andrew J. Kelly SQL MVP
> "Cory Harrison" <charrison@.csiweb.com> wrote in message
> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly fragmented for index ID
>> 0. I'm assuming this is the table itself? If so, what do I do about it? I can't enter this ID
>> into DBCC INDEXDEFRAG. All of my indexes with ID's 1 and on up appear to be fine, it's just
>> these ID 0's that look bad.
>>
>> thanks,
>> Cory
>>
>|||The idea would be that you would export with a select that had an order by.
Then once you truncate the table and import it back in you will not have any
forwarding pointers, gaps in the pages etc. The data may not be in physical
order like you would have at the leaf node of a clustered or non-clustered
index but it should be decent when thru. How long it stays that way is a
different story:). That process does not guarantee lack of fragmentation
from a data ordering standpoint but it will clean up the gaps, forwarding
pointers and if there is enough contiguous room in the data files it will be
mostly contiguous as well.
--
Andrew J. Kelly SQL MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:uRIghjN9GHA.4632@.TK2MSFTNGP02.phx.gbl...
> Hey Andy, I'm wondering how exporting & re-importing the data helps?
> Doesn't simply creating a clustered index & then dropping the clustered
> index provide an effective defrag of the heap?
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23E5p6yJ9GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Index ID of 0 means that the table is a heap. In other words the table
>> does not have a clustered index. The only way to remove fragmentation is
>> to create a clustered index on that table or to export all the data,
>> truncate it and import it all back in again. Index ID of 1 is always the
>> clustered index. You will never have both only one or the other.
>> --
>> Andrew J. Kelly SQL MVP
>> "Cory Harrison" <charrison@.csiweb.com> wrote in message
>> news:OtXCgFJ9GHA.3384@.TK2MSFTNGP05.phx.gbl...
>> When I run DBCC SHOWCONTIG I see several tables who appear to be
>> horribly fragmented for index ID 0. I'm assuming this is the table
>> itself? If so, what do I do about it? I can't enter this ID into DBCC
>> INDEXDEFRAG. All of my indexes with ID's 1 and on up appear to be fine,
>> it's just these ID 0's that look bad.
>>
>> thanks,
>> Cory
>>
>>
>

can tables themselves be fragmented? Index ID 0?

When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
fragmented for index ID 0. I'm assuming this is the table itself? If so,
what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
that look bad.
thanks,
Cory> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0.
Judging by what value? See my other post. There's no order for rows in a heap, so scan density etc.
are meaningless for a heap.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Cory Harrison" <charrison@.csiweb.com> wrote in message
news:%23NBNJIr9GHA.3344@.TK2MSFTNGP03.phx.gbl...
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>|||Hi Cory,
These tables to which you refer are heap tables, those that do not have a
clustered index defined on them. The data pages for heaps are not stored in
any particular order, which would explain why the fragmentation values are
high. Consider creating an appropriate clustered index on these tables if
you want to reduce the observed fragmentation.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Cory Harrison" wrote:
> When I run DBCC SHOWCONTIG I see several tables who appear to be horribly
> fragmented for index ID 0. I'm assuming this is the table itself? If so,
> what do I do about it? I can't enter this ID into DBCC INDEXDEFRAG. All of
> my indexes with ID's 1 and on up appear to be fine, it's just these ID 0's
> that look bad.
>
> thanks,
> Cory
>
>
>