We are provisioning a new ProLiant Gen10 production server with ProIV v10.503 and SQL Server 2019. Both are installed on the same server.
I have an issue that has been driving me crazy and am hoping someone out there can shed some light on the cause. We have a process called Financial Consolidation (CON) that I have been using for bench mark speed testing. This server is not yet in production and has very little running on it while I'm testing. CON is made up of a dozen update functions. Basically it deletes a lot of SQL records then reloads them by reading from the same SQL table and writing back into it under a different company code key. It's all SQL processing. The delete of SQL records is done by a separate update so that is committed after it completes.
The time to run this process should take around 11 minutes. Some days it takes 11 minutes and other days it takes 19 minutes. I have 3 identical ProIV environments on this server. Each database is identical. I can run CON separately in all 3 environments and it may run fast on two but slow on the third. Or, it runs fast in one and slow in the other two. It randomly changes after a restart of ProIV. What's really strange is if I run CON in all 3 environments at the same time they typically all complete at the same time in around 13 minutes. And, what's stranger too, sometimes when I run the slow environment again after running in all 3 simultaneously it now runs fast. It will continue to run fast under the next restart of ProIV.
I have tried changing the ODBC drivers between “SQL Server Native Client 11” and “ODBC Driver 17 For SQL Server” driver without any noticeable difference. I have also changed the SQL setting "Max Degree Of Parallelism" from 8 to 0 and vice versa without any difference. This server is set up like our current production server where CON takes consistently the same time to run no matter what the environment.
Do anyone have any idea on what could be causing this speed anomaly? Any ideas would be greatly appreciated.