Azure SQL alternative

Testa 551

Hi,

Currently, we are using Azure SQL Single database for a multi-tenant app. We are facing performance issue and keep scaling up to cope with the query volumes (and trying to optimize query with Query Performance Insight).

If we don't want to keep scaling up, what options do we have based on the below requirements:
1, minimum operational and implementational burden
2, (near) real time data update

Azure Database for PostgreSQL - Hyperscale (Citus) or Synapse Dedicated SQL Pool make sense? Or is there any other better service?

ShaktiSingh-MSFT 16,236 Reputation points

2022-09-27T04:21:28.42+00:00

Hi @Testa ,

Just checking in to see if the below answer provided by @Alberto Morillo helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
ShaktiSingh-MSFT 16,236 Reputation points

2022-10-03T07:22:53.333+00:00

Hello @Testa ,

Thank you for marking the answer by @Alberto Morillo as accepted. This will help others find useful answers faster.
Please feel free to take a survey on the relevant answer. You can help us improve by leaving feedback verbatim.

Regards,
@ShaktiSingh-MSFT
AzCommunity@microsoft.com
Alberto Morillo 34,451 Reputation points MVP

2022-10-03T13:13:28.06+00:00

Thank you @ShaktiSingh-MSFT for your support. Have a wonderful day.

Accepted answer

Alberto Morillo 34,451 Reputation points MVP

2022-09-26T04:38:14.45+00:00
Before you consider to try another service, please do the following first:

Perform maintenance of indexes and statistics regularly.

Tune up your queries. What are the top waits on your queries?

Make us of batching when you will process a large number of rows.

For ingesting large number of rows, make us of In-Memory OLTP.

For multi-tenant applications, one application/multiple databases consider elastic pools

To read data from large tables, consider setting the update statistics asynchronously

Consider to move to sharding in the future.
Please sign in to rate this answer.
Testa 551 Reputation points

2022-09-26T08:23:24.29+00:00

Thank you for your reply.

May I know:

1) "Tune up your queries" part has been done already. Data size is getting close to 1TB. But the responses to the queries made by the customers in the app are slow. From the below link, MPP (Massively parallel processing) might help but at the same time, near real time is not supporting. Is this the reason why you are suggesting sticking to Azure SQL?
https://learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/data-warehousing#data-warehousing-in-azure

2) When you are suggesting "elastic pools" and "sharding", are you referring to "Horizontally partitioned deployments"? (Currently "Fully multitenant development")?
https://learn.microsoft.com/en-us/azure/architecture/guide/multitenant/considerations/tenancy-models#horizontally-partitioned-deployments

Elastic pools seem to me that we still need to keep scaling up to deal with the slow response of their queries. We cannot allocate the resource flexibly like "30% of resources to Database A and 20 % to Database B and 50% to rest of 10 databases", right? Or any benefit of using elastic pools?

3) Comparing to moving to another service, "elastic pool" and "sharding" would be better in terms of "less operational and implementational work"?

Alberto Morillo 34,451 Reputation points MVP

2022-09-26T14:41:19.33+00:00

1) If your data size is getting close to 1 TB and you have tables in the range of billion rows you can benefit from async update of statistics: ALTER DATABASE [MyDatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON. That could improve elapsed execution time of queries from 8-10 minutes to a few seconds.

2) Partitioning may really help you in this scenario along with partitioned indexes. Columnsstore indexes can also provide you increase in performance.

You can have a mix of multi-tenant and single-tenant databases and elastic pools are good for that.If your clients are different in sizes, performance can greatly vary from one database to another and elastic pools is perfect on that scenario. The killer feature of Elastic Pools is being able to have the overall power of 100s databases but being able to shift that power from one database to another in a pool. Read here a similar thread and some benefits of Elastic Pools.

Here you will find sample solutions for your scenario.

Testa 551 Reputation points

2022-09-27T05:23:38.49+00:00

Thanks. Will check
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Your answer

Share via

Azure SQL alternative

1 additional answer

Your answer