Very poor performance on SQL 2012 and Hyper V

Posted By wysiwyg over 8 years ago

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/

0 49153 over 8 years ago

If you really want answers then contact Don Thomson at Tairox www.tairox.com
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 wysiwyg over 8 years ago in reply to 49153

Thanks. I'll try that.

Could somebody else with a similiar setup run the script from this website and post their results please?
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 wysiwyg over 8 years ago 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.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 49153 over 8 years ago 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.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 wysiwyg over 8 years ago 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.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 49153 over 8 years ago in reply to wysiwyg

Which drive is the Excel file saved to? A local drive or a network drive?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 wysiwyg over 8 years ago in reply to 49153

On a local drive.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 wysiwyg over 8 years ago 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?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 49153 over 8 years ago in reply to wysiwyg

You may have to reach out to MS and/or SAP support.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 49153 over 8 years ago 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.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage 300

Very poor performance on SQL 2012 and Hyper V

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING