Azure Sql performance bad on S3 but not S4

Question

Azure Sql performance bad on S3 but not S4

Anthony Argyrou 0

On the 10th of June our Database hosted in UK South started consuming all it's DTUs on a certain query. Usually the database would run at up to 20% DTU, but for an unknown reason it's hitting 100%.

I scaled up to S4 from S3 and then the database's DTU consumption dropped to 7%. Every time we scale back down to S3 the resources get completely consumed, but on S4 it's below 10% most of the time, but does fluctuate up to 40%.

I was hoping to create a support request, but it seems like MS doesn't offer that anymore

User's image

Vijayalaxmi Kattimani 3,250 Reputation points Microsoft External Staff Moderator

2025-06-18T07:13:20.5+00:00
Hi Anthony Argyrou
Greetings!

As we understand that, you are facing some performance issues with your Azure SQL Database scaling back from S4 to S3.

That query might be hitting some performance thresholds that S3 simply can't handle, even if it's not always sustained. The dramatic drop in DTU usage when you scale to S4 suggests that the workload is pushing against S3's resource ceiling, triggering throttling or inefficient query execution under pressure.

Here are a few ways you might dig deeper:

Use Query Performance Insight in Azure to identify the exact query consuming those DTUs. Examine its execution plan and look for signs of Missing indexes, Table scans instead of seeks, Parameter sniffing, High CPU, I/O, or memory usage.

At S3, limits on CPU, I/O throughput, and memory can throttle execution. At S4, higher headroom might be masking inefficient operations. Azure SQL’s resource governance can silently enforce those thresholds, causing queries to wait or retry.

Any recent changes to schema, indexes, data volume, or query parameters around June 10? Even subtle shifts (like updated stats or a growing table) can tip a query into inefficiency.

Check if there are multiple execution plans getting generated (plan regression). The Query Store is excellent for spotting these.

Suggestions:

Stay on S4 for performance headroom and stability, if budget allows.

Optimize the high-impact query via indexing, refactoring, or forcing a better execution plan.

Enable Automatic Tuning to let Azure suggest or apply index and plan optimizations.

Evaluate vCore-based pricing for more predictable performance, easier scaling, and deeper monitoring capabilities.

I hope this information helps. Please do let us know if you have any further queries.
Anthony Argyrou 0 Reputation points

2025-06-18T14:53:37.91+00:00

Hi Vijayalaxmi,

Thank you for the response. We hadn't made any changes to the database or deployments of code in about a week, the database just started behaving badly. The query in question is not a very heavy query and performance was optimised. On my local db the query is showing to use 3% performance impact. It's actually a table value function.

Using Query Performance Insight in Azure doesn't really give much information as it mostly shows statistics of 0.03% impact as a maximum on the highest impact queries.

I don't understand how for months the database performance in general can be running at under 20% average and then one day, without code changes or database changes, we're hitting 100% DTUs. And then scaling up you can see that the DTU usage falls under 10%.

I'm sure 80% head room should be enough for a "badly" performing query, especially for a database which has been fine up until the 10th.

To me this looks like a poor performance sql database has been added to the same server and now we have to pay the cost of their poor performance. Or the DTU algorithm was changed by Azure recently and once again, we have to pay the cost...

When was the last time the DTU algorithm was updated?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-06-18T20:54:47.4466667+00:00

I don't understand how for months the database performance in general can be running at under 20% average and then one day, without code changes or database changes, we're hitting 100% DTUs.

Oh, that is something that many SQL Server professionals have experienced. The typical reason is that the statistics have been updated, and the optimizer now makes a different estimate from before, and this leads to a different execution plan. Which is intended to be better, but is in fact worse.

I don't know if the optimizer considers the service objective directly when building plans, but there are some hardware parameters that it considers.

I would definitely try to get hold of the query plans for both S3 and S4 and compare them. You say this is a table-valued function. If that is a multi-statement function, SSMS is only going to show you the plan for the call. But you can dig in Query Store.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-06-18T20:58:55.6933333+00:00

I was hoping to create a support request, but it seems like MS doesn't offer that anymore

