Azure SQL DB (Serverless) - Strange performance issue when grouping - execution times increase from few seconds to several minutes

Question

I have seen some strange issues in a Azure SQL DB (Serverless) recently. (General Purpose - Serverless: Gen5, 1 vCore)

Tables with approx 120000 rows have aggregate queries taking minutes to complete if more than 2 grouping columns are used.

Example:

select sum(amount), company_key, account_key from #myTable group by company_key, account_key

This will complete on a few seconds (returning a result set on approx 10000 rows).

But adding a third "group by" column will cripple the execution:

select sum(amount), company_key, account_key, cost_center_key from #myTable group by company_key, account_key , cost_center_key

will take a few minutes! (Yes, it will return a larger resultset - but no extremes? about 60000 records)

This behaviour is consistent when using MERGE, Window functions and index creation.

The behaviour is similar using either "normal" tables or temp tables. And index creation is also very slow.

I have never seen similar behaviour where a small change in the SQL have such an impact on performance! And on such small datasets...

The code was running without issues for a couple of weeks when this behaviour suddenly occurred in mid August. (No code change and no increase in data size)

Any tips or similar experiences? Thanks!

Answer 1

According to this page:

https://learn.microsoft.com/en-us/answers/questions/495225/azure-sql-database-fit-query.html

"a result of 1.000000000000 1.000000000000 1.000000000000 indicates that the workload always fit underneath the desired % of the resource limit"

So, a 1.0 equals a 100% ratio within limits (80% in the query).

I'll keep investigating... I just had a query run in 1 minute and 48 seconds without "order by" clause - returning approx. 8000 rows.
When adding one order by column, the query took > 3 hours.

sys.query_store_wait_stats shows that the query have > 200 minutes in wait for "memory". (12177015 ms)