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 no
r INDEXDEFRAG. You can
create a clustered index, and possibly drop it. Or export and import the dat
a.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
quote:|||What kind of "impact" would i have if i create/drop
> 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?
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?
quote:
>--Original Message--
>You cannot defrag datapages which aren't part of an index
using DBREINDEX nor INDEXDEFRAG. You can
quote:
>create a clustered index, and possibly drop it. Or export
and import the data.
quote:
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
quote:
>
>"Claudia" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||BOL documents what locks are acquired when you create a clustered index (an
>news:530901c3e4c6$724a10e0$a601280a@.phx.gbl...
+[QUOTE]
tables a[QUOTE]
tabled[QUOTE]
be[QUOTE]
of[QUOTE]
>
>.
>
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 stra
tegy.
As I mentioned, INDEXDEFRAG or DBREINDEX will not shuffle or re-claim storag
e in any way for pages
which aren't part of an index. I do not right now see any other way to do th
is 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=...ls
erver
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:48eb01c3e4ca$459c1710$a301280a@.phx.gbl...[QUOTE]
> 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?
> using DBREINDEX nor INDEXDEFRAG. You can
> and import the data.
> oi=djq&as_ugroup=microsoft.public.sqlserver
> message
> +
> tables a
> tabled
> be
> of|||Furthe to what Tibor has already said, you should read the whitepaper at
http://www.microsoft.com/technet/tr...ze/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...
quote:
> 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?
No comments:
Post a Comment