They do! But you need a support plan of course. Interesting enough, if you go for the cheapest, Developer support, support is through this forum. (But the Microsoft support engineers can contact you through private message, so that you can share information, you don't want to share in public.)
Vijayalaxmi Kattimani 3,250 Reputation points Microsoft External Staff Moderator

2025-06-20T03:42:35.3566667+00:00
Hi Anthony Argyrou

We would like to inform you that, Microsoft has not publicly announced any recent changes to the DTU (Database Transaction Unit) calculation algorithm.

DTUs are calculated using a fixed blend of CPU, memory, and I/O. This formula has remained stable for years. Your issue is likely due to query plan regression, TVF performance surprises, or underlying data/stats changes.

I would suggest you to follow the below mentioned steps:Capture the Top Query: Use the Query Performance Insight in the Azure Portal:

Go to your SQL Database in the portal.

Click "Intelligent Performance" > "Query Performance Insight".

Set the timeframe around June 10th.

Identify the top consuming query by DTU or CPU.
-- Get recent high DTU-consuming queries SELECT TOP 10 qs.total_worker_time / qs.execution_count AS [AvgCPUTime], qs.execution_count, qs.total_worker_time, qs.total_logical_reads, qs.creation_time, qs.last_execution_time, st.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY [AvgCPUTime] DESC;
Check Execution Plan: Once you identify the top query run it in SSMS or Azure Data Studio with Actual Execution Plan enabled. Check for

Expensive key lookups.

Missing indexes.

Bad row estimates.

Nested loops on large datasets.

Compare Plans: S3 vs S4: If you are able to run the query on both S3 and S4 (e.g. in dev/test or off-peak hours) Compare execution plans. Higher DTU tiers may allow for parallelism or better memory grants, which makes inefficient plans appear “fine” on S4 but not on S3.

SET SHOWPLAN_XML ON;

-- run query here

SET SHOWPLAN_XML OFF;

Fixes Depending on Diagnosis:

Parameter sniffing: Use OPTION (RECOMPILE) or improve parameterization.

Outdated statistics: Run UPDATE STATISTICS on affected tables.

Missing indexes: Apply suggested index hints, but use with caution.

Poor query plan: Use Query Store to force a stable, efficient plan.

Inefficient schema/indexing: Refactor the query or improve indexing strategy.

I hope this information helps. Please do let us know if you have any further queries.
Vijayalaxmi Kattimani 3,250 Reputation points Microsoft External Staff Moderator

2025-06-24T01:15:48.7466667+00:00

Hi Anthony Argyrou

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Your answer

Vijayalaxmi Kattimani 3,250 Reputation points Microsoft External Staff Moderator

2025-06-18T07:13:20.5+00:00

Hi Anthony Argyrou
Greetings!

As we understand that, you are facing some performance issues with your Azure SQL Database scaling back from S4 to S3.

That query might be hitting some performance thresholds that S3 simply can't handle, even if it's not always sustained. The dramatic drop in DTU usage when you scale to S4 suggests that the workload is pushing against S3's resource ceiling, triggering throttling or inefficient query execution under pressure.

Here are a few ways you might dig deeper:

Use Query Performance Insight in Azure to identify the exact query consuming those DTUs. Examine its execution plan and look for signs of Missing indexes, Table scans instead of seeks, Parameter sniffing, High CPU, I/O, or memory usage.

At S3, limits on CPU, I/O throughput, and memory can throttle execution. At S4, higher headroom might be masking inefficient operations. Azure SQL’s resource governance can silently enforce those thresholds, causing queries to wait or retry.

Any recent changes to schema, indexes, data volume, or query parameters around June 10? Even subtle shifts (like updated stats or a growing table) can tip a query into inefficiency.

Check if there are multiple execution plans getting generated (plan regression). The Query Store is excellent for spotting these.

Suggestions:

Stay on S4 for performance headroom and stability, if budget allows.

Optimize the high-impact query via indexing, refactoring, or forcing a better execution plan.

Enable Automatic Tuning to let Azure suggest or apply index and plan optimizations.

Evaluate vCore-based pricing for more predictable performance, easier scaling, and deeper monitoring capabilities.

I hope this information helps. Please do let us know if you have any further queries.
Anthony Argyrou 0 Reputation points

2025-06-18T14:53:37.91+00:00

Hi Vijayalaxmi,

Thank you for the response. We hadn't made any changes to the database or deployments of code in about a week, the database just started behaving badly. The query in question is not a very heavy query and performance was optimised. On my local db the query is showing to use 3% performance impact. It's actually a table value function.

Using Query Performance Insight in Azure doesn't really give much information as it mostly shows statistics of 0.03% impact as a maximum on the highest impact queries.

I don't understand how for months the database performance in general can be running at under 20% average and then one day, without code changes or database changes, we're hitting 100% DTUs. And then scaling up you can see that the DTU usage falls under 10%.

I'm sure 80% head room should be enough for a "badly" performing query, especially for a database which has been fine up until the 10th.

To me this looks like a poor performance sql database has been added to the same server and now we have to pay the cost of their poor performance. Or the DTU algorithm was changed by Azure recently and once again, we have to pay the cost...

When was the last time the DTU algorithm was updated?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-06-18T20:54:47.4466667+00:00

I don't understand how for months the database performance in general can be running at under 20% average and then one day, without code changes or database changes, we're hitting 100% DTUs.

Oh, that is something that many SQL Server professionals have experienced. The typical reason is that the statistics have been updated, and the optimizer now makes a different estimate from before, and this leads to a different execution plan. Which is intended to be better, but is in fact worse.

I don't know if the optimizer considers the service objective directly when building plans, but there are some hardware parameters that it considers.

I would definitely try to get hold of the query plans for both S3 and S4 and compare them. You say this is a table-valued function. If that is a multi-statement function, SSMS is only going to show you the plan for the call. But you can dig in Query Store.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-06-18T20:58:55.6933333+00:00

I was hoping to create a support request, but it seems like MS doesn't offer that anymore

They do! But you need a support plan of course. Interesting enough, if you go for the cheapest, Developer support, support is through this forum. (But the Microsoft support engineers can contact you through private message, so that you can share information, you don't want to share in public.)
Vijayalaxmi Kattimani 3,250 Reputation points Microsoft External Staff Moderator

2025-06-24T01:15:48.7466667+00:00

Hi Anthony Argyrou

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Azure Sql performance bad on S3 but not S4

Your answer