How to defragment a table which does not have clustered index

Hello,
DBCC SHOWCONTIG shows that some table is fragmented.
This table does not have a clustered index.
AFAIK, DBCC INDEXDEFRAG or DBCC DBREINDEX will re-organize a table when
this table have a clustered index only.
Does anyone know how to defragment a table
which does not have a clustered index?
Thank you for your help,
OlegWhat kind of defragmentation do you expect to achieve for the data pages for
a table which doesn't have a clustered index? Such pages are *not* connected
in a linked list (as they were in the old architecture).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Oleg" <oleg@.nospam.com> wrote in message
news:O0xrFgw9DHA.632@.TK2MSFTNGP12.phx.gbl...
> Hello,
> DBCC SHOWCONTIG shows that some table is fragmented.
> This table does not have a clustered index.
> AFAIK, DBCC INDEXDEFRAG or DBCC DBREINDEX will re-organize a table when
> this table have a clustered index only.
> Does anyone know how to defragment a table
> which does not have a clustered index?
> Thank you for your help,
> Oleg
>|||Hello Tibor,
I am not a big expert in how SQL stores its tables.
DBCC SHOWCONTIG shows me that
table has Logical Fragmentation 75%
and its indices have 0%
I expected that if some value is not applicable
to some object it will have value NULL.
So I expected that if fragmentation is not applicable to heap tables
it will be showed as NULL by DBCC.
It is not so.
So what does that fragmentation value mean for heap tables
and is it a problem?
Thank you for your help,
Oleg
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u2$Tglw9DHA.1268@.TK2MSFTNGP12.phx.gbl...
> What kind of defragmentation do you expect to achieve for the data pages
for
> a table which doesn't have a clustered index? Such pages are *not*
connected
> in a linked list (as they were in the old architecture).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Oleg" <oleg@.nospam.com> wrote in message
> news:O0xrFgw9DHA.632@.TK2MSFTNGP12.phx.gbl...
>|||Hi Oleg!
BOL states that Logical Scan Fragmentation "is not relevant to heap and text
indexes".
You could argue that it should be NULL and I would agree. Consider sending a
request to sqlwish@.microsoft.com.
What can happen with heap pages is that they aren't 100% full or that there
are "air" between the extents that they use (or even that not all pages on
the extents are used). This is generally not considered a big issue, and
there is no way to sort this out except to export/import data.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Oleg" <oleg@.nospam.com> wrote in message
news:OAOmktw9DHA.2392@.TK2MSFTNGP11.phx.gbl...
> Hello Tibor,
> I am not a big expert in how SQL stores its tables.
> DBCC SHOWCONTIG shows me that
> table has Logical Fragmentation 75%
> and its indices have 0%
> I expected that if some value is not applicable
> to some object it will have value NULL.
> So I expected that if fragmentation is not applicable to heap tables
> it will be showed as NULL by DBCC.
> It is not so.
> So what does that fragmentation value mean for heap tables
> and is it a problem?
> Thank you for your help,
> Oleg
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u2$Tglw9DHA.1268@.TK2MSFTNGP12.phx.gbl...
> for
> connected
>
http://groups.google.com/groups?oi=...ublic.sqlserver
when
>|||However, IIRC, DBCC REINDEX will do enough reorganization to eliminate the
forwarding pointers for a heap table which, if it is big enough, would
result in faster table scans. (Seems that I attended a session by Kimberley
Tripp that described this behavior.)
Russell Fields
"Oleg" <oleg@.nospam.com> wrote in message
news:OAOmktw9DHA.2392@.TK2MSFTNGP11.phx.gbl...
> Hello Tibor,
> I am not a big expert in how SQL stores its tables.
> DBCC SHOWCONTIG shows me that
> table has Logical Fragmentation 75%
> and its indices have 0%
> I expected that if some value is not applicable
> to some object it will have value NULL.
> So I expected that if fragmentation is not applicable to heap tables
> it will be showed as NULL by DBCC.
> It is not so.
> So what does that fragmentation value mean for heap tables
> and is it a problem?
> Thank you for your help,
> Oleg
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u2$Tglw9DHA.1268@.TK2MSFTNGP12.phx.gbl...
> for
> connected
>
http://groups.google.com/groups?oi=...ublic.sqlserver
when
>|||Ahh, I always seem to forget those forwarding pointers. So DBREINDEX will
re-write the index row, pointing it to the proper datapage and remove the
forwarding pointer from the old data page, as I understand it.
Thanks Russell, yet another thing I didn't know! :-)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uYTDbfy9DHA.1052@.TK2MSFTNGP12.phx.gbl...
> However, IIRC, DBCC REINDEX will do enough reorganization to eliminate the
> forwarding pointers for a heap table which, if it is big enough, would
> result in faster table scans. (Seems that I attended a session by
Kimberley
> Tripp that described this behavior.)
> Russell Fields
> "Oleg" <oleg@.nospam.com> wrote in message
> news:OAOmktw9DHA.2392@.TK2MSFTNGP11.phx.gbl...
> in
pages
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> when
>