Slow activities on db after long export import process

Question

Slow activities on db after long export import process

fasttrack

SSCertifiable

Points: 6071
More actions
September 19, 2006 at 1:14 pm

#169695

I have a question for you:
I have built up a procedure that export and import lots of data from a DB to another.
Export and import process is executed with bcp and use a temp db (custom, not SQL Server one).
This process takes long to be completed, there is a transaction to roll back in case of failure.
Tables under the execution are more than 400.
The process always completes successfully; then a procedure is called to run maintenance: DBCC DBREindex, UPDATE STATISTICS and shrink log and db file for custom temporary db.
This procedure runs on sunday.
On monday all activities start and It has been found out that everything is very low; this is obsved every time after the procedure runs.
Anyone can help me to suggested me some idea:
get better maintenabìnce plan,
or how to discovered if something is wrong.
I have checked the procedures and everything looks ok, all object instanced looks closed.
Transaction is olny in destination DB and is commited.
Any help will very appreciated.

Thank

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 1

Can you check if any connections are left open? Maybe there is one with a transaction left open/select pending. (last batchtime in the weeked)

For a custom temporary db, perhaps you may consider simple logging so you won't have to shrink the log-file.

You could leave the profiler & performance counter logs running in the weekend/monday to furthere analyze the problem.

fasttrack SSCertifiable Points: 6071 More actions · Answer 2

I have thought to set the custom tempdb to simple, but all tables created in tempdb are inside the transaction.

this may fault transaction rollback in case of failure ?

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Stored Procedures and Views are cached in memory with their execution plans. After you Update Statistics the SPs should be recommpiled and views are accessed more slower the first time for the first calling user. This is a normal process.

I also use DBCC FREEPROCCACHE after I update statistics so the new plans would be compiled using the new statistics.

Regards,Yelena Varsha

fasttrack SSCertifiable Points: 6071 More actions · Answer 4

I have spoken to people and user that after this kind of maintenance some object will be accessed slower....but you know lots of people what find problem everywhere.

Setting Simple the tempdb may be ok, temp db is only for temporary operations and I do not need transation log for recovery.

I 'll try some DBCC like FREEPROCCACHE to add to the maintenance plan.

Any further suggestion or discussion is very appreciated.