x Want to participate? Join the group to interact
  • State Not Answered
  • +3 person also asked this people also asked this
  • Replies 14 replies
  • Subscribers 138 subscribers
  • Views 21372 views
  • Users 0 members are here
Browse Forums
Announcements
268
topics
260
replies
Databases and Operating Systems
758
topics
2k
replies
Financials Suite
1k
topics
3k
replies
General Discussion
2k
topics
10k
replies
Operational Suite
851
topics
2k
replies
Reporting and Analytics
1k
topics
3k
replies
Reports, Macros, and Customizations
876
topics
3k
replies
Sage CRM for Sage 300
182
topics
410
replies
MClark

Erratically slow performance Sage300

Posted By

A customer on a brand new server w/ Server 2012, SQL2012 and Sage 300 is reporting erratically slow response times, though it is getting more pronounced.  They have 5 concurrent users (about 12 workstations), about 5mb in data, no other applications running on the server.  Lots and lots of free space.

Sage 300 is installed on the server - with the volume of data and users they have, I thought this would be OK.  We're doing some mods and I was thinking of managing that process which will continue for some while.

If moving the program files to the workstation will make sense, we can do that, but is that the most effective route to go?  Or are there other things we should look into first?

TIA.

Mary

  • 0

    Moving the program files to the workstation NEVER makes sense, don't do it.

    Check for an aggressive virus checker, or a wonky network.

  • 0

    Open SQL Server Management Studio

    Open the server Properties

    Open Memory

    Change Maximum server memory to between 50% and 70% of total server memory.

    Click OK - no restart is necessary, this setting applies immediately.

    SQL Server 2012 aggressively uses as much server RAM as possible, leaving no available RAM for other server processes. You can verify this by looking at Task Manager, Performance (Free Physical Memory) and Processes sorted by Memory. You will see SQL server consuming most of the RAM. You want to leave at least 6GB RAM available for the server, so if you have 16GB RAM on the server and set SQL server max memory to 10GB - leaving 6GB for the server. This is documented in a Sage tech note somewhere.

    The other thing is if you are running SQL Server on a VM then you will continue to have issues until you move the SQL server off to a dedicated physical box.

  • 0 in reply to Ettienne Schwagele

    Ettienne,

    Can you elaborate on your comment regarding moving SQL off of a VM?  What is the reasoning behind it?

    Thank you.

  • 0 in reply to kkyle1

    It runs like crap on a VM, simple as that. The sites where we experienced random slowdowns or freezing where all running on VM, moving SQL server to a physical box resolved the issues.

  • 0 in reply to Ettienne Schwagele

    By "It" do you mean SQL or more specifically Accpac and its heavy queries? Meaning you think SQL runs like crap in VM regardless of the application making the calls or the size of DB.

  • 0 in reply to kkyle1

    SQL does not perform well on VM. Accpac users will experience slowdowns and freezing, but it is not Accpac itself. The application is really waiting on SQL to return datasets. If you kill some Accpac sessions then the remaining users will get some response back. This usually happens at busy periods, like at month end when users are running reports and posting many batches etc. If no users are in Accpac then SQL runs perfectly on a VM.

  • 0 in reply to Ettienne Schwagele
    Well you cant say "sql does not perform well on VM", because now a days everything runs on VM. I have many SQL vm servers for various things and they all perform fine. Perhaps people who think this don't know how to run VM servers, but its not a solution. We don't move machines off of VM.

    I am having random lagging issues as well with accpac. I have tried the fix you have mentioned on limiting sql memory to 50-75% of ram and will see if that works. Since this question is 3 years old, I was wondering if there are any other comments on accpac lag specifically to do with accpac. We have about 5 users using the database and the database size is not that big, MDF file looks to be about 3gb.

    We don't have consistent lag, just every once in a while, but when it lags it lags for 20min+. I am going to be taking a look at the resources next time it does that sort of a lag, but its really random so i am just relying on users to report it right now.
  • 0 in reply to iceicebaby
    Yeah I can say SQL does not perform well on VM, seen it many times. But can you hear it?
    The fact that everything runs on VM does not make it good.
    Look at the server resources when you get the lags. I bet you can force it to happen if you run something like an AR trial balance report from all the workstations at the same time.
  • 0 in reply to iceicebaby
    We had an issue where the VM was being backed up in the middle of the day. Normally the backup process would take less than a minute but on one of the VMs it would take several hours. No reason, it just did. But that was enough to cause more than at 30 second delay while the machine was being backed up and the Sage session lost it's database connection.

    So this is exactly like yours but here's the challenge: Because the VM was being backed up the computer had no idea what was going on and therefore nothing showed up in the event log files. In the end I set up a batch file that pinged the server every 10 seconds and I was able to see when the ping failed to connect.

    So you might want to consider that your slowdown might not be the VM but the VM environment.

    Best of luck with your search!
  • 0

    Hi.  We run lot of Sage 300s in the cloud.  

    VMs, perse are not the issue.

    Other things to try.  

    • Go directly to the SERVER and test performance.  IF the performance is running well from there - it could be a general network matter.  
    • If you have physical disks (RAID) make sure that those are operating well. 
    • Perform SQL LOG Maintenance
    • Modifications or customizations may not have been correctly scripted for optimal performance

    If you still have issues. Please give us a call and we will sand-box your system without charge to assess your data/erp safely.

    Thanks.

    (877) 888 5525

  • 0 in reply to GoGo
    Ok thanks. I havent been able to narrow down the cause yet, but thats all good ideas. I dont think its a hardware problem otherwise other services on the cluster would be suffering as well, there is no other evidence of hardware problems and its too infrequent to be failing hardware in my opinion.

    i guess its a mystery for now. i will post back if i have any new leads or figure it out.
  • 0 in reply to GoGo
    From the Sage 300 2016 Compatibility Guide:

    Avoid running servers as a virtual instance. At the time of writing, VMware vSphere endorses running database servers as a virtual instance. Sage 300 has not been benchmarked to run the database server on a virtual instance in VMware, despite the vendor allowing this configuration.
    (Page 7 of 17)

    support.na.sage.com/.../viewdocument.do
  • 0 in reply to Ettienne Schwagele

    Hi Ettiene, same suggestion applies for SQL Server 2016? We just uprade to Sage 300 2018.1 last week to a new server and users complain about performance but it is a robust new server and very good networking. They have Sage and SQL Server in VM.

  • 0 in reply to Oscar Gonzalez

    Hi Oscar, did you resolve the performance issue? I have a client on v 2018 and they are facing a similar problem. I will appreciate your reply.

*Community Hub is the new name for Sage City