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 ?
Best Answer
You may have a disk bottleneck. Run a Perfmon.exe recording specifically you want the individual "average disk read wait queue" and "average disk write wait queue" of all drives. Perfmon is part of windows functionality and is on all Windows PC / servers / etc. You will want a recording from a weekday and a recording from the weekend.
Are you running DBCC database integrity check on weekday nights? While I want this running every night, it causes to much performance problems so to live in reality, mine only runs on weekends.