Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Answered: SSIS Performance

    The package step basically dumps data from a view to a flat file - so nothing special there.

    Performance has been fine until recently, following a change to the underlying object definition.

    The changes to the view were:
    - A column that was previously returning no data (put in as a "buffer" so that the output file format didn't need any changes) is now being populated. The meta-data for the column was therefore out of date and needed updating.
    - This is a view-upon-a-view and the base view has been updated. This has caused us some small performance issues elsewhere but has been tuned appropriately.

    The view returns ~140K rows in total.
    In SSMS the complete resultset is returned in 3 seconds,
    In the SSIS package, it takes 25 minutes!

    It's not a massive problem, because this particular job is only executed every couple of weeks, out of hours. However it is making debugging a real pain!

    I've played around with the MaxBufferRows settings but this doesn't seem to be having an effect.

    Any suggestions on what else I can try?
    George
    Home | Blog

  2. Best Answer
    Posted by gvee

    "So this is happening again!

    There have been two view definition changes and the two branches of the package that interact with these objects have tanked.

    Cached query plans are my new favourite suspect.

    Armed with this idea I have come across the following, which looks to confirm this:

    http://blogs.msdn.com/b/sqlperf/arch...ficiently.aspx
    https://connect.microsoft.com/SQLSer...-or-ssis-hangs

    I'm going to go and rebuild the offending package steps now to see if this makes a difference"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Have you tried running the SSIS package during working hours to see how long it takes to run? I suspect that you might be running against another demanding process when the SSIS package runs for 25 minutes.

    If it still takes 25 minutes during the day, run the view in SSMS and the SSIS package together to see how long each runs.

    I've seen a few cases in SQL 2008 R2 where SSIS packages stored in msdb had a "booger in the metadata" that caused weird results after a schema change. Saving the SSIS package to an external file, deleting the SSIS package, then reloading it after a SQL Server restart seemed to resolve these problems but I never got any explanation for what the root cause was.

    I'll think on this and let you know if I have any more bright ideas.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yep, ran the job manually today, which is how I found the issue!

    The SSIS packages is stored on the file system already, not in msdb.
    George
    Home | Blog

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by gvee View Post
    Yep, ran the job manually today, which is how I found the issue!
    Oh you tease!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hmmm, looking through our SVN logs it would appear that the change to the base view is likely to blame for this.

    However I am struggling to understand how the same query in SSMS is smashing SSIS, 3 seconds to 25 minutes!

    I bet that rebuilding the package step from scratch would resolve the issue. I'll see if I can spare the time later today.
    George
    Home | Blog

  7. #6
    Join Date
    Jan 2015
    Posts
    2

    More information

    Are you running a simple query in SSIS?
    Is there something different between your code in SSMS and the SSIS?
    Is there something special with the new column?

  8. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So this is happening again!

    There have been two view definition changes and the two branches of the package that interact with these objects have tanked.

    Cached query plans are my new favourite suspect.

    Armed with this idea I have come across the following, which looks to confirm this:

    http://blogs.msdn.com/b/sqlperf/arch...ficiently.aspx
    https://connect.microsoft.com/SQLSer...-or-ssis-hangs

    I'm going to go and rebuild the offending package steps now to see if this makes a difference
    George
    Home | Blog

  9. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I can't believe how much of a difference that change has made!

    Basically swapped the data access mode from "Table or View" to "SQL command".

    Not sure whether it will hold up against further schema changes, but what I've done is pop a little commend in the SQL command so that those who follow know what they can do.
    George
    Home | Blog

  10. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    interesting. I guess I never saw this because I almost always use sql command or sql command from a variable.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Never used from a variable before *shrug*.
    This is a simple package that basically dumps the contents of views to CSVs - so I just took the path of least resistance.
    Needless to say that I won't be using the table method in the future.
    George
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •