we have an SSIS package that starts around mid night and does bunch of things. one of the insert statement in that package is causing slowness sometimes, runs for 90 minutes and sometimes runs in 42 or 20 minutes and during weekends it runs in 7 minutes.
I have checked for any resource contention, there is nothing going on except the database maintenance that runs in parallel and with the maintenance running in parallel the insert statement sometimes runs fast.
Environment : virtual Windows 2012 R2 SQL 2014 Enterprise Memory : 120Gb, max sql memory : 112GB vCPU's : 10
Initially i thought the issue might be with the memory because of the PageIOLATCH_SH and PLE going down at the same time and after increasing the memory the performance is still bad.
I'm thinking the amount the records they insert might be different for each days but app team specified that they are almost same ?
Do you think is there any issue with the Vmware environment, i'm thinking may be something with VM NIC card limitation ? our infratructre team already checked the disk performance and it looks good.
Execution plan for the insert statement looks totally good.
Can anyone shed some light on this ?