MSSQL performance is poor with Association Management software in Azure

Administrator 96 Reputation points
2021-10-22T15:38:21.77+00:00

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!

SQL Server on Azure Virtual Machines
Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
7,101 questions
0 comments
Accepted answer
  1. Administrator 96 Reputation points
    2021-10-29T17:03:17.403+00:00

    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.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-10-23T09:39:35.317+00:00

    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.

    0 comments
  2. Administrator 96 Reputation points
    2021-10-24T13:56:14.303+00:00

    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.

    0 comments
  3. Erland Sommarskog 100.9K Reputation points MVP
    2021-10-27T20:50:49.903+00:00

    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.

  4. Erland Sommarskog 100.9K Reputation points MVP
    2021-10-27T21:22:35.48+00:00

    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.