Also, too many virtual processors can actually slow things down as the system spends more time figuring out which processor to send tasks to than actually processing your data. If it’s a high number, try lowering it.
I’m not going to run any extra software until I get some info back from our other people, but just from the PDT it didn’t look like there was any problem with the actual processing. The numbers were half the pass mark.
We have three VMs, one for the DB, and two App servers. On Epicor’s advice each VM is tied to 8 cores from a total of 24 on the physical server. Epicor asked about turning off hyperthreading and our IT company said Fujitsu don’t approve doing that. Between them they agreed that raw performance seemed to be OK regardless. I’m not in a position to argue, but I will be forwarding these notes!
My instinct at this point is that there’s something wrong with SQL Server.
(edit) For completeness, we have two RX2540M4 Xeon Gold 6136 12C 3GHz, 192Gb RAM, SSDs in Raid 10 split 5 ways for C drive, Apps, DB, TempDB and Backups.
Lastly if you have a Firewall appliance such as SonicWall or Watchguard or Anti-Virus on the Servers. Make sure you exclude the SQL Traffic and SQL Paths from being scanned, i’ve seen that as a big contributor to slowness when Firewalls even filter internal traffic.
Hi @dhewi,
Not sure if this might help as well as all of the other great suggestions? I know you said it seems like only Epicor that is having problems but this might be something to try too.
Are you running SQL Standard or Enterprise Edition? If Standard, check the link out below. This is a change that we had to make to our VMs that Brent O. caught for us.
A quote from the link below:
SQL Standard Edition will only use the first 4 [sockets] here [in a VM environment], and that’s it. No matter how much load our SQL Server gets, it’ll only use 4 [sockets]– meaning CPU will only hit 50% busy.
[In order for your VM to actually use 8 cores you could…] Change the number of cores per socket…set it to a single-socket, 8-cores-per-socket VM, or a 2-socket quad-core, or a 4-socket dual-core.
CPU-Cores-or-Memory-Offline-the-Problem-with-Standard-Edition-VMs
5 Likes
@ERPSysAdmin so I have 2 sockets @ 6 cores each. I am good. Just verified I have Std edition.
1 Like
We are virtual on VMWare 6.5, one server running SQL and one AppServer, both on a NetApp SSD shelf. I am getting well within the spec for the PDT tool. In the screen shot you can see what I mean by the X86 Vs X64 bit versions of PDT. The X64 I highlighted in yellow.
We are working on 2 major issues
- Again, even something as simple as open a base form and all BPMs turned off seems slow.
- I am working with support on, what appears to be a data issue, with reports timing out.
- All BPMs Disabled
- Out of box report - I am assuming removes customizations
- Same test VM housing Pilot and Test slots
- IIS AppServer and Web config seems the same
- All DB instance and DB settings appear the same
- System Agent and Task agent settings appear the same
- Pilot’s data is from 7-11-2018 and Test data is from 09-18-18
When I run Inventory Usage for a single part in Pilot, it completes in 7 seconds or under to give the rolling 12.
When i run the same out of box report in test (everything being equal above) it takes 1minute 45 seconds
If I switch the database the time follows, leading me to believe this is data.
Is it possible to remove UD data and fields until I can find what may be the culprit?
PDT can only take you so far I wouldn’t rely on it to tell you much other than your setup is correct. Hows your SQL table index health? Double check the screens you are seeing issues in that any BAQs or BPMs are using ALL indexes for queries. and double double check that you are not joining temp tables to db tables directly, but querying them “.ToList()” and looping to do additional queries.
1 Like
Daryl,
I am not as familiar with Hyper-V, but I would imaging some of the same rules apply and for those who want to look using VMWare. This is a great guide for those common hardware settings mentioned. I’ll admit I skipped to BIOS and Power Management settings.
1 Like
Thanks a lot for that, it’s really interesting.
Sadly, it doesn’t look like the cause of our problem. We are on Standard SQL Server, but I’ve checked and all cores are online. But it may open up some further avenues to investigate.
2 Likes
Hi @MiguelS,
Yes, as long as your VM is setup with 4 or less sockets and 24 or less total vProcessors for SQL standard edition, you should be good. I can’t say whether any other configuration would work better for you, (such as 4 sockets with 3vPs each) but your setup looks like it follows those requirements! 
If you have more than 4 you can configure the SQL instance to only use certain CPU’s in the the Process or Affinity settings.
1 Like
Check your MAXDOP settings too in SQL
1 Like