If you want it optimal yes. As a test if you were to truncate a table with
an existing clustered index and reload it, then do a DBCC showcontig you
would see fragmentation where if you tuncated it, dropped the clustered
index, loaded the table, rebuilt the clustered index, then do a showcontig
it would be 99% defragged.
I believe this is because a truncate resets the table pointers to zero and
frees all the pages. Then when you start filling the table you get page
split after page split, because of the existing clustered index, until the
data is in. Any rebuild of the clustered index after that load reorders the
data and lays it out new on new pages with the proper fill factor.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Ellingson Cory (RCIS)
[mailto:mssqldba-ezmlmshield-x63130618.[Email address protected]
Sent: Thursday, June 30, 2005 1:20 PM
To: LazyDBA Discussion
Subject: RE: Index question...
But, the general thought is that the clustered index will need to be rebuilt
each refresh? The table itself is not dropped, just the data, and the
(only) index.
Cory Ellingson
Production Services
-----Original Message-----
From: Paul Schlieper
[mailto:mssqldba-ezmlmshield-x99823918.[Email address protected]
Sent: Thursday, June 30, 2005 1:09 PM
To: LazyDBA Discussion
Subject: RE: Index question...
I went with the "...a table that is built fresh each night..." part.
-----Original Message-----
From: Davis Ralph
[mailto:mssqldba-ezmlmshield-x75807676.[Email address protected]
Sent: Thursday, June 30, 2005 2:07 PM
To: LazyDBA Discussion
Subject: RE: Index question...
THIS IS TRUE IF BY REBUILDING THE TABLE YOU MEAN TRUNCATING AND RELOADING
COMPLETELY. If not, and you are doing a refresh/update, then if you also
have non-clustered indexes, with the clustered index, on that table it could
complicate things. This is because dropping the clustered index forces each
of the non-clustered indexes to be rebuilt automatically. Then putting it
back on will rebuild them again. In that case a DBCC DBreindex might be
better.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Paul Schlieper
[mailto:mssqldba-ezmlmshield-x93830260.[Email address protected]
Sent: Thursday, June 30, 2005 12:54 PM
To: LazyDBA Discussion
Subject: RE: Index question...
You're way better off dropping the index(es), loading the table, then
re-creating, clustered first.
P
-----Original Message-----
From: Ellingson Cory (RCIS)
[mailto:mssqldba-ezmlmshield-x5019876.[Email address protected]
Sent: Thursday, June 30, 2005 1:47 PM
To: LazyDBA Discussion
Subject: Index question...
Hello all!
I recently was informed of a database I now get to support. I have Lumigent
Log Explorer, and I have it set up to tell me when certain commands happed
(new tables, dropped tables, dbcc's, new indexes, etc) and I get an email
daily at the same time about an index that is dropped and recreated. After
some looking, I found a SP that issues this command, and it is part of a DTS
package. It appears the reason for this is there is a SP that appears to
look for duplicate records to remove before the final step to refresh a DB
user table. This index is a clustered index; the table is rebuilt each
night from numerous other tables...
Now, the question...
Is it necessary to drop and recreate this clustered index over a table that
is built fresh each night, or will the index continue to resort as it is
written?
I want to get rid of this statement...I was thinking my next step would be
to do a DBCC DBREINDEX (<table>, '<index name>'). I first wanted to see if
I needed it at all.
TIA
Cory Ellingson
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is
intended
solely for the person or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in reliance upon this
information by persons or entities other than the intended recipient is
prohibited. If you received this in error, please contact the sender and
delete
the material from any computer.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is intended
solely for the person or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in reliance upon this
information by persons or entities other than the intended recipient is
prohibited. If you received this in error, please contact the sender and delete
the material from any computer.
MS Sql Server LazyDBA home page