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

My experience with Azure SQL database is that you need to have your queries well tuned in terms of performance, and you need to regularly perform maintenance of indexes and statistics.

Creating an index like below after you populate the temporary table should support the group by operation an speed up things:

CREATE NONCLUSTERED INDEX ix_tempmyTable_Fields ON #myTable ([company_key], [account_key], [cost_center_key],[amount]);  

Hope this helps.

Answer 2

Thanks for input!

Unfortunately the index creation is also slow.

The index creation takes twice as long as the query. In my example the query ran at about two minutes. The index creation for the same table takes almost five minutes. (But then the query runs faster...).

My gut feeling is that I am hitting som kind of limit (memory?) when it comes to sorting/grouping. So as long as I keep under this limit I'm fine - but when exceeding it, the performance is bad. But what limit can it be?

Answer 3

This query can help you understand if you are hitting the limits:

SELECT   
    (COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'  
    ,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'  
    ,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'  
FROM sys.dm_db_resource_stats  

-- service level objective (SLO) of 99.9% <= go to next tier

Here you will find more ways to use this DMV.

Answer 4

Thanks.

CPU Fit Percent Log Write Fit Percent Physical Data Read Fit Percent

1. 000000000000 1.000000000000 1.000000000000

No memory issues visible when running my slow queries.

I am currently looking internally for a DB guru - because this is way beyond my expertise... I will try to update this post.

If I get the time I might setup a new database and see if the problems occur there too...

Answer 5

None below 99.9%