Clustered index with a truncate table operation question.

I have a situation at here that appears once in a while:
The table in question has a single clustered index on ReferenceID. It's
populated by the following process: Truncatae table MyTable, run DTS to
populate the table.
This runs every xx minutes.
The table has roughly 50K records but the records are large.
The problem occurs during a query similar to the following:
select ...
from MyTable
LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
MyTable.AssociatedID = seconds.MasterID) as Calculated on
Calculated.ReferenceID = MyTable.ReferenceID
At one point this query, which normally takes 30 seconds or so, went to over
16 minutes to complete consistently.
Eventually we ran
dbcc dbreindex(MyTable)
and that seems to have fixed it.
My question is why: We clear the table before repopulating, how did the
index get so jacked up?
Thanks,
EricYour problem is likely caused by internal or external Index Fragmentation
(based on your reporting that dbreindex() fixed the problem... This is cause
d
by having a clusterd index on a field (or COmbination od fields) for which
inserts are randomly dispersed. So, if your CLustered Index is on
ReferenceID, try to ensure that DTS sorts it's data by ReferenceID before it
runs... Then the pages in the clustered Index will be as full as possible,
and both internal and external fragmentation will be at a minimum. If you
can;t do this, then you need t orun dbcc reindex after every DTS run, or
choose a different Clustered Index that you can sort the DTS import data on.
.
"Eric" wrote:

> I have a situation at here that appears once in a while:
> The table in question has a single clustered index on ReferenceID. It's
> populated by the following process: Truncatae table MyTable, run DTS to
> populate the table.
> This runs every xx minutes.
> The table has roughly 50K records but the records are large.
> The problem occurs during a query similar to the following:
> select ...
> from MyTable
> LEFT JOIN (select .. from MyTable JOIN MyTable Seconds ON
> MyTable.AssociatedID = seconds.MasterID) as Calculated on
> Calculated.ReferenceID = MyTable.ReferenceID
>
> At one point this query, which normally takes 30 seconds or so, went to ov
er
> 16 minutes to complete consistently.
> Eventually we ran
> dbcc dbreindex(MyTable)
> and that seems to have fixed it.
> My question is why: We clear the table before repopulating, how did the
> index get so jacked up?
> Thanks,
> Eric
>
>