SSIS Package taking logn time

Question

SSIS Package taking logn time

anil702

Say Hey Kid

Points: 670
More actions
July 15, 2010 at 6:21 am

#141040

when we run ssis package on local server time span is 50 min
and the same package if he run on production time span is more than 2 hours
package is copying the data from one table to another table with in the database and other database not yet same time
then what will be the issue
And the local server is 32 bit
and the production is 64 bit

Viewing 8 posts - 1 through 7 (of 7 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Are there any other jobs running on production?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

syed.sherfudeen-707109 SSC Enthusiast Points: 138 More actions · Answer 2

Try to reduce the package size and if you have used SCD transformation alter with lookup and conditional Transformation.

Thanks,

Syed

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

anil702 (7/15/2010)
when we run ssis package on local server time span is 50 min
and the same package if he run on production time span is more than 2 hours
package is copying the data from one table to another table with in the database and other database not yet same time
then what will be the issue
And the local server is 32 bit
and the production is 64 bit

Ok 64 bit should have helped.. But apparently other factors are preventing it..

Bit questions, how much memory on the local server and prod server?

You are copying data from one table to another on the same server? This is true in production or is it copying from somewhere else?

What kind of disk sub-system does the lcoal have/Production?

Those are just off the top of my head.

CEWII

SanDiegoBeach Old Hand Points: 368 More actions · Answer 4

A couple of questions:

* Is the package running on the same machine as where the database is located?

* Are you moving this data using a data flow task or through another method such as the calling of a stored proc?

* When you moved the package to production, did you install it on the production server, or did you just update your connections to point to production?

* How many rows are you moving? How many columns are in the rows? Are the number of rows the same in both environments?

* Have you tried to run the package in production through BIDS so you can visually observe which portion of the package is taking up the majority of the time?

sqlsmokey Old Hand Points: 375 More actions · Answer 5

I have a production ssis package that has been running for over a year. It used to complete in under 1 minute. The package has had some intermittent long running instances in the last 3 months. However it now consistently takes over 7 minutes.

I know there is a blocking problem from General Statistics\Processes Blocked escalation and wait type of LCK_M_IS. I can restore the db to another box and the package runs in under 1 minute.

Here are some details:

The ssis package process:

truncates t1

loads t1 (insert into t1 select * from t2)

truncates t2

loads t2 from flat file on o/s (flat file is ftp'd from diff server - input file is same size)

Update t3, t4, t5

End

Isolation level = Serializable

The database:

has 2 files:

MDF = 9GB

LDF = 10GB (lots of small growths aka vlfs)

full recovery model w tlogs cut every 2 hours

nightly full backup, integrity check, reindexes (no extra stats update step)

The box is Windows Server 2008 R2 Version 6.1

24 processors

65GB RAM

SAN all RAID 5

SAN has 75% available space on data file (mdf) and 60% free on separate SAN drive for log file (ldf).

Mirrored local o/s drive with 64% free for sql engine

The pagefile lives on the o/s drive.

There are no filegroups.

No errors reported in the sql errorlog nor the package output.

These are the top 5 wait types in descending [resource wait time] order:

LCK_M_IS

DISPATCHER_QUEUE_SEMAPHORE

CXPACKET

OLEDB

LATCH_EX

There is no scheduled rebooted. Rebooted 37 days ago.

Plan cache confirms the long running query.

How do I determine the reason for the increased run time and how do I solve the problem?

Thanks

Phil Parkin SSC Guru Points: 246986 More actions · Answer 6

That's a good informative post.

Have you isolated the part or parts of the package which are taking a long time? If not, it might be worthwhile adding some logging to the package so that you can get this info.

The results will determine what you do next.

--Edit: You should probably have started another thread for this, by the way.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sqlsmokey Old Hand Points: 375 More actions · Answer 7

Phil,

Thanks for your reply, nod, and suggestion. The problem I was experiencing sounded eriely like the original poster and therefore I thought a good match. Hopefully their solution is as simple as mine. The nightly reindex only updates index statistics. OOOPS! and other colorful words to describe my utter lack of brilliance. The good news is that I stumbled across the solution by running sp_updatestats. It fixed it.

Here is an example and some background:

http://serverfault.com/questions/228122/rebuild-index-update-statistics

http://sqlblog.com/blogs/ben_nevarez/archive/2009/10/06/rebuilding-indexes-vs-updating-statistics.aspx

Thanks again to you, the good folks at SQLServerCentral, and all the tireless community contributors. Have a drink on me. 🙂

GO PASS!