SSIS Package taking logn time

  • 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

  • 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

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

    Thanks,

    Syed

  • 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

  • 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?

  • 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

  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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!

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

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