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
>

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=djq&as_ugroup=microsoft.public.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=djq&as_ugroup=microsoft.public.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
> >
> >
>|||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=djq&as_ugroup=microsoft.public.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...
> > 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=djq&as_ugroup=microsoft.public.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
> > >
> > >
> >
> >
>|||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...
> > 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=djq&as_ugroup=microsoft.public.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
> > >
> > >
> >
> >
>|||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=djq&as_ugroup=microsoft.public.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...
> > 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=djq&as_ugroup=microsoft.public.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
> > > >
> > > >
> > >
> > >
> >
> >
>

How to defrag a table?

To all gurus,
Thanx for the info concerning the dbcc statements. The only remaining
question is there a command or code that is known to be used to defrag a
table. It would be very good if this would be usable on the fly (ad-hoc)
Thanx
BillO
*** Sent via Developersdex http://www.examnotes.net ***"Bill Orova" <nospam@.devdex.com> wrote in message
news:e3W#oPuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> To all gurus,
> Thanx for the info concerning the dbcc statements. The only remaining
> question is there a command or code that is known to be used to defrag a
> table. It would be very good if this would be usable on the fly (ad-hoc)
> Thanx
> BillO
>
> *** Sent via Developersdex http://www.examnotes.net ***
What do you mean by defrag a table?
A heap (table without a clustered index) stores data in no particular order.
As rows are deleted and then inserted SQL Server places those rows where
they fit.
If you have a clustered index on a table and the clustered index is
fragmented, then you could rebuild the clustered index and essentially
defrag your table.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi,
See DBCC INDEXDEFRAG and DBCC DBREINDEX commands in sql server books online.
You could also see the command DBCC SHOWCONTIG in books online which details
the fragmentation for table.
Thanks
Hari
SQL Server MVP
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uDT6fUuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> "Bill Orova" <nospam@.devdex.com> wrote in message
> news:e3W#oPuZFHA.3876@.TK2MSFTNGP12.phx.gbl...
> What do you mean by defrag a table?
> A heap (table without a clustered index) stores data in no particular
> order.
> As rows are deleted and then inserted SQL Server places those rows where
> they fit.
> If you have a clustered index on a table and the clustered index is
> fragmented, then you could rebuild the clustered index and essentially
> defrag your table.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>|||
Rick,
So essentially if there is no clustered index then it would not be
possible to defrag a table(heap) or if possible it would have no
benevolent consequences in term of hard disk space?
BillO
*** Sent via Developersdex http://www.examnotes.net ***|||It depends on what you mean by fragmentation. For a heap, the is no order. B
ut as pages and extents
are allocated and rows and pages are deallocated, you can leave "holes" in y
our pages as well as
extents. I.e., pages not fully utilized and extents where not all 8 pages ar
e used. If you consider
this a type of fragmentation, then there is no way to defrag except re-loadi
ng the table or possibly
(shudder) shrinking the file/database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill Orova" <nospam@.devdex.com> wrote in message news:O0EQbd6ZFHA.1088@.TK2MSFTNGP14.phx.gb
l...
>
> Rick,
> So essentially if there is no clustered index then it would not be
> possible to defrag a table(heap) or if possible it would have no
> benevolent consequences in term of hard disk space?
>
> BillO
> *** Sent via Developersdex http://www.examnotes.net ***