Azure Hits Database Cpu Limits Too Easily

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 th

Solution 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.

Solution 2:

If you are creating Elastic tables with nvarchar(max) or varchar(max) in the original table, this will slow things down greatly when a query contains these fields. The only solution is to limit these fields to varchar (x), where x is your max data length. This made a HUGE difference in my elastic queries, from 35 min to 12 seconds.

Solution 3:

So in a nutshell; it turns out that the way sql data-pools works requires more optimized queries.

The way DTU's are measured means that any really beefy SQL work shuold be processed outside of the sql data-pools; but the data manipulation inside the data-pools should be as slick as possible (indexed, stats updated, fewest fields possible in joins).

It turns out that's just the way Azure works.