question

Administrator-3461 avatar image
0 Votes"
Administrator-3461 asked ErlandSommarskog commented

MSSQL performance is poor with Association Management software in Azure

We have an Association Management software program that has 3 components, the web server, MS SQL, and the association management server. The web server running on standards HDDs is running perfectly fine. The AMS software and SQL are really sluggish, performance is poor, and we run into frequent DB connection errors. We have upgrade the drives to Premium SSDs for both. our virtual machine sizes are as follows:

Web server - Standard_B2ms
SQL - Standard_B4ms
AMS server - Standard_DS11_v2

I would appreciate any help with this.

Thanks!


azure-virtual-machinesazure-sql-virtual-machines
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Administrator-3461 avatar image
0 Votes"
Administrator-3461 answered ErlandSommarskog commented

One last question, do you know of a good replacement for Microsoft's RemotApp? I saw the one by Citrix but that is too pricey for me.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Not sure what this has to do with the rest of the thread, nor why it suffices as an Accepted Answer. Anyway, I don't even know what RemotApp is, unless you are referring to Remote Desktop. I'm an SQL Server guy, so this is way out of my league.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

A not uncommon reason for this problem is poorly written queries, lack of appropriate indexes etc. Throwing hardware at the problem can help to some extent, but eventually, you will need to look into query tuning, reviewing indexes etc. Or, if this is a vendor application, you will need to open a support case with the vendor for things to happen.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Administrator-3461 avatar image
0 Votes"
Administrator-3461 answered

We have run this program for 25 years and it is very standardized. We had 0 issues with performance when it local and on different physical servers. it's also slow when running a simple query lookup on the SQL server itself. I still had a copy on a local machine and the query was done as soon as I executed it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered Administrator-3461 commented

Sorry for the late reply. I'm not very good at all these virtual machine sizes, but rather they leave my head in a whirl. So I hoped that someone else would chime in.

But it sounds as if you need to go for bigger sizes. How much RAM does these VM have? How does that compare to what you have on-prem?

How have you concluded that the AMS server and the SQL Server is slow? Have you made some form of monitoring to measure. (Bear in mind that I am entirely unfamiliar with AMS.)

I'm thinking that you should up the size of one these servers at a time, so that you can see what has effect and what has not.

Also bearing mind that bigger disk sizes also gives you better throughput, even if you don't need the size as such.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your reply!

It does work slightly better when I increase the disk size. On prem and in the cloud we use 16gb of memory and more than enough. We're barely using 6gb. We're at 50% on the CPU in the cloud and it's slightly more than when it was local. We tried increasing network speed but it didn't really make any difference. When it on-prem, the 3 VMs were on one physical server. It took about 4-5 seconds for the management software to open and member lookups were real-time.Even working off the server in the cloud it takes nearly 30 seconds for the software to open and simple member searches take 10-12 seconds. Working through the VPN add another 20% in time and performance.

The issue still remains the management server and SQL still take a long time to communicate. Even opening up SQL management studio takes longer than it did before.

I'm not sure what to do next.

I appreciate the interest.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

When it on-prem, the 3 VMs were on one physical server. It took about 4-5 seconds for the management software to open and member lookups were real-time.Even working off the server in the cloud it takes nearly 30 seconds for the software to open and simple member searches take 10-12 seconds.

Sounds like this could be a chatty application. That is, one that sends lots of short small queries to read and write one row at a time. This means that network latency can give you a big penalty. With all VMs on the same host, the network latency is reduced to a minimum.

Exactly how this works in Azure. I assume that you did not place the VMs in different regions, because that would be a true killer. But even if the are in the same region, and they are just three different VMs, I can imagine that there is a lot of overhead in Azure that causes you pain.

Have you looked that ping times between the VMs?

I don't know what options Azure offers to reduce this overhead, but it can be worth investigating.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What if I I increase the size and speed of the disks on both servers to a higher performing value? Do you think this will help will decreasing latency?The next size has about 25% more throughput.

0 Votes 0 ·

If my gut feeling is correct, those actions will not help much, as I think this is a matter of latency in the communication between the machines due to overhead in Azure. But it could also be that there is throttling going on, and in that case, you could benefit from up-sizing.

So if you can take the downtime the operation requires, you can try it. You can always revert if the outcome is negative. In either case, please let us know how it works out.

0 Votes 0 ·