sql server (alternate): Hi group,

I have a 175MM record table, with a record length of 200 bytes (about 20
columns).

Sometimes when I run a very simple DTS to import our monthly text file
of new records (about 10 million records) it really flies (takes less
than an hour or 45 minutes).

However, sometimes it takes forever...running over 6 or 7 hours before
finishing.

When it takes forever I run sp_who and don't see any blocking
processes...

To ensure that things move as quickly as possible I always drop the
primary key and the indexes before importing, so it shouldn't be getting
tied up trying to update the indexes.

What sort of things could be holding up what I would expect to be a very
simple process of appending the records to the end of the current
table...?

Thanks,

Warren Wright
Experian-Scorex



*** Sent via Developersdex http://www.developersdex.com ***

If this helps, I notice that if I open a second Enterprise Manager
instance and try to go into the "Management->Current Activity" section,
Enterprise Manager just shows the hourglass symbol and never finishes
gathering the info on current activity....

Seems like SQL just gets bogged down.

Thx,

Warren



*** Sent via Developersdex http://www.developersdex.com ***

Warren Wright (warren.wright@experianscorex.com) writes:
[quoted text, click to view]

I think that is the form that gets blocked, if someone has created a
temp table in a transaction.

For a possibly better alternative to Current Activity, you can try my
aba_lockinfo, see http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

Also, the Profiler can be good to track down what is going on.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at