Hi Everyone.
I have a sql ETL Package running in a job that runs in 65 mins, before it was 6 mins.
it starts after we updated sql server from 2016 to sql 2019. it runs in 6 minute on sql server 2016.
if you have any suggestion , i would appreciate it.
thank you in advance.
do you know which part is taking the longest time? Hard to diagnose without knowing a little more info
Can you run it using visual studio and see where the issue could be
Thank you for responding. yes i have it runs fine on it. have already looked at the package and the build it good. it is loading huge amount of records in batches of 10 thousand. It starts off fast but slows down significantly with more loads. we are using Execute SQL tasks, and then a Data flow task that has an OLE DB source (for Oracle) and an OLE DB destination. checked the oracle side and it is fine. it seems the sql side is causing the slowness.
I have tried to look at the package and it looks fine, I also tried a couple of things : updated to the latest version and change the compatibility level but it still takes long.
Thank you for responding. yes i have it runs fine on it. have already looked at the package and the build it good. it is loading huge amount of records in batches of 10 thousand. It starts off fast but slows down significantly with more loads. we are using Execute SQL tasks, and then a Data flow task that has an OLE DB source (for Oracle) and an OLE DB destination. checked the oracle side and it is fine. it seems the sql side is causing the slowness.
What do you see on the sql server side when it is running? I.E. blocks, writes, waits, etc..? What are the specs on the new server? what are the SQL config values on it?
1 Like
so it runs fine and fast when run manually but slows down when run via SQL Job?
Correct.
check the server: disk space, log file sizes, ram, when running the job look at the mem usage. maybe play with buffer size settings of the SSIS package.
The fact that it runs fine on yours and slows down on server sounds like server specific issues?
thank you mike. No blocks, write waits. MAXDOP is 6, Parallelsim cost is 50
Disk space , log size is all under control. The memory is 164G
Thank you Yosiasz. so i now see PREEMPTIVE_OS_WAITFORSINGLEOBJECT when i run spwho2 while the job is slow.
Wonder if you need to update the sql 2019 to oracle connector?
Are you doing a linked server? Openrowset? How are you connecting