Question about indexdefrag

Hi,
Can anyone explain me:
I have a few tables (some with and some without indexes +
some with and some without PK (ex i have a table that
doesnt have a primary key NOR indexes)) yes these tables a
fragmented (dbcc showcontig shows fragmentation). I
created a job to run the following commands on the tabled
in order to defrag the tables:
DBCC DBREINDEX
DBCC CHECKTABLE
DBCC Cleantable
followed by,
DBCC CHECKALLOC
DBCC UPDATEUSAGE
Howevere the tables stay the same (fragmented). I must be
very careful with these tables because they are tables of
a production server that is constantly being used. If
suffers alot of updates/inserts/delete and i cannot seem
to find a way to defrag the tables.
Can anyone help me?You cannot defrag datapages which aren't part of an index using DBREINDEX nor INDEXDEFRAG. You can
create a clustered index, and possibly drop it. Or export and import the data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?|||What kind of "impact" would i have if i create/drop
indexes to defrag the tables... will this cause a big
burden on my server... i know it depends on the size of
the table and amount of data but in a "general point of
view"...... is that really the ONLY possible choice?
>--Original Message--
>You cannot defrag datapages which aren't part of an index
using DBREINDEX nor INDEXDEFRAG. You can
>create a clustered index, and possibly drop it. Or export
and import the data.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Claudia" <anonymous@.discussions.microsoft.com> wrote in
message
>news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
>> Hi,
>> Can anyone explain me:
>> I have a few tables (some with and some without indexes
+
>> some with and some without PK (ex i have a table that
>> doesnt have a primary key NOR indexes)) yes these
tables a
>> fragmented (dbcc showcontig shows fragmentation). I
>> created a job to run the following commands on the
tabled
>> in order to defrag the tables:
>> DBCC DBREINDEX
>> DBCC CHECKTABLE
>> DBCC Cleantable
>> followed by,
>> DBCC CHECKALLOC
>> DBCC UPDATEUSAGE
>> Howevere the tables stay the same (fragmented). I must
be
>> very careful with these tables because they are tables
of
>> a production server that is constantly being used. If
>> suffers alot of updates/inserts/delete and i cannot seem
>> to find a way to defrag the tables.
>> Can anyone help me?
>
>.
>|||BOL documents what locks are acquired when you create a clustered index (an exclusive lock), and
naturally, SQL Server has to do the sort and all the I/O etc. Also, all non-clustered indexes are
re-created when you create or drop a clustered index. So, you really have to test this if it is
feasible in your environment. Also, test that against the export/import strategy.
As I mentioned, INDEXDEFRAG or DBREINDEX will not shuffle or re-claim storage in any way for pages
which aren't part of an index. I do not right now see any other way to do this but the clustered
index or export/import options.
This behavior is for some a part of the reason to have a clustered index on the table in the first
place.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:48eb01c3e4ca$459c1710$a301280a@.phx.gbl...
> What kind of "impact" would i have if i create/drop
> indexes to defrag the tables... will this cause a big
> burden on my server... i know it depends on the size of
> the table and amount of data but in a "general point of
> view"...... is that really the ONLY possible choice?
> >--Original Message--
> >You cannot defrag datapages which aren't part of an index
> using DBREINDEX nor INDEXDEFRAG. You can
> >create a clustered index, and possibly drop it. Or export
> and import the data.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Claudia" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> >> Hi,
> >>
> >> Can anyone explain me:
> >>
> >> I have a few tables (some with and some without indexes
> +
> >> some with and some without PK (ex i have a table that
> >> doesnt have a primary key NOR indexes)) yes these
> tables a
> >> fragmented (dbcc showcontig shows fragmentation). I
> >> created a job to run the following commands on the
> tabled
> >> in order to defrag the tables:
> >>
> >> DBCC DBREINDEX
> >> DBCC CHECKTABLE
> >> DBCC Cleantable
> >>
> >> followed by,
> >>
> >> DBCC CHECKALLOC
> >> DBCC UPDATEUSAGE
> >>
> >> Howevere the tables stay the same (fragmented). I must
> be
> >> very careful with these tables because they are tables
> of
> >> a production server that is constantly being used. If
> >> suffers alot of updates/inserts/delete and i cannot seem
> >> to find a way to defrag the tables.
> >>
> >> Can anyone help me?
> >
> >
> >.
> >|||Furthe to what Tibor has already said, you should read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
I'm assuming you're running SQL Server 2000. Can you explain why you run the
the sequence of commands you give below?
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
> Hi,
> Can anyone explain me:
> I have a few tables (some with and some without indexes +
> some with and some without PK (ex i have a table that
> doesnt have a primary key NOR indexes)) yes these tables a
> fragmented (dbcc showcontig shows fragmentation). I
> created a job to run the following commands on the tabled
> in order to defrag the tables:
> DBCC DBREINDEX
> DBCC CHECKTABLE
> DBCC Cleantable
> followed by,
> DBCC CHECKALLOC
> DBCC UPDATEUSAGE
> Howevere the tables stay the same (fragmented). I must be
> very careful with these tables because they are tables of
> a production server that is constantly being used. If
> suffers alot of updates/inserts/delete and i cannot seem
> to find a way to defrag the tables.
> Can anyone help me?