sql server - Azure hits database cpu limits too easily

Question

Background in a nutshell: - We have a SAAS solution with the following main components. 1. We have a web-portal back-end where administrators can edit data. 2. We have a web API that is called by mobile devices. The mobile devices track or report on students reading progress

Up to now the solution was hosted on virtual servers. Now we are migrating the solution to the Azure framework so that we can take advantage of the scalability of elastic database pools. We are using event topics to handle large volumes of posts from the mobile devices when the posts can be processed asynchronously, but there are some posts that need to be processed synchronously, and we are finding Azure's fabric really slow when it comes to multiple concurrent connections.

An example of the issue: -

So when Azure runs a query like the following: -

SELECT q.Category, COUNT(*)
FROM Question q
JOIN Answer a
ON a.QuestionId = q.QuestionId
GROUP BY q.Category
ORDER BY q.Category

The SQL CPU peaks above 97% in all the following scenarios: -
1. The DTU's are 50 and there is more than one concurrent call.
2. The DTU's are 1500 and there are 5 or more concurrent calls.
3. The DTU's are 4000 and there are 20 or more concurrent calls.

So we opened a support call with Microsoft. We spent more than a week of investigating things from sql statistics and indexes up to web api pricing tiers. After all that we still came up with the evidence that the CPU was peaking in the SQL database with the scenarios as outlined above.

This leads to the inevitable "re-write large chunks of your system" kind of argument.

So the underlying issue is that elastic database pools can't seem to perform anywhere near the ability of standard SQL databases. Also, the stand-alone database's performance doesn't seem to compete with the virtual server's performance.

This is so frustrating because Elastic databases pools were recommended for us for reasons of maintaining performance and adding scalability. We currently run 700+ customers on one virtual server; and were expecting to create one shard database per customer. The idea being that we could then scale up from hundreds of customers to tens of thousands of customers. In reality we are fighting to get the Azure fabric to perform anywhere near the kind of performance we have on virtual servers. So this question is to ask if there is anyone with significant experience in making Azure perform non-trivial tasks at a reasonable pace? (preferably without having to re-write large chunks of the system)

Answer 1

It takes a shift in thinking when migrating your SQL databases to the cloud.

In the on-premises world, we are used to powerful machines which are beefy enough to handle intense workloads. This is because physical machines are built with the needed resources to handle big workloads with heavy processing (built for the biggest task they need to handle, rather than the smallest task). Due to the over availability of resources, we often allow inefficiencies to work into queries and underlying schemas. With the excess availability of resources, the affect is often minimal.

But, then you try and move those same databases into Azure and things don't work quite as well. Remember that Azure is a pay-per-use kind of model. You pay X for Y resources, and when you need more, you pay more X for more Y. Because of this model, you have to consider that everything you do in your database effectively costs you money. Every query costs you money. Each and every inefficiency costs you more and more money. Etc. Etc. When explicitly paying for resources every single month, we tend to under buy (generally for the smallest task) because we feel like we are wasting money otherwise. This means that when an occasional big task needs to run, we don't have enough resources to handle it and performance is degraded. This leads us to think that Azure costs more but has worse performance.

So to improve your situation, you can always increase your resources in Azure if you are willing to pay for it. Or you can do as others suggest and optimize your queries and underlying schemas and realize cost savings each time you do it.