Sage running slow over network - A FIX
Posted by LiterallyPizzaSauce@reddit | sysadmin | View on Reddit | 32 comments
The fix:
The way that your hypervisor presents CPU's to the VM running Sage is important. You want to present them with as few sockets as possible. VMware ESXi with 8 CPU's allocated, change the VM CPU option to 8 CPU's per socket. Wa-la!
The situation:
I don't fully understand the "why" but someone here who is smarter than I am can probably explain.
I work at a small MSP and was recently transferred a client that has had an issue with Sage 100 Contractor for the past 6 months. They added Sage Paperless to the equation and ever since then, running Sage from anything other than the hosting server ran like absolute dog shit. They could RDP to the Sage server and get good performance but there were other issues with that which made it annoying to use. The previous admin, our engineer, and others were never able to get it back up to pre-Paperless speeds.
Paperless got moved off the base Sage server to its own VM in an effort to restore performance but that didn't help. I don't know what other troubleshooting had been done but I just know none of it worked. I decided to treat the Paperless integration as a red herring and explore other options.
I asked the client to run some reports while I watched task manager on the server to see if anything obvious came up. Whenever the client ran a report CPU0 would just get fuckin slammed and the other 7 cores were just chillin. I checked the CPU affinity for the process and they were all allowed. I took away CPU0 and then CPU1 just gets fuckin slammed like his friend did while the other 6 just went on break. Okay so Sage is only using a single core to run reports, no wonder it's not working well. I contacted Sage support and you know how that goes so I start digging more. I watched resource monitor and noticed that sqlserver.exe was the chonker of a process while running reports. So why didn't sqlserver.exe use the rest of the cores?
I start looking around on Google for reason why SQL servers would only use a single core. I find a thread from some developers, probably making something fuckin sweet, talking about how they allocated 16 cores to their SQL process but the config showed that only 4 were online. A comment on that thread mentioned that Microsoft SQL server license tiers limit how many CPU's can be used. So I go check Sage and see that it includes SQL Server 2019 Standard, which allows for like 2 CPU's (although it really seemed to be using only one). I go look at Vsphere and see that Sage is set up with 8 CPU's in 8 separate sockets. No shit. So I tell the user I need to shut down the server for a sec. I changed the 8 CPUs on 8 sockets to 8 CPUs on 1 socket because the fucker was only using one "socket" as it was. Booted back up and told the client to run something real quick. The message I got back was in caps lock.
This information doesn't exist online in this context yet the issue exists because I saw it unsolved over and over again. Hopefully someone who needs this information finds it and becomes a hero. Go forth and condense your CPU's into fewer sockets.
ITLevel01@reddit
Good stuff. I’ll dig through our docs to see if I can find this resolution on our end. Could’ve sworn we identified this issue years ago. Not sure if or how it was fixed.
LiterallyPizzaSauce@reddit (OP)
Are you a Sage person? I am waiting for Sage techs to get back to me still so I can tell them to add this to their troubleshooting/docs
ITLevel01@reddit
Not a Sage person. Just wanted to check if what we did matched your solution. We actually had to adjust the Max Degree of Parallelism setting in SQL Server settings. I observed the same thing. Only one processor was working while the remaining 7 were chilling until MAXDOP was changed to 8 (default 0).
LiterallyPizzaSauce@reddit (OP)
Same issue. Different fix. Effectively the same result. Thanks for adding it here!
amgeiger@reddit
This sounds like the old SQL Express hack of running 8 cores 1 socket to get around socket limitations.
LiterallyPizzaSauce@reddit (OP)
Definitely in the same spirit
rw_mega@reddit
Get Sage advanced, free upgrade and eliminate all you issues. Trust me. All your issues solved.
Not…. It’s your network….. it’s your firewall….. server should be on same subnet….. physical server is better than virtual….i have heard all of these from sage consultant.
On sage Reddit I read that they have had so many issues with it that if you ask them they will upgrade you to advanced for free. Same annual cost
LiterallyPizzaSauce@reddit (OP)
I'm new to Sage with this client actually. All my other clients have been using Quickbooks up until this point. When my first two interactions with Sage went equally poorly, I knew I was in for a good time.
rw_mega@reddit
I have gone into the weeds with sage, it’s written in a language called ProviderX. (I think that’s it)
They will tell you “it works fine when you rdp into the server; so is your network” “just have users rdp into the server”
The “fileshare” is not a true file share because the clients should not use unc paths. They should use mapped drives. So when the app calls files its letter:\mas90\…
Problem is Windows 10 and 11 will convert to unc during install. Even if installing from mapped drive.
To make things faster/better I tired mapping manually/by gpo/by vbs/ by batch/ using server name/fqdn/ipaddress all the same result.
Will work and unexpectedly throw an error. Multiple errors throughout the day. I only had 3 users so this made no sense.
Finally the consultant got fed up I was showing him things from Sage support forums that he wanted to have his msp review my infrastructure. F-that.
It was here that I found out people talking about Sage advanced. But there is a SQL version that you would have to pay for; and Sage advance non-sql. The non sql version introduces obdc connection on a specific port. Fixed everything. All my users and more no more issues. And a free upgrade, still $500 for conversion but the license was free
LiterallyPizzaSauce@reddit (OP)
I remember seeing a solution for our problem that mentioned "spawn tasks from server" but that didn't exist on our installation so I didn't venture down that path at all. I likely would have made it there eventually if other steps didn't help.
Maybe that last paragraph will help the fella down the thread a bit who is talking about his Sage 300 on HyperV running slow
rw_mega@reddit
Sage 300 is SQL by default (and if far more expensive), Contractor or Sage 100 is not. Sage 100/Contractor has three flavors.
Sage 100/Contractor Standard (written in provideX or “PVX” you will see that in error codes)
Sage 100/Contractor Advanced (non-sql) -after reporting problems with sage consultant (for me 3 solid weeks) they did offer the free license to advanced.
Sage 100/Contractor Premium (SQL)
Mine was on Esxi host with 10gb fiber. But you can read that other people literally put everything in the same room and still had problems because it was a VM.
I had done so many alternations to the server settings, Nic card, registry, and that we decided to spin up a new vm to install advanced. Hence the $500 conversion fee consultant had.
As far as I can tell advanced effectively uses the software to make the server do all of the work instead of the endpoints. Which is the purpose of a server in the first place. Standard try’s to make the clients do all the work locally but it doesn’t have a keep alive setting. So it keeps messing up even when there is nothing else going on in the network.
Sorry for long posts Sage eat up way too much of my time and I am passionate about telling anyone to run away if they can. But if I can save you months of angry clients and sleepless nights here is what I did to fix.
LiterallyPizzaSauce@reddit (OP)
The more helpful information online, the better. Maybe my fix won't help someone else looking for answers to a similar situation but then maybe your comments end up being exactly what they need. Who knows, but thank you for sharing!
Fatel28@reddit
Sage really needs to be ran on a terminal server. Running it on the end user workstations is just asking for trouble. It runs pretty good as a remoteapp too. Just stay away from redirected printers. It hates those.
iHopeRedditKnows@reddit
We present the Sage 50 app to end users via Citrix and the app "crashes" every time you open and close the session, takes forever to startup and shutdown, but it works in the end.
ShoulderIllustrious@reddit
This sage app sounds like a pos.
EthanW87@reddit
You're a hero
LiterallyPizzaSauce@reddit (OP)
Did this help?
urjuhh@reddit
Ah yes.... That one time when 4 cores out of 16 were at 100% usage and overall sql performance was doo-doo...
LiterallyPizzaSauce@reddit (OP)
I'm not sure if I'm following
urjuhh@reddit
Our servicedesk stepped on similar rake... A vm that had 16 cores and 16 sockets ( core per socket ) had 4 first cores running at 100% and others at few %. Something related to mssql licensing... That the sql instances were able to use only four sockets. Happened during adding cores to the vm, the guy who changed the values was not familiar with the process, thought it was simple as changing 14 to 16. But that change also changed the amount of sockets to 16. Thus the "help, our systems are slow"-phonecall from client. Fix was to shut the thing down and change the number of sockets. To 1 in our case and give sql access to all 16 cores via one socket. Pretty similar situation, yes/no ?
LiterallyPizzaSauce@reddit (OP)
Okay yes, sorry. Pretty much the same situation! I was reading a sarcasm tone there that you weren't intending.
Devilnutz2651@reddit
We're running our Sage 300 VM on Hyper-V. Always have people complaining about it being slow. I'll have to check this out. Thanks!
LiterallyPizzaSauce@reddit (OP)
Another user mentioned above that there is also a non-SQL version of Sage that may be a solution too if your HyperV doesn't allow you to condense your CPU's down properly. Sage Advanced with OBDC is what he was talking about
bbqwatermelon@reddit
Hyper-V does not have delineation of cores versus sockets. It does have NUMA related statistics and settings regarding the host you could investigate but I will always suspect storage subsystem first if not running on flash storage. It is noticeably less efficient in Iops than ESXi but that all goes away with flash storage or virtual hard disks on tiered storage spaces.
LiterallyPizzaSauce@reddit (OP)
I hope it is!
Fatel28@reddit
I'm amazed sage was the only thing having this issue with 8 cpus with a single core each. That's a huge oversight on the part of whoever setup that VM
LiterallyPizzaSauce@reddit (OP)
My understanding from the documentation I've read is that this is recommended practice by VMware for applications that don't limit the amount of "physical" CPU's. It is also default behavior to have all logical CPU cores assigned as their own CPU socket. We have many other VMware deployments that run fantastic with this config.
Fatel28@reddit
That seems ass backwards. Very few applications are smart enough to use multiple CPUs. But computers are also lightning we tricked into thinking so
illicITparameters@reddit
It was best practice prior to ESXi 6.5
Firefox005@reddit
I wouldn't be giving blanket statements like that.
If you want to learn more read up on NUMA, check out section 3.6 specifically in this whitepaper: https://www.vmware.com/docs/sql-server-on-vmware-best-practices-guide
LiterallyPizzaSauce@reddit (OP)
To address your second link, the version of MS SQL Server is bundled with Sage 100 Contractor so unless someone creates a custom installation, my situation will be similar, if not the same, to others who are using the same version of Sage.
LiterallyPizzaSauce@reddit (OP)
Yeah good call, I just removed that line. I will check out the link. Thanks!