November 21, 2019 at 10:32 am
Hi
We have a performance issue pulling data from an Oracle server into a staging database on a SQL server.
The job pulls from 2 tables in Oracle (40 and 80 million rows) and populates tables in SQL, 10,000 rows at a time. On a Windows 2012, SQL 2014 BI server it takes 40 minutes to run. On a new Windows 2016, SQL 2016 Ent server it took 6 days to run.
The servers have the same RAM and CPU , are on the same Vlan and are pulling from the same Oracle server. We have tried doubling the CPU and RAM on the new servers with no impact.
Network traces show the communication with Oracle it quick and the SQL server doesn't seem to be doing much work. It seems to be the DTExec process where the performance hit is. The first million or so rows seem to work ok, and then it grinds to a halt. On the old (performant) server perfmon shows the CPU, disk and network working quite hard. On the new server DTExec is using high CPU but the network and disk are not doing much. Running sp_whoisactive on the SQL shows periods of time where SQL is not processing anything.
Does anyone have any ideas?
Cheers
Alex
November 21, 2019 at 12:56 pm
If possible, I suggest using the same version of Oracle Data Access Components (ODAC) on SQL2016 as you did for SQL 2014. ODAC 12.2 release have some very nasty bugs, causing SSIS consuming 30+ GB memory(when I pinpointed the issue).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply