Why Azure Sql Managed Instance not using whole compute power while running queries

Question

azure sql managed instance is not using full compute power. While running queries/stored procedures - even though the cpu utilization is only 10%, stored procedure execution performance is not getting improved (takes few minutes sometimes for medium/complex read operations or calculations) by using excess compute power. Is there a way to make use of excess cpu/memory to make the operations faster? Because the cpu utilization never goes up more than 30%, I understand that we have high configuration with BC but is there any option to utilize them to make it faster?

Answer 1

unless you are using in-memory tables, cpu is not typically the limiting factor in sql queries. disk I/O and contention are the typical performance limiters.

as stated if you add ram, more data will be in memory, and thus less I/O waits, at least for reads. writes always require I/O.

my guess is that your queries are not well designed and not optimized.

Answer 2

It is very common scenario I have seen in Managed Instance customers. With Managed Instance memory consumption is always 100% and you don't realize queries with high consumption of RAM having an impact on the overall performance on the server, use Query Store and explore query plans with high RAM consumption, and optimize them to make better use of memory. For example, avoid nested CTEs. Try to avoid user-defined functions that serialize query plans and the use of cores. Examine the instance error log and there you may find some memory related errors.

If you scale up the tier for more vcores, you will get more available RAM, and you may see performance improvements. Even when you say you don't need to scale up the tier based on CPU consumption. but when you scale up you see performance improvements. RAM available is correlated with the number of cores of the service tier. So query optimization is key to save money with Azure SQL Managed Instance.

For IO intensive queries you may find the storage subsystem does not have good enough performance also.

What is the prevalent wait type you see on those queries with poor performance?