SSIS performance issue after Migration to SQL 2016

  • alex.palmer

    SSCrazy

    Points: 2232

    Hi

    I'm migrating a 2014 SSIS server to 2016. After the upgrade one of the packages we test with is taking around double the amount of time to run on the new server.  Around 400 seconds as oppose to 200 seconds.

    Something I can see in the profiler traces is an sp_ssis_getpackage call, then a 3 second pause and a exec sp_reset_connection

     

    These are consecutive rows in profiler and it happens around 30 times in the profiler trace.  I do not see this behaviour in the old 2014 server.

    Is this my smoking gun? Can anyone shed any light on it?

    Cheers

    Alex

  • DinoRS

    SSCrazy

    Points: 2513

    Have a look here

  • luismarinaray

    SSCrazy

    Points: 2784

  • alex.palmer

    SSCrazy

    Points: 2232

    Hi

    Thank you both for the replies.

    I'd already read the stackoverflow and it's full of useful information but I can't see how it moves me forward. The 2016 server is SP2 CU 9 so the KB should be fixed.

    Cheers

    Alex

  • luismarinaray

    SSCrazy

    Points: 2784

    Hi,

    Have you checked the compatibility level, of the query in your SSIS, I'm assuming that you have a query inside your package.

    Good luck

  • alex.palmer

    SSCrazy

    Points: 2232

    Hi luismarinaray

    SSIS is not my string suit.

    Could you point me to some documentation.

    Cheers

    Alex

  • luismarinaray

    SSCrazy

    Points: 2784

    Hi,

    Assuming that your target database in the ssis package has an updated compatibility level that is different from your source instance.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?redirectedfrom=MSDN&view=sql-server-2017

  • alex.palmer

    SSCrazy

    Points: 2232

    Ok I slightly misunderstood you, I thought there was a separate compatibility level setting in the SSIS packages.

    All the imported databases are compatibility level 120. MSDB is 130, I did try lowering this to 120, so it was in line with the user databses, but this made no difference

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

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