SSIS performance issue after Migration to SQL 2016

Question

Post reply

SSIS performance issue after Migration to SQL 2016

alex.palmer

SSCrazy

Points: 2292
More actions
October 10, 2019 at 2:05 pm

#3687740

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

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

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

DinoRS SSCrazy Points: 2683 More actions · Answer 1

DinoRS

SSCrazy

Points: 2683

October 10, 2019 at 2:28 pm

#3687742

Have a look here

Lou SSCrazy Points: 2929 More actions · Answer 2

Hi,

Have you checked this

https://support.microsoft.com/en-us/help/4338636/fix-sql-jobs-fail-due-to-blocking-in-ssisdb-in-sql-server-2014-2016-an

Good luck

alex.palmer SSCrazy Points: 2292 More actions · Answer 3

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

Lou SSCrazy Points: 2929 More actions · Answer 4

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: 2292 More actions · Answer 5

Hi luismarinaray

SSIS is not my string suit.

Could you point me to some documentation.

Cheers

Alex

Lou SSCrazy Points: 2929 More actions · Answer 6

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: 2292 More actions · Answer 7

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

Lou SSCrazy Points: 2929 More actions · Answer 8

Friend,

Your problem is not in the System Databases, may be in your user databases, so I could change the compatibility level only in these databases. If possible, ask your ETL developer for help and identify running queries/databases, not MSDB.

Good Luck