x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 10 replies
  • Subscribers 94 subscribers
  • Views 7906 views
  • Users 0 members are here
Browse Forums
Announcements
271
topics
260
replies
Databases and Operating Systems
771
topics
2k
replies
Financials Suite
1k
topics
3k
replies
General Discussion
2k
topics
10k
replies
Operational Suite
854
topics
2k
replies
Reporting and Analytics
1k
topics
3k
replies
Reports, Macros, and Customizations
878
topics
3k
replies
Sage CRM for Sage 300
183
topics
415
replies
wysiwyg

Very poor performance on SQL 2012 and Hyper V

Posted By

Hi,

I'm running Sage 2014 PU3 on a very decent DELL Poweredge R720 server with Hyper V, Windows Server 2012 R2 and SQL Standard 2012 SP2. (going to install SP3)

I have created 2 VMs, one on which I've installed Accpac and it runs the terminal server for approx. 25 users as well, The other runs jus SQL.

I've given the SQL server 64GB dynamic RAM, database size is 45GB.
Terminal server with Accpac got dynamic RAM as well with a minimum of 12GB.

Both VMs are on the same host, which does not run anything else.
They use the same virtual switch in Hyper V. The switch is not shared with the host.

I used to have SQL and Accpac installed on the same VM, but have seperated them after an upgrade.
Reports such as IC Item Valuation (no datapipe) or processes like IC Item Number Change or data integrity now run significantly slower when compared to having just 1 VM.
Often the report takes at least twice the time to finish.

I ran the script for the SQL wait statistics http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
and get below 2 lines returned.
94% of my waits are Network I/O.

My NIC drivers are up to date.
I used to have an issue with VMQ and Broadcom NICs as explained here> https://www.reddit.com/r/sysadmin/comments/2k7jn5/after_2_years_i_have_finally_solved_my_slow/

After NIC driver update this was however resolved.

I'm curious whether any of you have Accpac setup in a similar way and could advise whether their wait statistics show a high network I/O wait also.
Am also keen to know what else I can try to lower the wait time or is it just poor programming of of the client (Accpac) as explained here?
https://sqltimes.wordpress.com/2013/08/05/sql-server-wait-types-async_network_io/

Parents Reply Children
  • 0 in reply to wysiwyg
    Ok, after some more testing I reckon the wait statistics aren't really an issue in my case.
    Just opening IC Location details with no processing will leave a task of wait type ASYNC_NETWORK_IO open in SQL Server.
    This is just distorting the picture if there are few transactions on the server.

    My main issue with reporting however remains.
    Generating reports happens relatively quick. Even exporting them to Excel is quick, however when it reaches 100% of the export, Crystal just freezes and stays like that for about the same time or longer it took to generate the report.
    I've been testing it in report designer and it's the same.

    If I test it in report designer on the VM running SQL server it freezes as well, but only for a few seconds.
    What is Crystal doing when it reaches 100% of the export? It's doing something because it's using CPU time.
  • 0 in reply to wysiwyg
    My guess would be that it is saving the file to disk. Check your antivirus settings, it could be scanning the file when it is saved. Try disable AV momentarily and see if it makes any difference.
  • 0 in reply to 49153
    Have tried that already. Makes no difference.
    Both VMs have antivirus, but only exportig the report from the VM with Accpac is slow.
    I do the same on the VM running SQL and all is sweet.

    Exporting to PDF works on both VMs the same by the way. No speed issues.
  • 0 in reply to wysiwyg
    Which drive is the Excel file saved to? A local drive or a network drive?
  • 0 in reply to 49153
    On a local drive.
  • 0 in reply to wysiwyg
    So, I've now set up another VM to test it on a clean machine. No Accpac installed.

    As soon as I install the RDS role, speed exporting from Crystal drops. And I'm exporting from the report designer.
    Exporting ~150k rows to PDF is ~15% slower.
    My major issue however remains with exports to XLS which takes 4 times longer with the RDS role installed.

    Instead of 4.5 minutes I wait 18.5 min.
    Once I remove the RDS role it goes back to it's normal speed again.

    The issue therefore seems to be RDS.
    I have no printers installed except the Microsoft XPS Document Writer.
    I installed a PDF printer (FreePDF) but no changes.

    What else to try?
  • 0 in reply to wysiwyg
    You may have to reach out to MS and/or SAP support.
  • 0 in reply to 49153
    Also there are far more efficient ways to get Sage 300 data into Excel, exporting Crystal reports is the least efficient way.

*Community Hub is the new name for Sage City