DTS package takes longer than normal

Question

Post reply

DTS package takes longer than normal

JAR

SSC-Addicted

Points: 414
More actions
December 4, 2006 at 11:25 am

#95018

OK, I realize this is like looking for a needle in a hawystack, but I just want to remove some hay to make the needle a little easier to find...

I have a DTS Package that normally takes about 1 hour to run and last night took about 8 hours to run.

It runs on a SQL 2000 machine (SQL Server A). The package truncates tables in a SQL Server A database, then loads data into those empty tables from another SQL Server (SQL Server B).

What can I do to determine why it took so long to run?

I don't see anything unusual in the SQL Server logs or the #Event viewers on Server A.

If setting up a Profiler trace is the best way to catch the culprit, what event classes should I monitor?

Any advice is greatly appreciated.

Jason

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

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

Jon Russell Hall of Fame Points: 3048 More actions · Answer 1

Jason,

A lot could be going on here, but I will share with you some of my experiences with packages that run unusually slow. Since it normally only takes 1 hour then, obviously, something has changed since the prior run. I also assume the amount of data the package is importing is about the same.

One time I changed some jobs around the prior day and accidentally scheduled too many jobs to run at once. Could it be possible that that you have too many large packages or backups running at once? Anything else using up CPU resourcs at this time. Maybe a possible network issue?

Have your maintenance plans been running? Has a developer been playing around with the tables in question, like indexes? Have any system engineers been on the server?

I would also take a look at the tables in question. Running a trace this evening when the package normally runs may be a good idea. Look for open cursors, database file growth, deadlocks, and table scans. Basically anything else that will kill server resourcs, or anything else that will slow down package.

Jon

JAR SSC-Addicted Points: 414 More actions · Answer 2

Thanks Jon,

Turns out the source tables are part of an application and that application was updated over the weekend. So it's possible some indexes were outdated (?) by the time the job ran again.

Maybe running DBCC REINDEX prior to the operation will ensure this doesn't happen again?

In the job history, I noticed that a similar run duration occurred once 5 months ago. I will trace things when the job runs again tonight.

Again thanks for the feedback-

jason